Home > Pivot > Layout > Options Excel Pivot Table Options How to change pivot table option settings to adjust the pivot table's appearance and behaviour

Pivot Table Options

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

Open PivotTable Options

To open the PivotTable Options window:

• Right-click on any cell in the pivot table
• In the right-click menu, click PivotTable Options.

PivotTable Option Tabs

In the PivotTable Options window, the PivotTable name is shown at the top, and there are six tabs below that.

• Layout & Formatting
• Totals & Filters
• Display
• Printing
• Data
• Alt Text

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)

PivotTable Name

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".

Pivot Table Name Rules

Although the naming rules for other items, such as macros, or worksheets, are somewhat strict, the pivot table names are quite flexible.

• Minimum number of characters: 1
• Maximum number of characters: 256
• Non-alpha-numeric characters are allowed, e.g.: My Pivot .?;#$%<>"'*
  • I recommend using alphanumeric characters only though, to avoid problems when creating macros that refer to pivot tables by name.

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.

Change Error Values

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.

Show Zero in Empty Cells

To see the steps for showing a zero in empty pivot table cells, watch this short video. The written instructions are below the video.

Show Zero in Empty Cells

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:

1. Right-click a cell in the pivot table, and in the popup menu, click PivotTable Options.
2. In the PivotTable Options dialog box, click the Layout & Format tab
3. In the Format section, add a check mark to For Empty Cells, Show
4. In the text box, type a zero, or other characters, such as N/A
5. Click OK to close the dialog box.

The pivot table will change, to show the characters that you entered. In the screen shot below, N/A shows in the empty cells.

Show the Values Row

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

Values Row

The Values row appears in a pivot table heading, when there are:

• 2 or more fields in the Values area
• at least 1 field in the Rows or Columns area

If there are other fields in the Columns area, one of those field names will be in the Values row.

Hide 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:

1. Right-click a cell in the pivot table, and in the popup menu, click PivotTable Options.
2. In the PivotTable Options dialog box, click the Display tab
3. In the Display section, add or remove the check mark for "Show the Values Row"
   • Remove the check mark to hide the Values Row (when possible)
   • Add a check mark to show the Values Row (when available)
4. Click OK to close the PivotTable Options dialog box.

Get the Workbook

Get the sample workbook for the PivotTable Options examples on this page. The zipped file is in xlsx format, and doesn not contain any macros.

More Tutorials

Hide Error Values in Pivot Table

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table