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 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.

Key Takeaways

• To fill blank cells in a pivot table efficiently, use the “Go To Special” command to select all the blank cells within the pivot table range. This command streamlines the process of finding and filling blank cells without altering the pivot table structure.
• Modify pivot table options to automatically display a value, such as zero, in empty cells. In Excel 2000 and later versions, go to the pivot table options dialog and locate the “For empty cells, show:” setting, and input the desired value, eliminating the need to manually fill each blank.
• To fill in cells below each change in category within a pivot table without dragging and copying, convert the pivot table to static values by copying the pivot table and using “Paste Special – Values”. Following this, use the “Go To Special” command to select and then fill the blank cells accordingly. This process is essential if you plan to perform further operations on the pivot table data, like further summarizing or sorting.

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

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

Example 1:

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

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

 

The resultant Pivot Table is shown below.

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.

 

STEP 2: Go to PivotTable Analyze Tab > Options

 

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″.

All of your blank values are now replaced!

 

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.

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:

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.

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

 

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

STEP 2: Select PivotTable Options from the list.

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

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

FAQs About Excel Pivot Tables

What is the fastest way to fill blank cells in a pivot table?

To quickly fill blank cells in a pivot table, use the ‘Go To Special’ command. First, select the range that contains blanks, then go to ‘Find & Select’ on the Home tab, choose ‘Go To Special,’ and select ‘Blanks.’ Now, type the value you want to fill or a formula and press Ctrl + Enter.

Can I automatically repeat all item labels in my pivot table?

Absolutely! To automatically repeat all item labels in a pivot table, go to the PivotTable Tools Design tab, click on Report Layout, and choose ‘Repeat All Item Labels.’ This ensures that all labels are visible for each item, making your data much easier to read and analyze.

Where can I find more advanced Excel pivot table tips and tricks?

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!