In a pivot table, you might have a few row labels or column labels that contain the text “(blank)”. This happens if data is missing in the source data. For example, in the source data, there might be a few sales orders that don’t have a Store number entered.
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, you entered N/A as the text to display in empty cells.
However, the empty cells appear as (blank) in the Row and Column Labels areas.
Change the Labels
In the PivotTable Options dialog box, the setting for empty cells affects cells in the Values area, but not 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 and column labels haven’t changed.
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, even a space character, but you can’t clear the cell and leave it empty:
- Select one of the Row or Column Labels that contains the text (blank).
- Type N/A in the cell, and then press the Enter key.
Note: All other (Blank) items in that field will change to display the same text, N/A in this example.
__________________________________
Pivot Table Tools
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
- copy the formatting from one pivot table, and apply it to another pivot table.
- change all the values from Count to Sum
- remove the “Sum of” from all the headings
and much more!
That was the easiest, most pain-free solution I found anywhere on Google. Thank You!!!
THANKS for your solution, which a have referred to on another blog comment posted under name “pascal1957” on
“25 Aug 2015 1:06 PM” as being very handy to be used – see: http://blogs.technet.com/b/danstolts/archive/2014/10/26/how-to-hide-replace-empty-format-blank-values-with-an-empty-field-in-an-excel-pivot-table-without-using-filters-step-by-step.aspx?pi30369=2#comments
@Pascal, thank you! I’m glad you were able to pass the solution along to someone else.
So easy! Thank you!
Thanks for the article. I was aware of the capability already and use it a lot. I was wondering if you might know how to find where in Excel these renames are tracked?
For example, I’ve renamed (blanks) to “Migration Pending” in one of my spreadsheets. I can’t find a way where somebody else could easily determine that I’ve done this. Any ideas?
I was wondering about that too. In addition, what if I would like to return it to “(blank)” without a rename; you know, back to the original value?
To Jeremy and Jim:
Excel knows the item name that’s in the data, or knows if that cell is blank, but doesn’t show that source data info in a pivot table tooltip.
You can manually change the name back to the original, or use a macro. I’ve posted more info and a caption fix macro on my Pivot Table Blog:
https://www.pivot-table.com/2020/03/04/fix-pivot-item-captions-in-excel/
–Debra