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):
- Select the cells (PivotTable column or rows) containing (blank)
- Home tab
- Conditional Formatting
- Highlight Cells Rules
- 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
Enter your email address below to download the sample workbook.
How to hide blank values and blank rows in Top 20 PivotTables?
Thank you.
Hi Cooper, you can filter out blanks using the filter buttons on the PivotTable column headers. Hiding blanks is explained in the tutorial above. Mynda
Option 2 is great indeed
Rirst time today that I experience it’s not working on some of my fields. Is there any know reason when this could happen?
Kr Claudine
Hi Claudine, I can’t think of a reason for it not to work. You’re welcome to share your Excel file on our forum and we can take a look for you.
Hi, I wanna get rid of entire rows that have blanks or zeros…?
You can use the Filters (drop down buttons on the PivotTable) to filter out blanks for specific columns/rows.
Is there an easy vba solution for this?
why use VBA?
Nice!
Glad you liked it, Joan 🙂
For method 3, can’t you just increase the “applies to” range to a much larger area? Then as the pivot table increases, it will still be applying it to the new area. For this example, it could be applied to $F$1:$H$1000 so that as more rows are added to the pivot table it would continue to be updated. After it is applied in this manner, Excel breaks the applied to range to the region before the pivot table, the region after the pivot table, and the region in the table. It does automatically update those regions as the pivot table is refreshed.
Hi Danny,
Yes, I eluded to increasing the Conditional Formatting range in step 3, but it’s not recommended to apply formatting to a bigger range than you need as this can have performance repercussions. For small ranges it’s not an issue, but you don’t want to be applying it to large areas. Also, if you continue to make changes to your PivotTable layout the Conditional Format ‘Applies to’ range becomes very messy.
It’s just not best practice, and option 2 is dead easy, so I recommend that approach.
Mynda