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 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 Layouts They each have their advantages and disadvanta...
Insert Blank Rows In a Pivot Table Pivot Table reports are shown in a Compact Layout format as a default and if you have two or more Items in the Row Labels (e.g.Month & Customer), then the Pivot Table report can look very clunky... There is a cool little trick that most Excel users do not know about th...
Insert a Pivot Table Pivot Tables allow you to analyze thousands of rows of data with just a few mouse clicks.  It is the most powerful tool within Excel due to its speed and output and I will show you just how easy it is to create one. If you are using a table or data set to analyze your informat...
Sort Field List In a Pivot Table from A to Z Do you ever get in a situation where your Pivot Table Field List has over 20 fields and you spend heaps of time trying to locate one within the Field List? Well you can save time by sorting the Pivot Table Field List in alphabetical order! To do this you need to Right Click...