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.

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:

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

Published by Zach