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…



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



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


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on 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...
Different Ways to Filter an Excel Slicer Excel Slicers are the best thing since sliced bread!They are a new feature from Excel 2010 onward and Mac for Excel 2016 and are visual buttons that shows you what items have been filtered or selected in a Pivot Table.There are several ways that you can filter an Exce...
Pivot Table Calculated Field Pivot Table Calculated Fields allow you to do mathematical calculations with your Field List.  You can use any of the Excel mathematical equations, like /*+-%.The only limitation is that you cannot reference any cells.  Pivot Table Calculated Fields can be used to calculate p...
Pivot Table Report Layouts Pivot Tables have three different layouts that you can choose from: Compact, Outline and Tabular Form.You can choose from each layout by clicking in the Pivot Table and going to PivotTable Tools > Design > Report LayoutsThey each have their advantages and disadvanta...