Show the Values Row in Excel Pivot Table

In the Excel PivotTable Options dialog box, there are lots of settings that you can turn on or off. Most of the settings work the way you’d expect, but the “Show the Values Row” option might not seem to do anything. You turn it on and off, and nothing changes. Here’s an explanation of how, and when, the “Show the Values Row” pivot table option works, and you can see the steps in the video below.

Video – Show Values Row

In an Excel Pivot Table, the “Show the Values Row” option 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. Written instructions are below the video

See the Values Row

First, your pivot table has to have a Values Row, before you can show or hide it. The Values row only 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

Field Names in the Values Row

If a pivot table does have a Values row, sometimes “Values” is the only thing in the Values row. That’s the case in the first pivot table in the screen shot below.

However, if there is another field in the pivot table Columns area, its name will also appear in the Values row. In the second pivot table, the Region field is in the Columns area, so its name is in the Values row.

Hide the Values Row

If a pivot table does have a Values row, you might be able to hide it.

The Values row can be hidden if it only contains “Values”
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:

Right-click a cell in the pivot table, and in the popup menu, click PivotTable Options.
In the PivotTable Options dialog box, click the Display tab
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)
Click OK to close the PivotTable Options dialog box.

Get the Sample File

To see how the Show the Values Row settings works, go to the Pivot Table Options page on my Contextures website, and download the sample workbook.

The zipped file is in xlsx format, and does not contain any macros.

______________