Normally, a long pivot table prints to the bottom of a page, then continues on the next page. The page break occurs naturally, not at a specific position in the pivot table.
You can control the page breaks, by changing the pivot table settings.
The Sample Layout
In this example, the pivot table has two fields in the Row Labels area – Store and Product Category.
We’d like to print this pivot table, with each store’s data starting on a new page. That way, we’d be able to print the entire
report, and send each store its own section.
Change the Field Setting
We’ll change a setting in the Store field, so each pivot item starts on a new page in the printed report.
- Right-click one of the Store labels in the pivot table, and then click Field Settings.
- In the Field Settings dialog box, on the Layout & Print tab, add a check mark to Insert Page Break After Each Item.
- Click OK, to close the dialog box.
Things to Consider
The Insert Page Break After Each Item setting doesn’t force all items for a field fit on one page. It only creates a page break so the next item will start on a new page. If a field has many items, it might print on two or more pages.
Also, with this setting, your printed report could use an excessive amount of paper. You might prefer to manually adjust the page breaks in Page Break Preview.
To Go To Page Break Preview
- On the Ribbon’s View tab, in the Workbook Views group, click Page Break Preview.
- Dashed lines indicate an automatic page break and solid lines are manual page breaks.
- To move a page break, point to it, and drag up or down on the page.
To return to Normal view
- On the Ribbon’s View tab, in the Workbook Views group, click Page Break Preview.
_____________
If you have trouble where Excel only prints the first page of your pivot table after you enable this feature…
Ensure that you haven’t changed the page setting fit to 1 page high or Excel will only give you the first page of the series. This is unfortunate; I wanted it to scale each section of the pivot table individually to one page.
How would you, through VBA code (macro), print each pivot table item to a separate PDF file when each pivot table item’s number of rows will dynamically change from month to month? This means your print range for each pivot table item is not static and has to be programatically calculated. I haven’t seen online where anyone is doing what I’m trying to do. I hope what I’m asking makes sense. Thanks.
AWESOME!!!!!! Thanks for this! Totally helped me out just now!!!!
Pivot Field “Insert Page break” doesn’t work properly when sub-items overlap to first record on next page.
In this situation, the next main item continues immediately below on same page as sub-item from previous main item.
Page break works fine if sub-items overlap to 2 or more records on next page.
Can send example if required.
Exactly! I faced the same problem today. I tried adding a fake row to the related data and it was fixed. So Mark is right.
The Insert Page Break After Each Item setting doesn’t seem to carry over the pivot styles. For example, pivot style light 21 applies to the first two sheets but not to any sheets that follow. This seems to be the case with all the pivot styles. Do I need to create a new pivot style?
I was just about to give up on this and revert to using a template with subtotals, a vlookup of those subtotal names and predefined print areas. This is way easier. Thanks Debra, you wizard!
Is there anyway to expand this to every 3rd or 4th or 5th item?