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:

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