Hide Blanks in Excel PivotTables

There are a couple of ways you can hide blanks in Excel PivotTables. To be clear, the ‘blanks’ I’m referring to are those shown below where the text, (blank), is inserted as a placeholder for empty cells in your source data:

It’s important to point out that these (blank) placeholders only occur in row or column areas, and never in the values area of a PivotTable.

Here’s a diagram so you know what I mean by the different areas of a PivotTable:

The Cause of (blank) in PivotTables

The (blank) placeholder is caused by empty cells in your source data, like those shown in the Status column of my source data below:

How to Hide (blank) in PivotTables

Option 1: Ideally your source data shouldn’t have any blank or empty cells. So, the best solution to hide blanks in Excel PivotTables is to fill the empty cells. However, this isn’t always practical, hence options 2 and 3 below.

Option 2: Select any single cell in the PivotTable that contains (blank) and enter a space in the cell. Like magic it will replace all (blank) values for that field with a space, which is more aesthetically pleasing:

Pros: This setting is automatically applied to any new data added to your source that also contains empty cells. And if you add data to cells in your source data that were previously blank, the PivotTable will correctly update. i.e. the cells containing the space will be replaced with the new data upon refreshing the PivotTable.

Cons: If you have multiple fields containing blanks e.g. Department and Status, then you need to repeat this for each field. No biggy.

Option 3: Conditional Formatting can be used to hide the (blank) text.

Steps (as shown in the image below):

1. Select the cells (PivotTable column or rows) containing (blank)
2. Home tab
3. Conditional Formatting
4. Highlight Cells Rules
5. Text that Contains…

In the dialog box (shown below) enter ‘(blank)’ and select ‘Custom Format…’ from the drop-down list:

In the Format Cells dialog box choose ‘Custom’ in the ‘Category’ list on the ‘Number’ tab, and in the ‘Type’ field enter 3 semi-colons:

The 3 semi-colons simply tell Excel to hide all text or numbers in the cell, and the Conditional Formatting rule restricts this format to only those cells that contain the text ‘(blank)’.

Tip: Learn more about Excel Custom Number formats here.

Pros: It hides the (blank) placeholder and once the empty cells in the source data contain text or values they no longer meet the condition, so the actual text or values are displayed.

Cons: Requires manual updating if the PivotTable grows. Alternatively, you can apply the Conditional Formatting to the whole column/row. However, if your PivotTable changes shape, you’ll need to update the range the Conditional Format is applied to. This can be done via the Home tab > Conditional Formatting > Manage Rules dialog box:

This is because Conditional Formatting applied to row or column labels doesn’t automatically adapt to changes in the PivotTable shape or size, unlike when applied to the values area.

Personally, I like option 2 because it’s easier. You can call me lazy, but I like to call it efficient 😉

Download the Workbook