Pivot Table Options and Methods

Pivot table options provide you more flexibility in customization of your data. You can set filters and change the formatting of Pivot Table. It can help you with printing and Data management so that you can work more efficiently. And the table will always look clean for analysis.

To Start with we have the following data source,

And the below pivot table made from the above source:

Now we will look into some of the methods and options provided by the Pivot table in Excel.

Pivot Table Options

To open the Pivot Table options dialog box,

Click on any cell inside the Pivot Table -> Click on Analyze tab under the PivotTable Tools tab present on the ribbon -> Click on Options

Found under the PivotTable category.

The dialog box will appear,

In this box you can find various options and methods to start with.

First let us look into the Layout and Format Tab.

Layout and Format 

There are some basic layout options one of which I found to be very useful when looking at the Pivot Data. It is the Indentation of the row labels. By setting it to 4 characters (tab space) the data looks much cleaner and appealing to look at.

For this first we are adding up some fields from our source to the pivot, we get:

The above look, changing the indentation by setting up more characters can give the following look which is cleaner to look at.

Also, you can check the box for error values to give a value instead of showing an error.

The same we can do for empty cells.

Format Empty Cells

You can see in our source data we have blank cells due to which the pivot table also has blank cells.

This can be frustrating and prone to errors when preparing a report.

Steps to format empty cells in Pivot Table:

1. Click on any cell inside the Pivot Table
2. Go to PivotTable Tools Tab on the Ribbon -> Analyze Tab -> Options

3. You’ll get a dialog box, PivotTable Options

Under the format label, Check the For empty cells show: and then give your desired value.

We are giving 0 as the default value as quantity is a numeric field to avoid any errors.

Then Click on OK

4. Now you’ll have zeros in the empty cells.

Moving to another tab present in the PivotTable options Totals and Filters

Totals and Filters

This tab consists of some basic filtering and totals options

Check the box with label Show grand totals for rows if your data needs to be presented in that way for effective analysis.

In our example data, it is of no use as such the data is prepared in a vertical way rather than horizontal.

As our data is inserted in columns by default, we have a grand total row field at the bottom in our pivot table.

To hide the Grand Total field simply click on this row field -> Go to PivotTable options (PivotTable tools->Analyze->Options) -> Go to the Tools and Filters tab -> Uncheck the Show grand totals for Columns options

Now we have removed the grand total row from our Pivot table

And under the Filters category in this tab of PivotTable options,

Check the Box with label Show multiple filters per field to enable multiple filtering on a field in your PivotTable.

Display Options

The display options provide functionality to view or hide some of the features of the PivotTable.

For example, you can check this,

Option to enable dragging of the fields to the grid to add the fields directly to the table.

You can Show or Hide the expand/collapse buttons inside the Pivot Table to lock your view for the table you’ve created for selective analysis.

Also, you can show/hide the field captions and filter drop-down menus by checking/un-checking the following options.

At the bottom of the options, you can select whether to sort the data according to the source order or you can select Sort A to Z to sort the pivot table according to its contents.

Printing

The options present in this Printing tab are helpful when you want to print a physical copy of your Pivot Table.

I think it is neat to print without the expand/collapse buttons cause when the pivot table is on paper there is no point to have those extra elements without any functionality.

Also, you can set to print titles of the PivotTable.

By checking the Repeat row labels on each printed page, for every page the pivot table appears, the row labels will be printed for every page.

Data Options in Pivot Table

These Data options offer pretty important features for the end users of the Pivot table.

For example, if you tend to use some external source to build the pivot table in another sheet. You’ll need to carry the external data source with you if it is local file, or every time connect to the web if the file is pulling data from the cloud.

It is important to check the option Save source data with the file to have offline and single file flexibility so that you never lose out on the Pivot data.

Also, I usually check the option Refresh data when opening the file to avoid any inconsistencies while data analysis. You may forget to refresh the pivot data every time which can result in in-accurate results and error prone reports.

Alt-Text for Pivot Table

For a good representation of your data always give an Alt-text and a description to the Pivot Table which you’ve built.

Maybe your colleagues in your organization would not be able to understand the context of the pivot table which you made or it might be that you may have forgotten the cause for building the Pivot table. 

It’s always a nice rule of thumb to include alt-text and description to the Pivot Table.

And as Microsoft says, it can greatly help those with cognitive impairments to understand the context of the table. In case you needed a more concrete thought to include titles and descriptions to your data.