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

[

](/wp-content/uploads/2014/10/image.png)

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.

1. Select the cells that you want to conditionally format (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)

1. On the Home Tab of the Ribbon Select Conditional Formatting

1. From the Dropdown, select New Rule

[

](/wp-content/uploads/2014/10/SNAGHTML44b65bd.png)

1. Select Format only cells that contain

1. In the drop down box box select Cell Value

1. in the second drop down box select equal to

1. In the third box type in “(blank)” without the quotes

1. Click the Format… Button

[

](/wp-content/uploads/2014/10/image1.png)

1. On the Number tab; select Custom

1. Then to the right, under the word Type: just type in 3 semi-colons “;;;”  without the quotes

1. Click OK

[

](/wp-content/uploads/2014/10/image2.png)

1. 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

1. Change the range to whatever you want the range to be and click OK

1. If you want to delete the rule; click Delete Rule

[

](/wp-content/uploads/2014/10/image3.png)

1.

The final result will look like the following: [

](/wp-content/uploads/2014/10/image4.png)