In a pivot table, you can show all the summarized data, or focus on specific details. One way to hide information is with filtering, and that affects the pivot table totals.
Show Percent of Subtotal in Pivot Table
A pivot table is a great way to summarize data in Excel, and you can show sums, counts, averages, and other functions.
Group and Ungroup Text Items in Pivot Table
The Grouping dialog box isn’t available if you try to group items in a text field. Instead, you can manually group the items.
- Select the all of the items that you want to group
- Right-click on one of the selected items, and click Group
Continue reading “Group and Ungroup Text Items in Pivot Table”
Block New Pivot Items in Filtered Field
When you click on the arrow in a pivot table heading, you can manually filter the field’s items, by adding and removing check marks from the list of pivot items. That lets you focus the report on specific items, and temporarily ignore the other data.
How to Adjust the Pivot Table Field List
When you create a pivot table, and select a cell in it, by default, a pivot table field list should appear, at the right of the Excel window.
Arrange Value Fields Vertically for Printing
Most of the time, you probably use a pivot table on your computer, but occasionally you might need to print one, to share with other people in your company.
Onscreen, pivot tables can get quite wide, and you can scroll back and forth, to see everything. In the pivot table shown below, there are 3 Values fields, and they are repeated for each of the items in the Column field.
Continue reading “Arrange Value Fields Vertically for Printing”
Change the Report Filter Layout
By default, when you add Report Filters to a pivot table, they are shown in a single vertical list, above the body.
Create a Simple Calculated Field
Besides using the fields from a pivot table’s source data, you can create calculated fields. These fields are formulas that can refer to other fields in the pivot table, to perform calculations on the summarized amounts.
In this example, the RepBonus calculated field is added to the pivot table, to show the bonus amounts paid on product sales.
Quickly Move a Large Pivot Table
Sometimes you have to move a pivot table, after you’ve created it. That’s not too difficult if the pivot table is small – just select all the cells, cut them, and paste in a new location.
With a very large pivot table, it’s a bit harder – you have to scroll through the worksheet, and try to select all the cells, including any report filters.
GetPivotData Problems With Subtotals
To pull data from a cell in a pivot table, you can use a normal cell reference, such as =B5, or you can use the GetPivotData function, which is specially designed to extract data from a pivot table.