I am sure that you have come across a Pivot Table which has empty cell values and thought “What the hell is happening here?”

This is because your data source has blank cells for certain items, which happens from time to time.

This can be fixed in your Pivot Table and you can enter a value or text in place of that horrible looking and lonely blank cell.

You need to click in your Pivot Table > PivotTable Tools > Options > Options > Layout & Format > Format > For empty cells show: enter a value or text in this box.

Watch the tutorial below to see how this is achieved…

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Our pivot value for North is blank, let us change this!

 

STEP 2: Go to Pivot Table Tools > Options > Options

 

STEP 3: Set For empty cells show with your preferred value

All of your blank values are now replaced!

Format Empty Cells

HELPFUL RESOURCE:

728x90

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Pivot Charts Pivot Charts in Excel are an extension of a Pivot Table and they show its values in a graphical representation.  So when you filter a Pivot Table, the Pivot Chart updates accordingly. To insert a Pivot Chart you need to click in your Pivot Table, go to the PivotTable Tools tab...
Show The Difference From Previous Months With Exce... Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation. You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you ...
Show Pivot Table Values As a Running Total In If you want to track your sales on a monthly basis and see their Year to Date totals, then you can easily do this with the Running Totals In calculation. You can activate this by Right Clicking on your Pivot Table values and choosing Show Values As and Running Total In.  You c...
Icon Sets In A Pivot Table An Icon Set is a Conditional Formatting icon/graphic that you can include in your cells or Pivot Tables. The icon will depend on the cell´s value so you can highlight key variances or trends.  There are a few icon sets that you can include, like: DIRECTIONAL (Change in valu...