Software

How to repeat pivot table headings when printing Excel documents

A pivot table that spans more than one printed page will be confusing without its column headings. Use this tip to print column headings on every sheet.

When a pivot table spans more than one printed page, you lose the column headings that print at the top of the table. Readers might find it necessary to refer back to the table's first page to keep up with what they're reading. Users will probably remember what the data represents in a simple table similar to the one shown below, but tracking across several pages will most assuredly be annoying and confusing.

To avoid confusion, you can print the pivot table's column headings at the top of each new sheet, similar to printing titles in a sheet. The difference is that Excel prints titles at the beginning of every printed page, but Excel prints column headings only as part of the pivot table and not for every sheet in the print out.

Before setting this option, you must clear any existing sheet-level print titles. If you omit this step, setting the pivot table's print titles option will have no impact. To clear the sheet-level print titles, do the following:

  1. Click the Layout tab.
  2. In the Page Setup group, click the Print Titles option.
  3. Delete any references in the Rows To Repeat At Top and Columns To Repeat At Left options. They must be blank.
  4. Click OK.

Now, to print column headings for pivot tables that span more than one page, do the following:

  1. Right-click the pivot table and choose Pivot Table Options.
  2. Click the Printing tab.
  3. Check the Set Print Titles option.
  4. Click OK. Now, Excel will print the pivot table's column headings at the beginning of a new page.

I don't think the first step of deleting the sheet-level title setting should be necessary, but I've not found a way around it yet.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
jensqox
jensqox

If you have multiple pivots on the same page you actually have to use the Print Titles option Suzan sugests to be removed.

boucaria
boucaria

Susan: I am not sure if this is under the general rubric of headings, but I have provided source material for a staff member who produces reports that have columns at the top of the Pivot table reports, and he is working with one set of data ( fairly large set), and under one "pivot" he has 5 across the top, but he changes the selection and the display drops from 5 to 3 columns. I seem to recall that there is a solution within a pivot table that will use the data that is there in the data, but does not display, however, I have been searching for a solution, as well as trying to find the exact article I recall, but a trainer here wants to plug the back end into an access data source, which seems to be overdoing it. However, I am stuck in that the data to display is not something I can bring out into public forums, anyway, I don't know what sort of info you would need in this case, since the pivot table would be more consistent with 5 columns, since the rest of the data displays that way. I have been searching Bill jelen's books on the Pivot tables but I can't seem to find exact info. Apologies if this sounds waffly, thats what pivot tables feel like sometimes :-) Anyway, I had helped the staff member get so far and not farther; feels like a shakespearean quandry, to go back takes the same effort as to go forward ...

Shadeburst
Shadeburst

Cool. I tried it. It works. Thanks!

Editor's Picks