How to Hide, Replace, Empty, Format (blank) values with an empty field in an Excel Pivot Table without using filters –Step-By-Step
I have been struggling with this for a while. Excel puts the word (blank) on a pivot table field if it does not have any data. I want the field displayed without data. I see in Excel 2013 there is a checkbox under Pivot Table options but I could not get it to work 
I did however finally figure it out. I could solve it using conditional formatting. I many workarounds online in the forums but unfortunately, after a data refresh, the pivot would revert. Using conditional formatting we do not have this problem.
Select the cells that you want to conditionally format [have a (blank) value] (you could select the entire column or even the table too if you want to do multiple columns [even if they do not have (blank) values)
On the Home Tab of the Ribbon Select Conditional Formatting
From the Dropdown, select New Rule
Select Format only cells that contain
In the drop down box box select Cell Value
in the second drop down box select equal to
In the third box type in “ (blank) ” without the quotes
Click the Format… Button
On the Number tab; select Custom
Then to the right, under the word Type: just type in 3 semi-colons “ ;;; ” without the quotes
Click OK
If you want to change anything later (like the range to select the entire column or entire table) you can click Conditional Formatting then Manage Rules
Change the range to whatever you want the range to be and click OK
If you want to delete the rule; click Delete Rule
The final result will look like the following:
Comments
- Anonymous
October 31, 2014
You can also select one of the cells that contains "(Blank)" and type a space character to replace the text.
The option "For empty cells" only affects cells in the Values area, not the row or column labels. - Anonymous
December 16, 2014
Thanks for the help! :) - Anonymous
February 04, 2015
this is the only solution that actually works ..read atleast 5 other links and all of them fail on refresh..thanks a lot!! - Anonymous
March 19, 2015
Nice!! - Anonymous
March 23, 2015
It works!!!! Love the 3 semi-colons “;;;” - Anonymous
April 06, 2015
The comment has been removed - Anonymous
April 10, 2015
I do something that is much simpler, that I think provides the same result; it definitely looks the same. Click on the "Row Label" filter and uncheck Blanks. Then they don't show up, aren't counted in the totals and remain hidden upon refresh. Is there a reason to do it the way outlined here? If this method will make using my data easier to use I'm all for it but I can't see a difference in the result. - Anonymous
May 02, 2015
Awesome! - Anonymous
May 02, 2015
Awesome! - Anonymous
May 14, 2015
perfect - thanks so much - Anonymous
May 26, 2015
The comment has been removed - Anonymous
June 06, 2015
Your example was easy to follow and works perfectly. Thanks so much for sharing - Anonymous
June 06, 2015
Thansk! - Anonymous
June 09, 2015
The comment has been removed - Anonymous
July 08, 2015
TY...TY...TY...!!!! - Anonymous
July 29, 2015
is it same with the slicer?
, it also have {Blanks}....haven't tried it yet - Anonymous
July 29, 2015
Great, finally a solution that works. Thanks. - Anonymous
August 05, 2015
The comment has been removed - Anonymous
August 13, 2015
Searched all internet but and finally an easy solution that really works. Thanks!! - Anonymous
August 20, 2015
The comment has been removed - Anonymous
August 20, 2015
http://www.penguintravel.net/category/golf-travel-resorts Many of the finest golf instructors in the us are inside Arizona! Among the better resorts come in Arizona! Appear, stay, and study on the finest! - Anonymous
August 20, 2015
http://www.directoryautomotive.net/category/auto-repairautomobile In case you might have an automobile accident or your car or truck gets ripped off, it can be quite a big damage. Until and until you are covered by insurance or your car or truck is covered by insurance. - Anonymous
August 20, 2015
http://www.automotivedeals.net/category/auto-repair An unforeseen auto fix bill may be stressful news on this unstable economic system. If that is true available for you, here are usually some techniques you could possibly save money on your own next automobile repair costs. - Anonymous
August 20, 2015
http://www.caraudiomanual.net/category/auto-motor Do you understand how generator controls perform? These can control the particular movement regarding motor. It is designed for total generator control. There are numerous types. Read this informative article to comprehend motor handles. - Anonymous
August 25, 2015
The comment has been removed - Anonymous
August 25, 2015
great suggestions found here !! thanks to all.
another interesting method I found at http://blog.contextures.com/archives/2008/12/11/change-blank-labels-in-a-pivot-table/
quite handy to quickly replace empty - ie. "(blank)" - occurrences in your pivot, without affecting the original table - Anonymous
August 25, 2015
This helped greatly. Thank you! - Anonymous
September 02, 2015
This is great. Instruction is clear and it works. Thanks a lot - Anonymous
September 22, 2015
This works!! Thank you :) - Anonymous
October 14, 2015
Can you replace it with anything else (i.e. 'N/A') using this method? - Anonymous
October 15, 2015
GET YOUR PROBLEM SOLVE TODAY WITH MY PROFESSION IN ANY SPIRITUAL SPELL OR ANY KIND OF PHYSICAL BATTLE THAT NEED, MY NAME IS DR SYLVESTER AND THIS IS MY EMAIL FOR CONTACT (stbenson391@gmail.com) OR YOU CAN FOLLOW HIM UP ON FACEBOOK BY MY NAME (SYLVESTER E BENSON) ON FACEBOOK OR CALL ME ON MY MOBILE NUMBER +2348136090988, AM ALWAYS AVAILABLE TO RENDER YOU HELP WITH EXPERIENCE OF 32 YEARS IN SPELL CASTING AND HERBAL MEDICURE TO CURE ANY KIND OF DISEASE THAT YOU MAY HAVE, CONTACT ME ON ANY KIND OF ISSUES. - Anonymous
November 06, 2015
Thank you Debra Dalgleish. Your comment helped. - Anonymous
November 12, 2015
You can also just add the measure value as a filter and filter out blanks... - Anonymous
November 12, 2015
You can also just add the measure value as a filter and filter out blanks... - Anonymous
November 26, 2015
Great - Thanks for this :) - Anonymous
December 01, 2015
Thanks for this tip. It is the only won that worked to clear blanks in Rows/Columns fields for a tabular style table. All built in options in Excel only work for the Values Fields. - Anonymous
January 18, 2016
Very very useful. Thanks you - Anonymous
February 05, 2016
Appreciate bro - Anonymous
February 05, 2016
Appreciate bro - Anonymous
February 16, 2016
If you do not want to display blank row or column, you can just right click on the header (for example blank), then filter and click "hide selected item). Even if you refresh, it will not show up