How to Remove Blanks From a Pivot Table in Excel

By Zicheng Ren
All the blanks are replaced with the content you set up.
Image Credit: Courtesy of Microsoft

Pivot tables are used in Microsoft Excel as tools for data analysis, summation and exploration. Using a pivot table allows you to sort though a large amount of data so that you can obtain summations and averages of the data that are analyzed, for example. In order to not leave blanks in the pivot table, you can remove and replace the blank entries with other data by setting up the values of empty cells in the "PivotTable Options...."

Step 1

Open the pivot table report worksheet in Excel.
Image Credit: Courtesy of Microsoft

Open your XLS file and locate the pivot table report worksheet which you have derived from your source data.

Step 2

Choose "PivotTable Options...."
Image Credit: Courtesy of Microsoft

Right-click one of the entries in the pivot table and choose "PivotTable Options..." from among the drop-down list. (see reference 2)

Step 3

Check the "For empty cells show:." check box.
Image Credit: Courtesy of Microsoft

Check the "For empty cells show:" check-box in the Format section of the Layout & Format tab.

Step 4

Type your content in the text field and click "OK."
Image Credit: Courtesy of Microsoft

Type the content that you want to to appear in blank cells in the "For empty cells show:" text field and click "OK" to apply to the changes. For example, type "N/A" without the quotation marks to replace all the blank cells in the table with "N/A" without the quotation marks.

Step 5

Check the result.
Image Credit: Courtesy of Microsoft

Check the entries in the pivot table. All the blanks have been removed and replaced with the new content.

Tip

If you want to remove only some of the empty entries in the pivot table you can create a pivot table based on a selected range of the source data table so that you can manipulate the data in the specified pivot table.

references
Show Comments