Excel Pivot Table Design & Layout, Pivot Table Styles
As applicable to Excel 2007
In this section we discuss the Design tab which contains options to format a Pivot Table report and has three groups – Layout, Pivot Table Style Options and Pivot Table Styles.
Excel Pivot Tables Tutorial:
2. ‘PivotTable 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.
10. Pivot Table Report Layout, Compact, Outline and Tabular Form, Pivot Table Styles and Style Options, Design tab
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
The Design tab
Under the ‘PivotTable Tools’ tab on the ribbon there is a ‘Design’ tab. This contains special formatting options for a Pivot Table report, and has three groups: (i) Layout – this has settings for subtotals, grand totals, layout forms and blank rows; (ii) PivotTable Style Options – has options for display of row & column headers and banded rows & columns; and (iii) PivotTable Styles – you can choose from a variety of visual styles in the gallery or add a custom style.
Layout group
Report Layout
To adjust the layout of a Pivot Table report, click ‘Report Layout’ in the ‘Layout’ group and select from the 3 options – “Show in Compact Form”, “Show in Outline Form” and “Show in Tabular Form”. Pointing the mouse cursor over “Report Layout”, displays the caption “Adjust the report layout. The compact form optimizes for readability, while the tabular and outline forms include field headers.” The compact form minimizes scrolling by including the row labels in one column wherein each field is differentiated by indenting and field headers are not shown. The compact form is also the default layout in Excel 2007. The three Layout Forms are explained in detail below, with the help of visuals.
Image 1a shows the Pivot Table in a Compact Form; Image 1b shows the Pivot Table in a Outline Form; and Image 1c shows the Pivot Table in a Tabular Form. The Compact Form does not show field headings (‘Car Models’ and ‘Region’) and also shows both these fields in column A, as differentiated by an indent. The Outline Form shows field headings (‘Car Models’ and ‘Region’) and in separate columns ie. column A and column B. Values for the second field of ‘Region’ (ie. in column B) start after leaving a blank row at the top ie. cells B5, B9 & B13 are blank in Image 1b. The Tabular Form also shows field headings (‘Car Models’ and ‘Region’) and in separate columns ie. column A and column B, similar to the Outline Form. However, values for the second field of ‘Region’ (ie. in column B) start without leaving a blank row at the top (Image 1c). Note: Because Tabular Form does not leave a blank row at the top, Subtotals 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.
Set Indenting in Compact Form: As explained above, in a Pivot Table Report in Compact Form, each field is differentiated by indenting. You can set the number of characters for indenting by clicking the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, click ‘Options’ in the ‘PivotTable’ group and click ‘Options’ again which will open the dialog box of ‘PivotTable Options’ -> in the ‘Layout’ section of the ‘Layout & Format’ tab, select a number in the scroll box of ‘When in compact form indent low labels’.
Subtotals
For Subtotals setting in a Pivot Table report, 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, as explained above (refer Image 1c). For the layouts of Compact Form or Outline Form, Subtotals can be shown either at the Top or Bottom, as per their setting (refer Images 1a and 1b which show Subtotals at the top).
Select a cell in the rows or columns area of the Pivot Table report, click ‘Field Settings’ in the ‘Active Field’ group which will open the ‘Field Settings’ dialog box:
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’. 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.
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.
Grand Totals
For Grand Totals setting, to display or hide grand totals in a Pivot Table report click Grand Totals in the ‘Layout’ group and select from the 4 options – “Off for Rows and Columns”, “On for Rows and Columns” , “On for Rows Only” and “On for Columns Only”. You can also set Grand Totals by clicking the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, click ‘Options’ in the ‘PivotTable’ group and click ‘Options’ again which will open the dialog box of ‘PivotTable Options’ -> select or clear the relevant checkboxes in the ‘Grand Totals’ section of the ‘Totals & Filters’ tab.
Blank Rows
Add Blank Rows after each Row Item:
To insert (or delete) blank rows in a Pivot Table report, click ‘Blank Rows’ in the ‘Layout’ group and select from the 2 options – ‘Insert Blank Line after Each Item’ and ‘Remove Blank Line after Each Item’. Inserting a Blank Row segregates grouped items. Note that this procedure adds a blank row after each outer row item and not after each item in the innermost nest. Refer Image 2a which shows a Pivot Table Report before inserting Blank Rows, and Image 2b shows Report after inserting blank rows after each outer row item. Blank Rows are not inserted after the innermost nested items of the “City” Field.
Add Blank Rows only after the selected Row Item:
Select a cell in the row labels area in the Pivot Table and then click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Field Settings’ in the ‘Active Field’ group, and in the ‘Field Settings’ dialog box which opens select the ‘Layout & Print’ tab and in the ‘Layout’ section select the check box of ‘Insert blank line after each item label’. Note that this procedure will add blank rows only after each item of the relevant row field where the cell is selected. Note further that blank rows can be added only after outer row items and not after each item in the innermost nest. Refer Image 2a which shows a Pivot Table Report before inserting Blank Rows, and Image 2c shows Report after inserting blank rows after each item of the ‘Region’ field (cell containing the item ‘Europe’ was selected while inserting blank rows).
Pivot Table Styles group
You can easily change the style of a Pivot Table Report, as to its visual appearance in terms of format and color scheme. Excel 2007 provides a gallery of predefined styles from which you can choose and you can also create your own Custom Style.
Pivot Table Styles
To choose a visual style for a Pivot Table report, in the ‘PivotTable Styles’ group, click the More arrow at the bottom of the scroll bar which opens the Pivot Table Styles Gallery which has four sections of Custom, Light, Medium & Dark and various styles in each section. Provided the ‘None’ style under the ‘Light’ section has not been applied to the report, running your mouse cursor over the styles in the gallery will show its preview (see the selected Pivot Table for the visual effect). Refer Image 3 where the ‘Pivot Style Dark 4’ style in the Dark section has been applied to the Pivot Table report.
To apply a style to a Pivot Table report, select any cell in the Pivot Table report, right click on the style you wish to apply and select between ‘Apply and Clear Formatting'(this will clear any formatting, say, if the text is center-aligned in the Pivot Table cells, this alignment will be removed) or “Apply (and Maintain Formatting)”. You can also select any cell in the Pivot Table report and just select the custom style you wish to apply – this will ‘Apply (and Maintain Formatting)’.
To remove or clear any existing style from the Pivot Table report, in the PivotTable Styles Gallery at the bottom, below the 4 sections, click on “Clear”.
Add a Custom Style: To add a custom style, click ‘New PivotTable Style’ in the PivotTable Styles Gallery at the bottom, which opens the ‘New PivotTable Quick Style’ dialog box. In the dialog box, choose from the list of options in the ‘Table Element’ section (contains options as to which area of the Pivot Table to include), apply a Format, select the check box at the bottom if you want to set as default, enter a Name for the style, and click OK – the custom style will get added to the “Custom” section which is visible at the top within the 4 sections. You can also right-click and ‘Set as Default’ a custom style or any style in the Gallery.
Modify, Duplicate or Delete a Custom Style: Right click on the custom style (in the Custom section of the PivotTable Styles Gallery) and you will get options to “Modify”, “Duplicate” or “Delete” the custom style you have created. Using the Duplicate option is useful when you do not wish to create a custom design right from start and you can just copy/duplicate an existing style and Modify the same. Note that you can also duplicate any style in the Gallery and Modify the same.
Example – Create and Add a Custom Style. In the PivotTable Styles Gallery at the bottom, below the 4 sections, click ‘New PivotTable Style’ which opens the ‘New PivotTable Quick Style’ dialog box. Select ‘Grand Total Row’ in the ‘Table Element’ section, click on ‘Format’, this opens the ‘Format Cells’ dialog box, select yellow color in the ‘Fill’ tab and click “OK” twice -> this style gets added in the Custom section. Image 4 shows this style applied to the Pivot Table report.
Pivot Table Style Options group
Selecting PivotTable Style Options makes readability easier in a Pivot Table. This group has check boxes for Row Headers, Column Headers, Banded Rows & Banded Columns which can be selected or cleared.
Selecting Row Headers or Column Headers displays special formatting for the row headers (or column headers) of the Pivot Table. If say, you have selected the Row Headers checkbox, the style chosen in the PivotTable Styles Gallery will include Row Headers also to apply special formatting. The ‘special formatting’ could be bold fonts, or a different background color.
Selecting Banded Rows or Banded Columns will band rows (or columns) wherein alternate rows (or columns) are formatted or shaded differently, which provides easier readability. If say, you have selected the Banded Rows/Columns checkbox, the alternate rows/columns will be formatted differently per the style chosen in the PivotTable Styles Gallery. The ‘formatted differently’ could be a background shading difference for alternate rows/columns ie. even and odd rows will be of a different color, or a colored border between rows/columns.
Themes: In the “Page Layout” tab on the ribbon, click on “Themes” in the ‘Themes’ group, to check the Current Theme and you can therein choose a new Theme for the entire document to change the overall design including colors, fonts and effects. The Pivot Table will also show accordingly.
Example of applying Pivot Table Style Options – refer Images 5a to 5e:
Pivot Style chosen from the Dark section of the PivotTable Styles Gallery: ‘Pivot Style Dark 7’.
Image 5a: No PivotTable Style Option is selected ie. Row Headers, Column Headers, Banded Rows & Banded Columns have NOT been selected.
Image 5b: Only Row Headers has been selected (Column Headers, Banded Rows & Banded Columns are not selected).
Image 5c: Only Column Headers has been selected (Row Headers, Banded Rows & Banded Columns are not selected).
Image 5d: Only Banded Rows has been selected (Row Headers, Column Headers & Banded Columns are not selected).
Image 5e: Only Banded Columns has been selected (Row Headers, Column Headers & Banded Rows are not selected).
Image 5f: ALL PivotTable Style Options (ie. Row Headers, Column Headers, Banded Rows & Banded Columns) have been been selected.