Contextures

Excel Pivot Table Options

How to change pivot table option settings to adjust the pivot table's appearance and behaviour

Pivot Table Options

In addition to the pivot table settings on the Ribbon, you can make changes in the Pivot Table Options dialog box.

To access this, right-click on any cell in the pivot table, and click PivotTable Options.

PivotTableOptions in popup menu

Video: Show Zero in Empty Cells

To see the steps for showing a zero in empty pivot table cells, please watch this short video tutorial. 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.

empty cells in pivot table

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.

empty cells in pivot table

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

empty cells in pivot table

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.

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

Values row in pivot table

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

Column field name in 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.

Column field name in 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.

empty cells in pivot table

Download the Workbook

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.

PivotPower Premium Add-in

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

More Tutorials

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table

Don't Miss Our Excel Tips

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.

Get weekly Excel tips from Debra

 

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

 

Free Pivot Table Tools

 

 

Peltier Tech Charts for Excel 3.0

 

 

 

Pivot Power Premium

Last updated: January 20, 2019 3:19 PM
Contextures RSS Feed