Printing a Pivot Table Report – Excel
As applicable to Excel 2007
Excel Pivot Table Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
12. Printing a PivotTable report, Repeat Row Labels, Set Print Titles, Insert Page Breaks, Print Area, Print Layout.
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
To print an Excel Pivot Table report, the worksheet print features and those for Pivot Table reports are required to be used together.
Print Options in the ‘Pivot Table Options’ dialog box:
To launch the dialog box, click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Options’ in the ‘PivotTable’ group and click ‘Options’ again which opens the dialog box. In the ‘Print’ section of the ‘Printing’ tab of this dialog box, you have three options for printing a Pivot Table:
1. Print expand/collapse buttons when displayed on Pivot Table:
If you have chosen to show expand/collapse buttons in your Pivot Table, then you can also print these buttons by selecting the check box of ‘Print expand/collapse buttons when displayed on PivotTable’ in the ‘Print Section’ of the ‘Printing’ tab.
2. Repeat row labels on each printed page:
To repeat the outer row label items on the printout of each page of the Pivot Table, select the check box of ‘Repeat row labels on each printed page’ in the ‘Print Section’ of the ‘Printing’ tab. This is applicable when there is more than one row label item in the Pivot Table report.
3. Set print titles:
To repeat field titles including Row Labels, Value Field & Report Field names, on the printout of each page of the Pivot Table, select the check box of ‘Set print titles’ in the ‘Print Section’ of the ‘Printing’ tab.
Example showing different Printing options for a Pivot Table:
Refer Image 1a which shows a Pivot Table Report, being split into the next page after row no. 29.
Image 1b shows the print of the second page from row no. 30 onwards, when ‘Repeat row labels on each printed page’ is not selected and the outer row label items of ‘Compact’, ‘Europe’ & ‘France’ do not show.
Image 1c shows the second page when ‘Repeat row labels on each printed page’ check box is selected (and ‘Set print titles’ check box is not selected) and the outer row label items of ‘Compact’, ‘Europe’ & ‘France’ show in printing.
Refer Image 1d which is the print of the second page when ‘Repeat row labels on each printed page’ is not selected and ‘Set print titles’ is selected. The outer row label items of ‘Compact’, ‘Europe’ & ‘France’ do not show but the Row Labels of ‘Car Models’, ‘Region’, ‘Country’ & ‘City’ and the Value field ‘Sales’ as also the Report Filter field of ‘Year’ all show in printing.
Refer Image 1e which is the print of the second page when both ‘Repeat row labels on each printed page’ and ‘Set print titles’ are selected. The outer row label items of ‘Compact’, ‘Europe’ & ‘France’ show, and the Row Labels of ‘Car Models’, ‘Region’, ‘Country’ & ‘City’ and the Value field ‘Sales’ as also the Report Filter field of ‘Year’ all show in printing.
Insert page break after each item (in the ‘Field Settings’ dialog box):
To print a Pivot Table report with specified parts on separate pages, you can set a page break after an item of any outer row label. In the Pivot Table report, right-click on the outer row label area (whose items you want to appear on separate pages) in a Pivot Table report, select ‘Field Settings’, this will open the dialog box of ‘Field Settings’, click on ‘Layout & Print’ tab, and then in the ‘Print’ section select check box of ‘Insert page break after each item’. This will print each item of the outer row label which is right-clicked, on a separate page. Note: You can also open the dialog box of ‘Field Settings’ by clicking ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, and in the ‘Active Field’ group click ‘Field Settings’. Refer Image 2 (where the ‘Region’ row label has been selected and ‘Insert page break after each item’ enabled) which shows the print view of page breaks with horizontal broken lines after each Region of Europe and North America.
Print Titles (in the ‘Page Layout’ tab):
You can also repeat specific rows at the top or columns on the left while printing a Pivot Table report, irrespective of the ‘Printing’ settings in the ‘PivotTable Options’ dialog box of the Pivot Table. To do this, in the ‘Page Layout’ tab on the ribbon, in the ‘Page Setup’ group, click ‘Print Titles’, this will open the dialog box of ‘Page Setup’, go to the ‘Sheet’ tab, and in the heading ‘Print Titles’ enter values in the boxes of ‘Rows to repeat at top’ or ‘Columns to repeat at left’. Note: Ensure these boxes are cleared to set ‘Repeat row labels on each printed page’ and ‘Set print titles’ in the dialog box of ‘PivotTable Options’ explained above.
——————————————————————————————————————————–
Steps to Print a Pivot Table Report
1. Set Print Area
In case you have multiple Pivot Table reports in a single worksheet, set the print area of the specific Pivot Table report you wish to print – under the ‘Page Layout’ tab on the ribbon, click on ‘Print Area’ in the ‘Page Setup’ group. You can set the area to include the entire Pivot Table which should be pre-selected. You can select the entire Pivot Table by clicking the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Select’ in the ‘Actions’ group and finally click ‘Entire PivotTable’.
2. Determine Page Layout
For a Page Layout View, under the ‘View’ tab on the ribbon, click on ‘Page Layout’ in the ‘Workbook Views’ group. This enables to view the document as it will appear on the printed page, to see where pages begin and end, and to view any headers or footers on the page. You can set margins, switch page orientation, choose paper size, use rulers to determine width and height, set headers & footers, apply scaling for printing, print titles, and so on.
3. Print Settings specific for a Pivot Table report
As explained above, printing options specific to a Pivot Table report are available in the ‘PivotTable Options’ dialog box and the ‘Field Settings’ dialog box. Below are the print options and settings to be determined likewise:
Print expand/collapse buttons when displayed on Pivot Table;
Repeat row labels on each printed page;
Set print titles; and
Insert page break after each item.
4. Print Titles (in the ‘Page Layout’ tab)
As explained above, print titles can also be set in the ‘Page Layout’ tab on the ribbon.
5. Preview Page Breaks
For a Page Break View, under the ‘View’ tab on the ribbon, click on ‘Page Break Preview’ in the ‘Workbook Views’ group. This view gives a preview of where pages will break when the report is printed. You can make any overall changes where you want the pages to break.
6. Print Preview
For a Print Review, click the Microsoft Office Button on the top left of the ribbon, point your mouse to ‘Print’ and click on ‘Print Preview’ in the list of commands. This shows the final layout of the page as it will be printed. Preview and make changes to pages before printing.