Join Curt Frye for an in-depth discussion in this video Printing headers at the top of each printed page, part of Excel for Mac 2011: Pivot Tables in Depth.
- [Voiceover] If your Pivot Table extends onto more than one printed page, you and your colleagues might find it hard to track which data belongs with which column or which row. In this movie, I will show you how to print headers for rows and columns, and also how to control other elements that might print or might not based on the choices that you make. I will show you how to do this using the headers Excel workbook and you find that in the chapter five folder of your exercise files collection. In this workbook I have a Pivot Table and as I scroll down you can see that it's very likely that it would print on more than one page.
To take care of that what I want to do is to insert a page break so that I can force it to print 2014 on one page and then 2015 on the next page. To create my page break I will click cell A 19 and then I'll go to the layout tab in the ribbon click the breaks button, this is in the page setup section and click insert page break. And if I click away, you can see that there is a dash line now above cell A 19.
If I click cell, say C 19 then the page break would extend horizontally above where I clicked, the cell that I clicked, but also there would be a vertical page break running along the left side of the cell as well. So if you only want a horizontal page break click the cell in column A. And we can see how this works by previewing it, so I'll go to print preview, open the file menu, and click print. And there I can see page one and also page two.
One thing to note about page two is that there are no headers, nothing at the top of the page. So, I'll click cancel to stop the print job and I will work with the Pivot Table to change it so that the headers appear at the top of each page. So, I'll click a cell in the Pivot Table that brings the Pivot Table contextual tab back to the ribbon which is what I want to click and then in the data group, I'll click options. That displays the Pivot Table options dialogue box and what I can do is to go down to the print section of the dialogue box, there are two options.
Repeat row labels on each page which you always want to have checked and also page, row, and column headings. I usually check this as well. So, I'll go ahead and check it. If I go back up to the top, I can also see in the show section that I can either choose to print or not print expand and collapsed triangles those are the controls that you click to show or hide details. In this case I will leave them turned off but if I did want to print them I could simply check the print expand collapsed triangle check box.
That's the change I wanted to make so I'll click okay. And I'm back in my workbook. Now, if I got to print preview by going to file and then print. I see the headers at the top of the first page and when I move forward, I also see the headers at the top of the second page. So, if you were going to print a worksheet that expands onto more than one page, I highly recommend that you add column labels in addition to row labels.
- Creating a PivotTable
- Refreshing PivotTable data
- Managing totals
- Creating calculated fields
- Grouping PivotTable fields
- Sorting and filtering PivotTable data
- Printing PivotTables