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: 

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

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: