Excel Pivot Table Report Layout & Format, Totals & Filters, Display, Printing & Data
As applicable to Excel 2007
Excel Pivot Tables Tutorial:
2. ‘Pivot Table Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data
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 dialog box
The Pivot Table Options dialog box has options for settings in respect of Layout & Format, Totals & Filters, Display, Printing & Data in a Pivot Table report. To launch the dialog box: under the ‘PivotTable Tools’ tab on the ribbon, click ‘Options’ tab, then click ‘Options’ in the ‘PivotTable’ group and click ‘Options’ again which will open the dialog box of ‘PivotTable Options’. The dialog box has 5 tabs of Layout & Format, Totals & Filters, Display, Printing & Data each of which have various settings for a Pivot Table report.
Layout & Format tab
Layout section
Merge and Center Cells with Labels, in the Pivot Table Report:
You can merge and center the outer cells which contain Row Labels, Column Labels, or Items, so that they will center horizontally or vertically. The unmerged cells by default have a left-justify alignment and appear at the top of the Item group. To merge and center, in the ‘Layout’ section of the ‘Layout & Format’ tab, select the check box ‘Merge and center cells with labels’ (deselecting will unmerge). Refer Image 1a which shows a Pivot Table before applying this setting and refer Image 1b which shows the effect of merge and center.
Set Indenting in Compact Form: In a Pivot Table report shown in Compact Form, each field is differentiated by indenting. You can set the number of characters for indenting in the ‘Layout’ section of the ‘Layout & Format’ tab, by selecting a number in the scroll box of ‘When in compact form indent low labels’. Refer Image 2a which shows the indent number of 1 for the PivotTable in Compact Form, and Image 2b shows the Pivot Report with indent number set to 4.
Display Fields in Report Filter Area, in a Pivot Table Report:
In the ‘Layout’ section of the ‘Layout & Format’ tab, you have 2 options in the pull-down of ‘Display fields in report filter area’: (i) Down, Then Over – selecting this will display fields (in the order they are added) in the Report Filter area vertically from top and going downwards, before the next column starts; and (ii) Over, Then Down – selecting this will display fields (in the order they are added) in the Report Filter area horizontally from left to right, before the next row starts. You can select the number of fields to display before the next column or row starts by selecting or entering the number in the scroll box of ‘Report filter fields per column’. Refer Image 3a which shows how the 4 fields of Year, Car Models, Region & Country appear in the the setting of Down, Then Over with the number of fields entered as 2, whereas Image 3b shows how they appear in the setting of Over, Then Down.
Format section
Determine Display of Errors and Empty Cells in a Pivot Table Report:
Wherein data consists of blank cells (could be blank lines also) or error values which appear in a Pivot Table Report, you can control and determine how these will display in the Report, in the ‘Format’ section of the ‘Layout & Format’ tab,
Error Display: Select the check box ‘For error values show’ and enter the value or text in the box which you wish to display for error values. If you enter ‘Error’ in the box, the report will show the text ‘Error’ for all error values. Leaving the box blank will show blank cells for error values. If the check box is not selected, which is the default, the box will appear as shaded grey, and this will show the actual error in the Pivot Table Report viz. the error could show as #DIV/0!.
Empty Cells Display: Select the check box ‘For empty cells show’ and enter the value or text in the box which you wish to display for error values. If you enter ‘No Value’ in the box, the report will show the text “No Value” for all blank cells. Leaving the box blank (the default setting) will show blank cells in the Report. If the check box is not selected, the box will appear as shaded grey, and the Report will show zeros for all blank cells.
Retain or Adjust Column Widths and Cell Formatting on Update:
In the ‘Format’ section of the ‘Layout & Format’ tab there are 2 checkboxes: (i) ‘Autofit column widths on update’: Selecting the check box will adjust column width automatically to the widest text or numerical value when you update or refresh the Pivot Table. Deselecting will retain the existing column width; and (ii) ‘Preserve cell formatting on update’: Selecting the second check box will retain cell formatting (say, if the text in a cell is center-aligned, this alignment will be retained) when you update or refresh the Pivot Table.
Totals & Filters tab
Grand Totals section
In the ‘Grand Totals’ section of the ‘Totals & Filters’ tab, you can show or remove Grand Totals by selecting or deselecting the check boxes of ‘Show grand totals for rows’ and ‘Show grand totals for columns’. Another way to display or hide grand totals in a Pivot Table report, is by clicking the Design tab under Pivot Table Tools on the ribbon, then click Grand Totals ( in “Layout” group) and select from the 4 available options – ‘Off for Rows and Columns’, ‘On for Rows and Columns’, ‘On for Rows Only’ and ‘On for Columns Only’. Refer Image 4a which shows grand totals for both rows and columns whereas in Image 4b only the ‘Show grand totals for columns’ is checked.
Filters section
Allow Multiple Filters per Field, in a Pivot Table Report:
In the ‘Filters’ section of the ‘Totals & Filters’ tab, select the ‘Allow multiple filters per field’ check box to allow more than one filter (ie. mutiple filters) to be set for a Field. Clearing the check box will allow only a single filter to be set.
Sorting section
In the ‘Sorting’ section of the ‘Totals & Filters’ tab, you can enable ‘Use Custom Lists when sorting’ by selecting its check box or disable by clearing the check box. In the case of this option being disabled, if AutoSort is selected in the “More Sort Options” dialog box, the Ascending or Descending sort order will be alphabetical, whereas when this option is enabled then the Ascending or Descending sort order is based on the custom list. However, if a custom list is selected in the ‘First key sort order’ (ie. AutoSort is not selected) in the ‘More Sort Options’ dialog box, then Ascending & Descending sorting will be based on the custom list order which is selected, both when the use of Custom Lists is enabled or disabled. For details see the chapter of ‘Sort Data in a Pivot Table Report’.
Display tab
Display section
Show/Hide Expand and Collapse Buttons:
In a Pivot Table, you can expand and collapse fields or items to any level. This will to 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. In the ‘Display’ section of the ‘Display’ tab, select or clear the check box of ‘Show expand/collapse buttons’ to show or hide the +/- Buttons which allow you to expand or collapse items within the Pivot Table. Another way to do this is to 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. Image 5a shows the +/- Buttons in the Pivot Table while Image 5b hides the Buttons. Refer chapter on ‘Expand/Collapse Fields & Items’ for further details.
Show Contextual Tooltips, in a Pivot Table Report:
Running your mouse cursor over data items in a Pivot Table report can display tooltips which show value, row or column details. To display tooltips, in the ‘Display’ section of the ‘Display’ tab select the check box of ‘Show contextual tooltips’. Clear the check box to not display these tooltips. Refer Image 6 which shows the tooltips for France in the Country field.
Display field captions and filter drop downs:
To display Row Labels and Column Labels at the top of the Pivot Table and the filter drop down arrows with these Labels, in the ‘Display’ section of the ‘Display’ tab select the check box of ‘Display field captions and filter drop downs’. Clear the check box if you do not wish to display these. Image 7a shows ‘Display field captions and filter drop downs’ in a Pivot Table and Image 7b does not display.
Classic Pivot Table Layout and enable Dragging of Fields in the Pivot Table Report:
To display the Classic Pivot Table layout which also enables dragging of fields within our outside the Pivot Table report, in the ‘Display’ section of the ‘Display’ tab select the check box of ‘Classic PivotTable layout (enables dragging of fields in the grid)’. Clear the check box if you do not wish to display the Classic layout and to disable dragging of fields. Image 8a shows the Classic layout and note the 4-pointed arrow on the Region field which enables dragging, while Image 8b shows the Pivot Table without the Classic layout where fields cannot be dragged.
Field List section
Sort in the Fields section of ‘PivotTableField List’ Pane:
In the ‘Field List’ section of the ‘Display’ tab select from the 2 option buttons: (i) ‘Sort A to Z’ – to sort in ascending order; and (ii) ‘Sort in data source order’ – to sort as per the order in which fields appear in the Source Data range. Note that this sorts the Fields in the Fields Section of the “PivotTable Field List” Pane, and not in the Pivot Table report. Image 9a shows sorting in data source order whereas Image 9b shows sorting A to Z in ascending order.
Printing tab
For details see the section of ‘Printing a Pivot Table report’.
Data tab
Pivot Table Data section
In the ‘PivotTable Data’ section of the ‘Data’ tab you have the following options:
1. ‘Save source data with file’:
You can Improve Performance of a Pivot Table by selecting or clearing the check box ‘Save source data with file’, as explained below.
While creating a Pivot Table, a hidden copy, referred as cache, of the source data is created by Excel. Excel uses this cache to make quick calculations for the Pivot Table, without disturbing the original source data.
If the source data is an Excel List or Database, having this database in an open worksheet will mean that two copies of data get retained in memory while creating the Pivot Table. Having the source data in a separate workbook and keeping the workbook closed will conserve memory because then Excel will retain only the cached data in memory.
If you use the same source data to generate a new Pivot Table both Pivot Tables use the same cache, which conserves memory because the number of source data copies stored in memory get restricted. Use this option when you need to generate multiple Pivot Tables from the same source data.
When you select ‘Use an external data source option’ while creating a Pivot Table, only one copy of the source data is retained in memory.
If you select the check box of ‘Save source data with file’, then on saving the workbook the cached data created for generating a Pivot Table also gets saved, resulting in a larger file size and more time taken to save the workbook because additional data is also saved. But on reopening the workbook, the cached data does not get loaded till the Pivot Table is edited or refreshed, which conserves memory.
On the other hand, deselecting the check box of ‘Save source data with file’, will mean a smaller file size and shorter save time because the cached data does not get saved while saving the workbook. In this case, on reopening the workbook, you will get the message “The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the Report.” and you will need to refresh the Pivot Table, and this process is slower than when the workbook is reopened using the option of ‘Save source data with file’. Alternatively, you can select the option ‘Refresh data when opening the file’ (in the same ‘PivotTable Data’ section), which will automatically reload the data into the Pivot Table when workbook is reopened.
2. ‘Enable show details’:
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 10a shows the Pivot Table where cell having value of 6,900 in the ‘Sales-$’ column is double-clicked and Image 10b shows a new worksheet created which shows the drilled out details of how the value is arrived at.
3. ‘Refresh data when opening the file’:
Selecting the check box ‘Refresh data when opening the file’ will automatically reload the data into the Pivot Table when workbook is reopened. The Pivot Table will be refreshed and updated, when the Excel workbook is opened, with any changes made in the source data.
Retain items deleted from the data source section
Deleted items in Source Data still appearing in Pivot Table filters?
When your source data changes and a previous appearing item is deleted, it will still show in the drop-down filter of the field it was in, in the Pivot Table Report even if you Refresh. One way of not showing this deleted item in the field is to remove the field from the Pivot Table Report (ie. deselect the field in the ‘PivotTable Field List’) and then ‘Refresh’ and then add the field back in the Report. The field will not contain the deleted item now. Another way of not showing the deleted item in the field is to select ‘None’ in the drop-down of ‘Number of items to retain per field’ in the section ‘Retain items deleted from the data source’ in ‘Data’ tab.
You can choose from the 3 options of Automatic, None or Max in the drop-down of ‘Number of items to retain per field’. The default setting is ‘Automatic’ which retains deleted items (in source data) of a field in the temporary cache of the Pivot Table. Choosing ‘None’ will not retain any item when it is deleted from the source data. You can also specify the maximum number of deleted items to be retained by entering a number upto 1,048,576. If you enter the number 3, then the first 3 deleted items of source data will get retained ie. if 4 items are deleted from a field in the source data, then only the first 3 items will show in the field filter in the Pivot Table Report.