Pivot Table Tools Design Tab Autofit Column Widths On Update

After creating a PivotTable and analyzing your data, you may want to modify or change the design of a pivot table in terms of the layout and format. The ideology behind designing your pivot is that it will enable people to easily read and scan the data for details.

To alter the Design of Pivot Tabel click on any cell within the Pivot Table, and then click on Design Tab from the Ribbon. Three sections namelyLayout,PivotTable Style Options andPivotTableStyles will help in designing of the Pivot.

Contents

1 Change the Layout of a Pivot Table
- 1.1 Subtotal
- 1.2 Grand Totals
- 1.3 Report Layout
- 1.4 Blank Rows
2 Pivot Table Styles
3 Pivot table Style Options
4 Pivot Table Options
- 4.1 Layout & Format
- 4.2 Display

Change the Layout of a Pivot Table

Under the layout section of a PivotTable, you can change the way fields, columns, rows, subtotals, empty cells and lines are displayed. To change the layout of the Pivot click on Design > Layout

Layout section : This includes Subtotals, Grand Totals, Report Layout and Blank Row.

Subtotal: Hide/ Display subtotals. Subtotals are helpful in case you have region or segments you want to total. You can Display subtotals at the top or bottom.
Grand Totals: You can Ascertain totals for rows or columns.
Report Layout: opt an option from compact, outline or tabular. Additionally, you can hide repeating labels.
Blank Row: Add or remove blank row for clear readability.

Subtotal

Subtotal is the total of one set of filed values either in a row or column in a pivot table. Subtotals are helpful in case the data consist of region or segments which you wish to total. Options under Subtotal are either to display or not. Moreover, you can choose to display the Subtotals on top or bottom of the Group. You can see the options under Subtotals from the Design Tab.
Note: Subtotal will not function when you have only a single row or column in your filed list.

Grand Totals

As the name says grand total adds up the total of the Values. You can add Grand Totals for both rows and column or either one as per requirement. Similarly, when not required you can turn them off.

Note : Value field is a must for Grand Total and Subtotal to result in an output.

Report Layout

There are 3 report layout options available. They are as follows:-

Compact form displays items from different row area fields in one column and uses indentation (space) to distinguish the items from different fields. Row labels take up less space thereby giving the name compact form. This gives more space for numeric data. This is the default layout form for PivotTables.

Tabular form displays one column per field and provides space for field headers. Below is a pictorial representation of Tabular form in a Pivot table. Notice the difference, in compact form, the names were under each Sector and in Tabular form, the names are in a separate column but still under the sector. Another point to notice is that in Compact form is headed with Row labels, whereas in Tabular form you'll notice the header to be Sector.

Outline form is similar to tabular form but items in the next column are displayed one row below the current item, unlike Tabular form. Notice by default Subtotal is added in Outline form. However, you remove them from the Subtotal option.

Repeat All Item Labels is an impressive feature which saves time and helpful while filtering. Notice in the above images the Sectors appear only beside the first entry i.e. BFSI is there only against the first record. To get them for all the entries use the Repeat All Item Labels under Report Layout. Below is an image depicting how the repeat all item labels works.

Blank Rows

Bank rows can be useful to distinguish and space your data in a way which is easier to interpret. Blank rows are added at the end of each row item. Notice in the below image once the BFSI sector there is a blank row and then the next sector, that is consulting starts. You can remove them from the same section using remove all the blank rows.

Pivot Table Styles

Lastly, thePivotTableStyles section which facilitates funky style to your Pivot table. This enables you to change the colour and apply a different format to your desired selection. You can apply a predefined style, banded rows, and conditional formatting. Else you can create your own style. To do so click on the more option under PivotTable Styles as below.

Pivot Table Design and Layout in Excel 1

A drop will appear as below. Toward the end of the drop-down, you will notice an option saying New PivotTable Style. Click on it and you can create the style as per your choice.

Pivot Table Design and Layout in Excel 2

You can remove any existing style which is already applied, to do so follow the above steps and click the Clear option. The above image shows the clear option towards the end.

Pivot table Style Options

PivotTable Style Options consist of Header, Banded Rows and columns. The Row Headers option displays special formatting for the first row of the table. Similarly, Column Headers format the column. If you wish to remove them you can uncheck them. Next to them is yet another impressive feature called Banded Rows and Banded Columns. The purpose of them is to colour alternative rows or columns with a combination of a lighter and darker colour format.

All the options can be found under the Design Tab when in Pivot Table and it looks like as below

Below is an image of Banded Rows and formatted first Column header of the pivot table.

Notice the Row header is not formatted but the rows are banded, i.e. Alternative rows are coloured with a darker shade of colour.

Pivot Table Options

ThePivotTable Options enables to control Layout & Format, Display and various other settings for a pivot table.

To access the pivot table options, right-click on any cell within the pivot and select Pivot Table Options.

Layout & Format

This section deals with the layout of the Pivot Table using the following. Before we get into the details, take a look at how the Layout & Format looks like.

Merge and centre cells with labels: This selection basically performs merge and centre for all labels both in a row or column. This works best when in a tabular layout.
When in compact form indent row labels: This helps to indent rows in the row labels area when the PivotTable is in a compact format. You can select an indentation level ranging from 0 to 127.

Display fields in report filter area: The Down, Then Overoption displays fields in the report filter area from the top to the bottom, as fields are/were added. TheOver, Then Down option display fields in the report filter area from left to right as per the order of adding the fields.
Report filter fields per column: Type or select the number of fields to display before going into another column or row based on the setting ofDisplay fields in the report filter area.

For error values show: Select this checkbox, and then type the text that you wish to display instead of an error message such as "Invalid".
For empty cells show: Select this checkbox, and then type the text that you wish to display in case of a blank cell. For example "Empty".
Autofit column widths on update: Select this to automatically fit the size of columns based on the widest text or number value.
Preserve cell formatting on update. This saves the layout and format of the PivotTable so that it is used each time that you perform an operation on the PivotTable.

Display

Show expand/collapse buttons: This allows to display the plus or minus buttons that you use to expand or collapse the row or column labels.
Show contextual tooltips: Check the box to display tooltips that show value, row or column information for a field or data value. Uncheck to hide contextual tooltips.
Display field captions and filter dropdowns: This option enables to display or hide PivotTable captions at the top of the PivotTable and filter drop-down arrows on column and row labels.
Classic PivotTable layout: This is an amazing feature which enables to add rows, columns or values to the Pivot by just dragging and dropping into the Pivot Table directly. To activate this, simply check the box.

Notice your pivot table areas are highlighted by blue lines. Now instead of dragging into the field pane, you can directly drag the fields into the Pivot Table.

To know more on Pivot Table, click here.
For video assistance on reporting and analyzing using Pivot Tables click here.