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!
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!
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:
- Insert Blank Rows In a Pivot Table
- Find Blank Cells In Excel With A Color
- Go To Blanks
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
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!