Pivot Table is an amazing tool that can be used to summarize data in Excel. It lets you  analyze more than 1 million rows of data with just a few mouse clicks. You can rank values, group data by quarter, month, week and so much more!

But, I am sure that you have come across a Pivot Table which has empty cell values and thought“What the hell is happening here?” “Why Pivot Table showing blank instead of value?”

This is because your data source has no value for certain items, which happens from time to time. The default setting in the Pivot Table is to display the values of those items as blank cells.

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.

In this tutorial, you will learn how to fill blank cells in Pivot Table with any custom text.

Watch it on YouTube and give it a thumbs-up!

How To Fill Blank Cells in Pivot Table | MyExcelOnline

Follow a step-by-step tutorial on How to fill blank cells in Pivot Table and download this Excel workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

 

Example 1:

Suppose you have this data set containing sales data as shown below:

How To Fill Blank Cells in Pivot Table | MyExcelOnline

Using this data, a Pivot Table has been created by dropping region in the row field and sales in the values field.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

 

The resultant Pivot Table is shown below.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

As you can see the pivot value for North Region is blank, let us change this! Follow the steps below to learn how to fill blank cells in Pivot Table with any custom text.

 

STEP 1: Click on any cell in the Pivot Table.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

 

STEP 2: Go to PivotTable Analyze Tab > Options

How To Fill Blank Cells in Pivot Table | MyExcelOnline

 

STEP 3: In the PivotTable Options dialog box, set For empty cells show with your preferred value. Let’s say, you change pivot table empty cells to”0″.

how to fill blank cells in pivot table

All of your blank values are now replaced!

How To Fill Blank Cells in Pivot Table | MyExcelOnline

 

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

This is how you can replace pivot table blank cells with 0!

Let’s look at another example on how to fill blank cells in pivot table with a custom text.

 

Example 2:

In this example, you can different departments and job numbers related to that department. A budget has been assigned to these items.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

A Pivot Table is created with Job Number in Rows field, Department in Columns field and Budget in Values field. The result is shown below:

How To Fill Blank Cells in Pivot Table | MyExcelOnline

You might see there are blank cells in this Pivot Table. This is because there are no record for that particular row/column label.

For example, there is no budget assigned for job number A1227 in Finance, IT and HR.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

You can easily replace this blank cell with the text “NA”.

 

STEP 1: Right click on any cell in the Pivot Table.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

STEP 2: Select PivotTable Options from the list.

How To Fill Blank Cells in Pivot Table | MyExcelOnline

STEP 3: In the PivotTable options dialog box, enter NA in the field – For emply cells show:

How To Fill Blank Cells in Pivot Table | MyExcelOnline

That’s it! All the blank cells will now show NA!

How To Fill Blank Cells in Pivot Table | MyExcelOnline

 

You can easily format your PivotTable by following the above mentioned steps.

There is a lot for you can do in Excel Pivot Table. We have over a hundred tutorials for you to learn and master Excel Pivot Tables! So read on! Click here to learn more!

 

HELPFUL RESOURCE:

728x90

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, Power Query, and Macros & VBA!

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn