Fix Pivot Table Row Heading Cells Blank Label

After you build an Excel pivot table, you might see 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.

Video: Fix Pivot Table Headings

Watch this short video to see how to fix those “(blank)” labels, and there are written steps below the video.

Video Timeline:

Labels Show (Blank)

In the pivot table shown below, there are three City heading cells, in column B, that shows (blank), instead of a city name. There is also one Region heading cell in column A that has the same problem.

When working with a pivot table, I find that text distracting, and usually remove it, to get a cleaner look in the pivot table.

  • Sometimes I replace the “(blank)” with other text, such as “N/A”
  • In most cases, I want the cell to look blank, so I replace it with a space character

The steps to do that are below this screen shot.

row label cells with (blank) in pivot table

Pivot Option Setting Does NOT Change Labels

First, here’s a solution that you might have tried, to remove the “(blank)” text, and found that it didn’t do what you needed.

  • In the PivotTable Options dialog box, on the Layout & Format tab, there is a setting, “for empty cells show:”
  • Maybe you typed “N/A” in that box, or a space character, hoping it would solve the “(blank)” label problem
  • After making that change in the Options window, you clicked the OK button.

for empty cells show setting in pivot options

Why That Does NOT Work

After the PivotTable Options dialog box closed, you’d be disappointed to see that the “(blank)” headings were still in the row labels.

That solution does not work for “(blank)” cells, because: that “empty cells” setting has these limitations:

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

For example, in the screen shot below, you can see that:

  • missing data in the Values area has been replace by N/A
  • row labels and column labels haven’t changed – they still show “(blank)”.

pivotoptionsemptycells02

Manually Change Blank Labels

Instead of using the PivotTable Options, you can manually change the (blank) labels in the Row or Column Labels areas by typing over them in the pivot table.

However, this technique has a couple of limitations too:

  • You cannot type an existing item name, to replace the (Blank) entry
    • If you type an existing name, the (blank) label will move into the place where that item was in the pivot table layout
  • You cannot clear the cell and leave it empty – it must have a text entry

How to Change Label Text

Here are the steps for manually changing a pivot table row label text, or heading labels in the column areas.

  • Note: 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.

Pivot Table Changes

After you press the Enter key, you’ll see the following changes in the pivot table, shown in the screen shot below:

  • All other (Blank) items in that same pivot field will change to display the same text
    • In this example, all “(blank)” cells in the City column have changed to “N/A”.
  • Blank items in other pivot fields are NOT affected
    • In this example, the “(blank)” cell in the Region column has NOT changed to “N/A”.

pivotrowlabelsblank02

Pivot Table Sample Data

To get Excel data for practice with your pivot table skills, go to the Excel Sample data page on my Contextures website.

  • For the example in this page, I used the Food Sales Data, and cleared a few cells in the source data.
  • There are several other sample files to download, such as Workplace Safety, Insurance Policy Sales, and Stationery Sales.

___________________________

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

__________________________

Fix Pivot Table Row Heading Cells Blank Label

pivotheadingsfixblanklabels01b

____________________

Conditional Formatting Excel Pivot Table Color Scale

It’s November, a month when we expect cooler temperatures here in Canada. However, it was more like summer last weekend, and we enjoyed an afternoon beverage on the patio. Was that normal? What were the November temperatures over the past few years? Let’s use a pivot table with conditional formatting, to find out!

Continue reading “Conditional Formatting Excel Pivot Table Color Scale”

Pivot Table Date Field Filters – 3 Types to Try

After you create a pivot table in Excel, you can filter the data, to focus on specific things. Maybe you want a top product report, or a regional summary, or see the sales to a couple of new customers. You can also use filters on date fields, and there are 3 different types you can use.

Continue reading “Pivot Table Date Field Filters – 3 Types to Try”

Check Sales Progress with Percent Running Total in Excel Pivot Table

Quickly check your sales results over time, or by top products, with the % Running Total feature in an Excel pivot table. This feature is available in Excel 2010, and later versions.

Continue reading “Check Sales Progress with Percent Running Total in Excel Pivot Table”

Duplicate Numbers in Pivot Table Items Problem

When you set up a pivot table, and put fields into the Rows Area or Columns area, Excel groups the items, and calculates the totals for each group. For example, see count of products for each Unit Price. Each item should only be listed once in the pivot table, but sometimes you might see duplicates.

Continue reading “Duplicate Numbers in Pivot Table Items Problem”

Faster Pivot Table Refresh in Excel Macros

With a large pivot table, running a macro that refreshes that pivot table might be painfully slow. The usual macro steps, like turning off screen updating, and disabling events, don’t solve this problem. Try adding an extra bit of code to your macro, to see if that makes the refresh go faster.

Continue reading “Faster Pivot Table Refresh in Excel Macros”

Pivot Table Highlight Weekend Data Conditional Formatting

You can use conditional formatting in an Excel pivot table, to highlight specific data, such as months with high sales numbers. This example uses conditional formatting to highlight the pivot table values that are connected to weekend dates. Continue reading “Pivot Table Highlight Weekend Data Conditional Formatting”

Excel Dashboard Check Pivot Table Filters Before Printing

Do you create weekly or monthly reports based on pivot tables, and share those reports with clients or co-workers? If there are multiple pivot tables in your report, use this Excel dashboard technique to check all the filters, before you hit Send!

Continue reading “Excel Dashboard Check Pivot Table Filters Before Printing”