Pinterest Pixel

How To Fill Blank Cells in Pivot Table

Pivot Table is an amazing tool that can be used to summarize data in Excel. It lets... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

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 our free training video on how to Find Blank Cells In Microsoft Excel With A Color

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

See also  Group Dates with a Pivot Table

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

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

 

The resultant Pivot Table is shown below.

How To Fill Blank Cells in Pivot Table

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

 

STEP 2: Go to PivotTable Analyze Tab > Options

How To Fill Blank Cells in Pivot Table

 

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!

See also  Sort by Largest or Smallest With Excel Pivot Tables

How To Fill Blank Cells in Pivot Table

 

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

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

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

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

See also  New Pivot Table Features in Excel 2019 and Office 365

 

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

How To Fill Blank Cells in Pivot Table

STEP 2: Select PivotTable Options from the list.

How To Fill Blank Cells in Pivot Table

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

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

How To Fill Blank Cells in Pivot Table

 

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!

Further Learning:

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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  Filter by Text wildcards
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!