Excel: How to Remove Blanks in Pivot Table

Often you may want to remove blank values from a pivot table in Excel.

Fortunately this is easy to do using the Options button within the PivotTable Analyze tab.

The following example shows exactly how to do so.

Example: Remove Blanks in Excel Pivot Table

Suppose we have the following dataset in Excel that shows the number of sales made by two individuals during various months:

Now suppose we create the following pivot table to summarize the sales made by each individual each month:

Notice there are several blank values in the pivot table where Andy or Bert had no sales during specific months.

To replace these blanks with zeros, click on any cell in the pivot table.

Then click the PivotTable Analyze tab along the top ribbon.

Then click the Options button:

In the new window that appears, make sure the box is checked next to For empty cells show: and then type in a zero: 

Excel remove blanks from pivot table

Once you click OK, the blank cells in the pivot table will automatically be replaced with zeros:

Note that you can replace the blank values with any value that you’d like.

For example, you could instead type a string such as “None”:

The blank values in the pivot table would then be replaced by this value:

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Filter Top 10 Values in Pivot Table
Excel: How to Sort Pivot Table by Grand Total
Excel: How to Calculate the Difference Between Two Pivot Tables

Leave a Reply

Your email address will not be published.