Printing an Excel Pivot Table Report |
Step 4 :: Printing an Excel Pivot Table Report |
Do you plan to print your Excel Pivot Table report?
- Automatically reprint labels at the top of each page
- Specify where you want Excel to insert page breaks.
- Repeat the outer row field items on each printed page.
- Print sections of a Excel Pivot Table report on separate pages
Before you start printing
Before you set up your printing, follow these 3 steps to specify the print area for the report:
- Click inside the Excel Pivot Table.
- Right Click your mouse to reveal the contextual menu and click on the Select > Entire Table option. (This ensures the whole Excel Pivot Table is selected)
 |
1. On the File menu, point to Print Area, and then click Set Print Area.
We will use an example scenario to take you through creating a Pivot Table using the source data below.
 |
This sets the Excel Pivot Table as the area to print.
Automatically reprint labels at the top of each page
To make it easier to follow the Excel Pivot Table report from one page to the next, you can automatically reprint labels at the top of each page.
The Pivot Table will look like the example below. Note, the colours are for illustrative purposes only, these help identify the relationship between the source data columns and the pivot table areas.For example, on the printed page, you can repeat the labels from rows 3 and 4 and also repeat the outer row field item ‘Year’.
 |
To set print options for your Excel Pivot Table report, use any or all of the following procedures:
Repeat row labels on each printed page.
1. On the File menu, click Page Setup, and then click the Sheet tab. Make sure the ‘Rows to repeat at top’ and ‘Columns to repeat at left boxes’ are blank, and then click OK. Simply delete the values if they exist in these boxes.
 |
2. Click inside the Excel Pivot Table.
3. Right click your mouse button and select the ‘Table Options…’ preference from this contextual menu to open up the ‘Pivot Table Options’ menu.
 |
4. In the ‘Pivot Table Options’ menu in the ‘Format options’ section, select the ‘Set print titles’ check box.
 |
Go to File | Print Preview to see the results.
Repeat the outer row field items on each printed page.
If your Excel Pivot Table spans over many columns and does not fit into a single page easily. You can print the Excel Pivot Table so that the labels on the left hand column are repeated on each page.
1. On the File menu, click Page Setup, and then click the Sheet tab. Make sure the ‘Rows to repeat at top’ and ‘Columns to repeat at left boxes’ are blank, and then click OK. Simply delete the values if they exist in these boxes.
 |
2. Click inside the Excel Pivot Table.
3. Right click your mouse button and select the ‘Table Options…’ preference from this contextual menu to open up the ‘Pivot Table Options’ menu.
 |
4. In the ‘Pivot Table Options’ menu in the ‘Format options’ section, select the ‘Repeat item labels on each page’ check box (if it isn’t already ticked).
 |
Go to File | Print Preview to see the results.
Print sections of an Excel Pivot Table report on separate pages
To ensure that a logical chunk of the Excel Pivot Table report is printed on each page, you can specify where you want Excel to insert page breaks.
You might choose to include a page break at a specific location, such as before each new Year field to avoid separating the items for each Year.
In the following illustration, an automatic page break is inserted between rows 21 and 22, separating items for 2003 and 2010.
|
1. Ensure you have selected the entire Excel Pivot Table and Set the Print Area. (See the ‘Before you start printing’ section above.)
2. Double-click the heading on the outer row field that has the items you want to print on separate pages. For example, to insert an automatic page break between each Year, double-click the heading for the Year row.
|
3. In the PivotTable Field dialog box, click the ‘Layout. ..’ button.
Note: If there's only one item in the outer row field, Layout will be unavailable.
4. In the ‘Print Option’ section of the ‘Pivot Table Field Layout’ dialog box select the ‘Insert page break after each item’ check box, and then click OK twice.
 |
5. On the main excel menu select View > Page Break Preview, and make any adjustments you want to the automatic page breaks. You can move a page break by dragging it or delete a page break by pressing DELETE.
 |
Note: Ensure the ‘Scaling’ settings in Page Set-Up are set to ‘Adjust to…’ rather than the ‘Fit to..’ option or the page breaks will not work correctly.
After you set print options, you can click Print Preview on the File menu to preview the printed report.
When you're ready to print the Excel Pivot Table report, click Print.
| <<< Previous Section :: Refresh an Excel Pivot Table Report | Next Section :: Show the Excel Pivot Table Toolbar >>> |