Wednesday, November 21, 2012

How to Insert Page Breaks on a Pivot Table

1. Open the Excel file that contains the pivot table you want to format. Then select 'View,' 'Toolbars' and 'PivotTable' from the toolbar to open the 'PivotTable' toolbar if it's hidden.
2. Select 'PivotTable' and 'Table Options' from the 'PivotTable' toolbar. The 'PivotTable Options' dialog box will open. Add a checkmark to 'Repeat item labels on each printed page' and to 'Set print titles.' Then click 'OK.'
3. Go to the 'Pivot Table Field List' task pane. You can open the task pane if it's closed by clicking on the 'Show Field List' button in the 'PivotTable' toolbar.
4. Customize how each page is categorized by selecting 'Page Area' from the 'Add To' drop-down list in the task pane. Select the field you want to use to separate each page. Then drag it from the task pane into the 'Page Area' in the top-left section of your table (by the 'Row Area').
5. Choose 'PivotTable,' 'Select' and 'Entire Table' from the 'PivotTable' toolbar. Then click on 'File,' 'Print Area' and 'Set Print Area' from the menu. This will set which area of the page will be printed.
6. Select 'File' and 'Page Setup' from the menu and click on the 'Sheet' tab. Click within in the 'Rows to repeat at top' text box. Then go to the worksheet (while the dialog box is open) and select the rows you want to repeat on each page. Click within the 'Columns to repeat at left' text box. Then go to the worksheet and select the columns you want to repeat. Click 'OK' to close the 'Page Setup' dialog box when you are finished.
7. Click on 'View' and 'Page Break Preview' in the toolbar to switch to page break view. The current page breaks in your pivot table report will be revealed at the blue lines. You can insert page breaks manually on your page or after each row type.
8. Insert a manual page break by right-clicking on any row where the page break will be added. Then select 'Insert Page Break' from the pop-up.
9. Insert a page break after each row type by double-clicking on the row type heading on your spreadsheet. The 'PivotTable Field' dialog box will open. Click on the 'Layout' button to open the 'PivotTable Field Layout' dialog box. Then add a check mark to 'Insert page break after each item' and click 'OK.'
10. Click on 'OK' to close the 'PivotTable Field' dialog box.
11. Click on 'View' and 'Normal' in the toolbar to return to normal view. Then save your changes.

Blogger news