Excel Pivot Table Report – Field Settings, Expand or Collapse Fields & Items, Refresh Data, Change Data Source & Show or Hide options
As applicable to Excel 2007
Excel Pivot Tables Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
4. Pivot Table Report – Field Settings, Expand/Collapse Fields & Items, Refresh Data, Change Data Source & Show/Hide options
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Pivot Table Options tab – Active Field group, Data group, and Show / Hide group
Customizing a Pivot Table report: When you insert a Pivot Table, a blank Pivot Table report is created in the specified location, and the ‘PivotTable Field List’ Pane also appears which allows you to Add or Remove Fields, Move Fields to different Areas and to set Field Settings. The ‘Options’ and ‘Design’ tabs (under the ‘PivotTable Tools’ tab on the ribbon) have various tools to customize the Pivot Table report and determine its structure, layout and design. In this section we discuss the tools available in the Active Field, Data, and Show/Hide groups of the ‘Options’ tab.
Active Field group:
‘Value Field Settings’ dialog box:
Set Number Format for Values Field:
Select a cell in the values area of the Pivot Table report, click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, click ‘Field Settings’ in ‘Active Field’ group -> this opens the ‘Value Field Settings’ dialog box -> at the bottom left click on ‘Number Format’ which will open the ‘Format Cells’ dialog box. Here you can set the appropriate number format.
Another way is to select a cell in the values area in the Pivot Table report and right click, select ‘Value Field Settings’ from the list which will open the ‘Value Field Settings’ dialog box, and then proceed as above.
You can also click on the Values field in the Areas Section of the ‘PivotTable Field List’ Pane, select ‘Value Field Settings’ from the list which will open the ‘Value Field Settings’ dialog box and then proceed as above.
Summary Functions and Custom Calculations:
The tabs ‘Summarize by’ & ‘Show values as’ in the ‘Value Field Settings’ dialog box have been discussed in detail in the section ‘Calculating the Value Fields – Value Field Settings (Summary Functions & Custom Calculations)’.
‘Field Settings’ dialog box:
Opening the dialog box:
Select a cell in the rows or columns or report filter area of the Pivot Table report, click ‘Field Settings’ in the ‘Active Field’ group which will open the ‘Field Settings’ dialog box. Another way is to select a cell in the rows or columns or report filter area in the Pivot Table report and right click, select ‘Field Settings’ from the list which will open the ‘Field Settings’ dialog box. You can also click on the Row Labels, Column Labels or Report Filter fields in the Areas Section of the ‘PivotTable Field List’ Pane, select ‘Field Settings’ from the list which will open the ‘Field Settings’ dialog box.
‘Subtotals’ section of ‘Subtotals & Filters’ tab:
The ‘Subtotals’ section of ‘Subtotals & Filters’ tab (in the ‘Field Settings’ dialog box) has options of ‘Automatic’ ‘None’ or ‘Custom’. For outer row or column labels you can choose any one: ‘Automatic’ will display subtotals by the default summary function; ‘None’ will not display subtotals; and ‘Custom’ will allow you to select one or more from the list of Functions that can be used to Subtotal. Functions that can be used to Subtotal include Sum, Count, Average, Max, Min, Product, Count Numbers, StDev, StDevp, Var & Varp. These functions have been discussed in detail in the section ‘Calculating the Value Fields – Value Field Settings (Summary Functions & Custom Calculations)’. For inner row or column labels choosing ‘Automatic’ or ‘None’ will be irrelevant, and hence choose ‘Custom’ which will allow you to select one or more from the list of Functions that can be used to Subtotal.
‘Layout’ section of the ‘Layout & Print’ tab:
In the ‘Layout’ section of the ‘Layout & Print’ tab (in the ‘Field Settings’ dialog box), you can select either ‘Show item labels in outline form’ or ‘Show item labels in tabular form’. Remember, the tabular form will display subtotals only at the bottom. If you select the option of outline form, you can select either one or both the check boxes under it – selecting the first check box changes the layout to compact form and selecting the second check box will show subtotals at the top (deselecting the second check box will show subtotals at the bottom). For the layouts of Compact Form or Outline Form, Subtotals can be shown either at the Top or Bottom.
Subtotals setting in the ‘Design’ tab under the ‘PivotTable Tools’ tab on the ribbon:
Click Subtotals in the ‘Layout’ group and select from the 3 options – “Do Not Show Subtotals”, “Show all Subtotals at Bottom of Group” and “Show all Subtotals at Top of Group”. Subtotals for Tabular Form layout will always be shown at the bottom, irrespective of the setting to show these at the Bottom or Top. For the layouts of Compact Form or Outline Form, Subtotals can be shown either at the Top or Bottom, as per their setting. This has been discussed in detail in the section ‘Pivot Table report Layout, Style Options and Styles (Design tab)’.
Include new items in manual filter – ‘Filter’ section of the ‘Subtotals & Filters’ tab:
If a Manual Filter has been applied to a field in a Pivot Table report, and a new item gets added in that field in the source data, refreshing the Pivot Table displays the new item in the Pivot Table report wherein it automatically gets selected in the manual filter, even if it does not meet the filter criteria. This happens if the check box of ‘Include new items in manual filter’ is selected in the ‘Filter’ section of the ‘Subtotals & Filters’ tab in the ‘Field Settings’ dialog box. To not display this new item which is added in source data, clear the check box of ‘Include new items in manual filter’.
Insert page break after each item – ‘Print’ section of the ‘Layout & Print’ tab:
To print a Pivot Table report with specified parts on separate pages, you can set a page break after an item of any outer row label. In the ‘Field Settings’ dialog box, click on ‘Layout & Print’ tab, and then in the ‘Print’ section select check box of ‘Insert page break after each item’. This will print each item of the outer row label which is right-clicked, on a separate page. This has been discussed in detail in the section ‘Printing a PivotTable report’.
Expand or Collapse Fields and Items:
In a Pivot Table report, you can expand and collapse fields or items to any level. This will show or hide details of the respective field or item. You can expand and collapse for the next level, or upto a certain level, or for all levels.
Show/Hide Expand and Collapse Buttons:
Click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, in the ‘Show/Hide’ group, clicking on ‘+/- Buttons’ will alternatively show or hide the buttons which allow you to expand or collapse items within the Pivot Table report. You can also show or hide these buttons in the dialog box of ‘PivotTable Options” – in the ‘Display’ section under the ‘Display’ tab, select or clear the check box of ‘Show expand/collapse buttons’.
Expand or Collapse Options:
Select an item in the Pivot Table report, and right click, then move cursor over ‘Expand/Collapse’ in the list and click from the list of options: (i) Expand: shows details for the selected item; (ii) Collapse: hides details for the selected item; (iii) Expand Entire Field: shows details for all items of the field whose item is selected item; (iv) Collapse Entire Field: hides details for all items of the field whose item is selected item; (v) Expand to <Field Name>: shows details upto the level of the Field Name specified; (vi) Collapse to <Field Name>: hides details upto the level of the Field Name specified. Note that if you select a Row Label or Column Label (instead of an item) in the Pivot Table report, then the entire field will Expand or Collapse when you click any of the above options. The options of ‘Expand Entire Field’ and ‘Collapse Entire Field’ can also be accessed in the ‘Active Field’ group of the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon (remember to select a cell in the rows or columns area of the report). The ‘+/- Buttons’ on the left of each item, allow you to Expand or Collapse items within the Pivot Table report. Another way is by Double-clicking an item in the outer row or column which will alternatively Expand and Collapse it.
Double-clicking an item in the inner row or column, or selecting a cell in the inner row or column and clicking on ‘Expand Entire Field’ will open the ‘Show Detail’ dialog box. In this dialog box you can choose the field containing the detail you want to show, and that field will be added to the Pivot Table report which detail will show for the respective inner row or column items.
Example – how Expand and Collapse works:
Refer Image 1a – Pivot Table report shows details for all fields and items. Starting from Image 1a, select the item ‘Canada’ in the Report and click ‘Collapse’ – refer Image 1b – details for Canada get hidden. Starting from Image 1a, select the item ‘Canada’ in the Report and click ‘Collapse Entire Field’ – refer Image 1c – details for the entire field of ‘Country’ ie. for all countries, get hidden. Starting from Image 1a, select the item ‘Canada’ in the Report and click ‘Collapse to Region’ – refer Image 1d – details for the Region containing Canada (ie. North America) gets hidden. However, Starting from Image 1a, if we select the field ‘Country’ in the Report and click ‘Collapse to Region’ – refer Image 1e – details for all Regions get hidden. If we select the outermost field ‘Car Models’ and click ‘Collapse’ – refer Image 1f – details for all levels get hidden.
Show Details for a Value Field in a Pivot Table Report:
To view details of data for a value field, which shows the basis on which the value is arrived at (ie. drills down to detailed data from the data source), select a cell in the values area and right click and then select ‘Show Details’. A new worksheet gets created which shows the detailed data. Alternate method to show details is to double-click a cell in the values area. You can enable or disable access to this feature of ‘Show Details’ by selecting or clearing the check box of ‘Enable show details’. Enabling the feature of ‘Show Details’, Image 2a shows the Pivot Table where cell having value of 6,900 in the ‘Sales-$’ column is double-clicked and Image 2b shows a new worksheet created which shows the drilled out details of how the value is arrived at.
Data group:
Refresh Data in a Pivot Table report:
You have different ways to refresh and update data in a Pivot Table report with any changes made in the source data. Click ‘Refresh’ in the ‘Data’ group and then click on ‘Refresh’ to update the selected Pivot Table report or click on ‘Refresh All’ to refresh all Pivot Tables in the workbook. Another way is to right click a cell in the Pivot Table report, click on ‘Refresh’ in the commands list, to update the selected report. Selecting the check box ‘Refresh data when opening the file’ (in the dialog box of ‘PivotTable Options”, this check box is in the ‘PivotTable Data’ section of the ‘Data’ tab) will automatically reload and refresh the data into the Pivot Table report when the workbook is reopened.
Change the Data Source Range of a Pivot Table report:
You can change the Data Source Range of a Pivot Table report to expand or delete any part of your source data. Click ‘Change Data Source’ in the ‘Data’ group, in the ‘Change PivotTable Data Source’ dialog box which opens, select the option of ‘Select a table or range’ where you can manually change the data source range, and then ‘Refresh’ the report for changes to take effect. If your source data is in a named range, then upon any change in the named range, just ‘Refresh’ the Pivot Table report. To Refresh a report, right-click a cell in the Pivot Table report and then click on ‘Refresh’ in the list of commands.
Show/Hide group:
Show or Hide the Pane:
In the ‘Show/Hide’ group, clicking the ‘Field List’ will alternatively show or hide the Pane. You can also close the Pane by: (i) at the top right of the Pane, click on ‘Task Pane Options’ (the down arrow) and in the list click ‘Close’; or (ii) at the top right of the Pane, click on ‘Close’ (ie. x).
Show/Hide Expand and Collapse Buttons:
In the ‘Show/Hide’ group, clicking on ‘+/- Buttons’ will alternatively show or hide the buttons which allow you to expand or collapse items within the Pivot Table report. You can also show or hide these buttons in the dialog box of ‘PivotTable Options” – in the ‘Display’ section under the ‘Display’ tab, select or clear the check box of ‘Show expand/collapse buttons’.
Show or Hide Field Headers:
Click ‘Field Headers’ in the ‘Show/Hide’ group to alternate between showing and hiding field headers (viz. row and column labels).