How to change pivot table option settings to adjust the pivot table's appearance and behaviour
When you’re setting up a pivot table, you can use the Ribbon commands to change its appearance, and the source data, and several other settings. There’s another place where you can control the pivot table’s behaviour too – in the Pivot Table Options. See how to open that dialog box, and change the settings
To open the PivotTable Options window:
In the PivotTable Options window, the PivotTable name is shown at the top, and there are six tabs below that.
NOTE: The settings available on each tab may vary slightly, depending on which type of pivot table is selected (Normal, OLAP-based, pre-2007, MDX support)
At the top of the PivotTable Options window, there is a box that shows the current name of the selected pivot table.
Excel automatically creates a numbered name for each new pivot table, and you can leave the default name as is, or change that name to something more meaningful, such as "SalesPivot".
Although the naming rules for other items, such as macros, or worksheets, are somewhat strict, the pivot table names are quite flexible.
Naming Restriction: You cannot give a pivot table the same name as another pivot table on the same worksheet. If you try to do that, Excel will show an error message, and will not change the name.
When there are errors in the pivot table source data, you might see errors in the pivot table Values area. Watch this video to see how to hide those error values, or change them to a different value.
There are written steps on the Hide ErrorValues in Pivot Table page.
To see the steps for showing a zero in empty pivot table cells, watch this short video. The written instructions are below the video.
If no data is entered for some items, there will be blank cells in the pivot table. In the screen shot below, no Banana Bars were sold at stores 3062 or 3659, so those cells are empty.
Instead of leaving these cells blank, you can change a pivot table option, to show a zero, or other characters, in those cells:
To change what appears in empty cells, follow these steps:
The pivot table will change, to show the characters that you entered. In the screen shot below, N/A shows in the empty cells.
The "Show the Values Row" setting shows or hides the Values row in the pivot table, in some situations.
This video shows when the Values Row appears in a pivot table, and when you are able to hide it. There are written instructions below the video. Written instructions are below the video
The Values row appears in a pivot table heading, when there are:
If there are other fields in the Columns area, one of those field names will be in the Values row.
To hide the Values row, you can change the Show the Values Row setting in the PivotTable Options.
NOTE: You cannot hide the Values row if another field name appears in the Values row.
To change the Show the Values Row setting, follow these steps:
Download the sample workbook for the PivotTable Options examples on this page. The zipped file is in xlsx format, and doesn not contain any macros.
NOTE: If you have purchased a copy of my PivotPower Premium (PPP) add-in, you can store some of the options in your Preferred Settings window. Then, after you create a new pivot table, just click the Apply Prefs command to change all of the stored settings, with a single click.
Watch this short video to get a quick tour of the PPP tab in Excel, and see some of the key commands that will help you save time when working with pivot tables.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: March 26, 2021 10:34 AM
Contextures RSS Feed