Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a PivotTable position that illustrates a point well, you can print the PivotTable and include it in a physical document. In this movie I'll show you how to print your PivotTables. First you can preview how your PivotTable will look when you print it by going to the Backstage view. To do that click the File tab which displays the Backstage view and then click the Print item to display the Print page and on the right side of that page is a print preview. So you can see the PivotTable and also the data comment that is over to the side, which would also be printed.
So let's say that I just want to print the PivotTable and not the comment over on the side. To do that, I click the Home tab at the Ribbon, then click the Options tab, and then click the Select button, and click entire PivotTable. So what I've just done is selected the entire PivotTable. Now I can go to the Page Layout tab, click the Print Area button, and click Set Print Area. When I do, and I'll just click away from the selected cells to release the selection, you can see that Excel has dashed lines around the area that was selected, indicating it's a print area.
In fact if we go back to the Backstage view and the Print page, you can see that the comment text over on the side will no longer be printed because it was outside of the print area. So now let's switch back into the document proper and I'll just click the Home tab. Now let's say that I want to have the titles appear on each printed page and let me show you what I mean. First I'll create a page break here on row 18 and my goal is to split the values from 2009 and 2010 onto two separate printed pages.
So I've clicked the row header and then I'll go to Page Layout, click Breaks, and then click Insert Page Break. And when I do you can see that there will now be a page break between December 2009 and the summary for 2010 and if we go to the Backstage view, File > Print, you can see that we have the values on page 1, that's 2009, and then if I go to the next page, you can see the values for 2010. But what you also see are that there are no headers for 2010. So for example, I might not remember that the first column is FirmA and the second column is FirmB because of those labels don't appear.
So what I'll do now is set up my worksheet so that those labels will appear when I print the PivotTable. To do that I'll go back into the main document and I'll just click the Home tab and then right-click any cell in the PivotTable and click PivotTable Options. Then on the Printing page of the dialog box check the Set print titles box and then click OK. Now when we go back to the Backstage view and preview our print job to come, we'll see that on the first page we have our headers and then on page 2 they appear as well.
Now there's one other option I'd like to show you when it comes to printing PivotTables and that is that you can turn the expanding contract controls on or off. In other words you can print them or not. So I'll click the Home tab again to go back into the PivotTable, right-click any cell within the PivotTable, click PivotTable Options again, and then on the Printing page you can select whether to print to the expand and collapse buttons that are displayed on the PivotTable. In this case it's turned off, which is usually the better choice, but I'll just show you what they look like when you have them turned on.
So I'll check that box, click OK, and then go back to print preview and you can see that the button will appear when you print the worksheet. Printing a PivotTable seems like a straightforward operation and it usually is. That said, you do have a number of options when it comes to printing your PivotTable. You should experiment with the different ways you can control how your PivotTables print, so you can get exactly the output you want.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64708 Viewers
80 Video lessons · 124322 Viewers
52 Video lessons · 60258 Viewers
59 Video lessons · 46091 Viewers