Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a PivotTable, it's likely that you'll have data for many categories grouped together into a single display. If that's the case, it might make sense to print your data on a number of worksheets. There are two ways that you can separate your PivotTable data. The first is by using the fields in the Row Labels area and the second is by using fields in the Report Filter area. If your PivotTable has more than one field in the Row Labels area, you can have Excel print each group on a new page. For example, in this worksheet I have data from 2009 and 2010 and let's say that I want it to print 2009 and 2010 on separate worksheets.
To do that, you right-click any cell in the label field by which you want to separate. In other words I want to separate by year so I'll click one of my two year fields, in this case right clicking 2009, and in the shortcut menu that appears click Field Settings. In the Field Settings dialog box the setting that we're interested in is on the Layout & Print page and at the bottom of this page you can check the Insert page break after each item box and then click OK.
You don't see anything on the screen, but if you go to Backstage view by clicking the File tab and then click Print, you can see that 2009 will be printed on one page and then if we scroll to the next page, 2010 will be printed there. Now click any tab on the Ribbon to switch back. Now let's say that you create a filter using the Report Filter area, which I'll do by dragging the Quarter field down to the Report Filter area. So now I have the ability to filter the data that appears in the PivotTable.
In this case, however, what I want to do is to print data from quarter number one on one page, quarter number two on another page, and so on. So the first thing I'll do, and this is just personal preference, but I'm going to remove the print area that's been set, so that I have 2009 and 2010 on the same page. In other words I don't want to have two settings at the same time. So I will right-click a value in the Year field, click Field Settings, go to the Layout & Print page, and clear the Insert page break after each item checkbox and click OK.
Now with any cell in the PivotTable selected, I can go to the Options contextual tab and then in the PivotTable group I click the Options button's down arrow and then click Show Report Filter pages. In the dialog box that appears I verify that the quarter field appears and that is the report filter page that I want and click OK. When I do, Excel creates four different worksheets, one displaying data from quarter one, the second quarter two, three and four, and you can see those new worksheets down here on the tab bar with worksheets one, two, three and four.
Now let's say I want to print all of those worksheets. To do that, I select sheet number one and then holding down the Shift key I click the tab for sheet number four. Then if I go to the Print page of the Backstage view and print my workbook, these four worksheets will all be printed. The built-in Excel PivotTable functionality enables you to separate your data when you print without necessarily separating the data in the workbook. Creating new worksheets for each item in the Report Filter area does add worksheets to your workbook, but setting the Insert page break after each item option enables you to separate those categories of data without affecting your PivotTable's organization.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86668 Viewers
80 Video lessons · 135978 Viewers
59 Video lessons · 54711 Viewers
52 Video lessons · 68617 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.