Join Curt Frye for an in-depth discussion in this video Printing each item on its own page, part of Excel 2016: Pivot Tables in Depth.
- View Offline
- When you create a pivot table, it is likely that you'll have data from many categories grouped together into a single display. If that's the case, it might make sense to print your pivot table data on a number of worksheets. There are two ways you can separate your pivot table data. The first is by using the fields in the row labels area. And the second is by using the fields in the report filter area. In this movie I will show you how to perform both of these tasks. My sample file is the Print Items Excel workbook, which you can find in the Chapter Seven folder of the Exercise Files collection.
This pivot table has monthly sales data for four different room types. These four values here. And you can see in the row area, I have data by year, and also within each year by month. And my goal, scrolling down to show data for 2015, is to print the data for 2014 on one page and for 2015 on the next. To do that, I need to to identify the year field as the field where we will make our breaks.
In other words, when each item under year ends, I want to have a blank page, or rather I want the printing to start on a new page. So I'll click the Analyze contextual tab of the ribbon, which is active because a pivot table field is selected. Then I will click any cell that contains a year value. So I'll go ahead and click 2014, that makes the year the active field. And then I can click the Field Settings button.
In the Field Settings dialog box, I can see that the year field is selected, and I want to change this print settings, so I'll go ahead and click the Layout and Print tab. There are a number of layout options at top that I'm not going to bother with, but at the bottom, I see in the print area, that I can check this box to insert a page break after each item. And again, for the year field, the first item is 2014 and the second is 2015.
That's the setting I want to change, so I'll go ahead and click okay. And when I press Control P to print preview, I can see that 2014 appears on the first page and scrolling down, that 2015 appears on the second. And I'll click the Go Back button to go back to the main workbook. And then I'll click Field Settings again. Go to layout and print and clear that check box and click okay so that I can make another change.
Another way to print items from a pivot table on separate pages, is to create a separate worksheet for each of those items. You do that using the Report Filters area, or the Filters area, as it's called. To do that you need to add the field that you want to separate your values to the filters area, so I will drag the year field from the field list to the filters area, and when I do, you can see that Excel adds it to the Filters area and removes it from the rows area.
So now I have my Report Filter Field at the top, which is year. Now I want to create a separate worksheet for each value in the Year drop down list. So if I click the Year field's filter arrow I see that I have 2014 and 2015. So I'll click Cancel. Now I can click the Options button's down arrow, that's on the Analyze tab, and in the pivot table group. So again, I'm not clicking the button itself.
I'm clicking the down arrow to the right of it, and the second item in the list is Show Report Filter Pages. And when I click that item, the dialogue box called Show Report Filters Page disappears and I can see that I will show all Report Filter pages of the Year field. And when I click okay, Excel creates two new worksheets, and you can see, I'll zoom in a bit, to make the worksheet easier to see.
You can see that there is a new pivot table, and it has the Year Field Report filter already set to the year 2014. The worksheet called 2015, and again I will zoom in on that, has the Report Filter Field set to 2015. So again, these are fully fledged pivot tables that you can work with anyway you want, but if all you wanted to do was print a particular year, Excel created pivot tables based on those values from the year field and you can print the worksheets separately or print them both together using standard techniques.
The built in Excel 2016 pivot table functionality enables you to separate your data when you print without necessarily separating that data in the workbook. Creating new worksheets for each item in the filters 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 pivot table's organization.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Connecting to data sources
- Consolidating data from multiple data sources
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables with styles and formats
- Applying conditional formats
- Creating PivotCharts
- Printing PivotTables
- Running macros
- Creating a PivotTable using the data model