Print a PivotTable report

Printing a PivotTable report the way that you want often requires using a combination of print features for worksheets and PivotTable reports. To use these features, you must have only one report on the worksheet, or you must set a print area (print area: One or more ranges of cells that you designate to print when you don't want to print the entire worksheet. If a worksheet includes a print area, only the print area is printed.) that includes only one report.

 Tip   For the best results, follow these sections in sequence.

In this article


Set a print area

If you have more than one PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) on the worksheet, set a print area (print area: One or more ranges of cells that you designate to print when you don't want to print the entire worksheet. If a worksheet includes a print area, only the print area is printed.) that includes only the report that you want to print.

  1. Click the PivotTable report.
  2. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.

Excel Ribbon Image

  1. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.

Excel Ribbon Image

Top of Page Top of Page

Fine-tune the page layout in Page Layout view

Page Layout view is useful for getting your data ready for printing. For example, you can use the rulers to measure the width and height of the data, change the page orientation, add or change page headers and footers, set margins for printing, and hide or display row and column headers.

  1. On the View tab, in the Workbook Views group, click Page Layout.

Ribbon image

 Tip   You can also click Page Layout View Button image on the status bar.

  1. Make adjustments to the page layout.

For more information, see Use Page Layout view to fine-tune pages before printing.

Top of Page Top of Page

Repeat row and column labels on each page as print titles

You can repeat the row labels and column labels of a report on every printed page as print titles (print titles: Row or column labels that are printed at the top of or on the left side of every page on a printed worksheet.). When you change the layout of the report so that the labels are shown in different worksheet rows and columns, the new label rows and columns are repeated automatically the next time you print the report.

  1. On the Page Layout tab, in the Page Setup group, click Print Titles.

Excel Ribbon Image

  1. In the Page Setup dialog box, make sure that the Rows to repeat at top and Columns to repeat at left check boxes are cleared.
  2. Close the Page Setup dialog box.
  3. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. In the PivotTable Options dialog box, click the Printing tab, and then select the Set print titles check box.
  2. If your report has more than one row label and you also want to repeat the outer row label items on each page, select the Repeat row labels on each printed page check box.

Top of Page Top of Page

Set page breaks after each outer row label

When your report has multiple row labels and a page break falls within a group of row label items, you can set the report to automatically repeat the item labels for the outer labels at the top of the next page. In a report with two or more row labels, all but the rightmost label are outer row labels. In the following example, the report is set so that the items East and Qtr2 from the outer row labels Region and Qtr are printed on the next page after the page break.


Printing a PivotTable report

Callout 1 Page break within an item group.
Callout 2 Item labels from the outer row labels are repeated on page 2.

  1. Right-click the outer row label that has the items that you want to print on separate pages, and then click Field Settings on the shortcut menu.
  2. In the Field Settings dialog box, click the Layout & Print tab.
  3. Select the Insert page break after each item check box.

Top of Page Top of Page

Adjust page breaks throughout the report

It is a good idea to review the entire PivotTable report in Page Break Preview, in case you need to make additional adjustments of the page breaks.

  • On the View tab, in the Workbook Views group, click Page Break Preview.

Ribbon image

 Tip   You can also click Page Break Preview Button image on the status bar.

You can insert new manual page breaks and move and delete automatic page breaks. For more information, see Add, delete, or move page breaks.

Top of Page Top of Page

Decide whether to print the expand and collapse buttons

You may or may not want to print the expand and collapse buttons. To decide whether you want to print the expand and collapse buttons, do the following:

  • To show or hide the expand and collapse buttons on the worksheet, on the Options tab, in the Show/Hide group, click +/- Buttons.

Excel Ribbon Image

  • To show or hide the expand and collapse buttons on the printed report, do the following:
    1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. Click the Printing tab, and then select or clear the Print expand/collapse buttons when displayed on PivotTable check box.

 Note   This check box requires the +/- Buttons command in the Show/Hide group on the Options tab to be turned on.

Top of Page Top of Page

Check your final layout in Print Preview and then print the report

  1. To check your final print layout, click the Microsoft Office Button Button image, click the arrow next to Print, and then click Print Preview.

Keyboard shortcut  You can also press CTRL+F2.

Make final adjustments as necessary. For more information, see Preview worksheet pages before printing.

  1. When the preview looks correct, click Print.

Top of Page Top of Page

 
 
Applies to:
Excel 2007