Tag: Empty

Changing Blank Row Labels

In your Excel pivot table, you might have a few row labels or column labels that contain the text “(blank)”.

This happens when data is missing from the source data. For example, in the source data, you might have a few sales orders that don’t have a Store number entered.

Labels Show (Blank)

In the pivot table shown below, there is one store number cell, in column A, that shows (blank).

Instead of that text, you want blank cells in the Row Labels area and Column Labels area to contain the text “N/A.”

In the PivotTable Options dialog box, on the Layout & Format tab, you entered N/A as the text to display in empty cells, and then you clicked the OK button.

However, the empty cells still appear as (blank) in the Row and Column Labels areas.

Does NOT Change Labels

In the PivotTable Options dialog box, the setting for empty cells:

  • affects cells in the Values area
  • does NOT affect the Row or Column Labels areas.

In the screen shot above, you can see that missing data in the Values area has been replace by N/A, but the row labels and column labels haven’t changed.

Manually Change Blank Labels

You can manually change the (blank) labels in the Row or Column Labels areas by typing over them in the pivot table.

You can type any text to replace the (Blank) entry, but you CANNOT clear the cell and leave it empty.

How to Change Label Text

Here are the steps for manually changing a pivot table row label text or labels in the column areas. I used “N/A” in this example, but you could use a different text string, or a space character.

To change a blank label cell to “N/A”, follow these editing steps:

  1. First, select one of the Row or Column Labels that contains the text (blank).
    • Even if there are multiple cells with a “(blank)” label, you only need to select one of them.
    • You DO NOT need to press Ctrl and select all of them
  2. Next, on your keyboard, type N/A in the cell, and then press the Enter key.

Note: All other (Blank) items in that same pivot field will change to display the same text, N/A in this example.

___________________________

More Pivot Table Tips

For more information on pivot tables, see these pages on my Contextures site:

Manually Move Pivot Items

Clear Old Items in Pivot Table

Pivot Table Options

_________________