Hide or Change the Display of Blank Cells in Excel Pivot Tables

by Avantix Learning Team | Updated March 1, 2020

Applies to: Microsoft® Excel® 2013, 2016, 2019 and 365 (Windows)

When you create a pivot table in Excel, blank cells may appear if you have blanks in your data source. Sometimes, the word “blank” appears in brackets or parentheses in cells. To remove blanks in pivot tables, you can set pivot table options to display data in empty cells, filter to remove blanks, apply conditional formatting, find and replace blanks, change pivot table design settings or clean up the source data.

Note: Buttons and Ribbon tabs may display in a different way (with or without text) depending on your version of Excel, the size of your screen and your Control Panel settings. For Excel 365 users, Ribbon tabs may appear with different names. For example, the PivotTable Tools Analyze tab may appear as PivotTable Analyze and the Options button may appear in a PivotTable drop-down menu.

Recommended article: 10 Great Excel Pivot Table Shortcuts

Displaying data for empty cells using Options

You can use the PivotTable Options dialog box to control the display of blanks. Use this method if the blanks are in the values area of the pivot table.

To set pivot table options for empty cells:

  1. Click in the pivot table.
  2. Click the PivotTable Tools Analyze tab in the Ribbon.
  3. Click Options in the PivotTable group. You can also right-click in the pivot table and select PivotTable Options from the drop-down menu. The PivotTable Options dialog box appears.
  4. Click the Layout & Format tab.
  5. Check For empty cells show and enter data in the entry box (such as 0). This only affects cells in the values area of the pivot table, not the row or column areas.
  6. Click OK.

Below is the PivotTable Options dialog box:

Pivot Table dialog box in Microsoft Excel with option to display data for empty cells.

In the following example, note the blanks in the values area of the pivot table:

Blanks in an Excel pivot table in the values area.

After changing pivot table options, the blanks have been replaced with zeros:

Blanks replaced with zeroes in an Excel pivot table:

Filtering to remove blanks

Depending on the location of cells with blanks, you can filter to remove the blanks. If blanks appear in row or column heading fields, filtering can work well.

To filter to remove blanks in a row or column field:

  1. Click the arrow to the right of a row or column heading in the pivot table. A drop-down menu appears.
  2. Click to uncheck the (blank) check box. You may need to scroll to the bottom of the list.
  3. Click OK.

Applying conditional formatting to remove blanks

To apply conditional formatting to remove blanks in a pivot table:

  1. Click in the pivot table.
  2. Press Ctrl + A to select the cells.
  3. Click the Home tab in the Ribbon and click Conditional Formatting. A drop-down menu appears.
  4. Select New Rule. A dialog box appears.
  5. In the dialog box, click Format only cells that contain.
  6. Under Format only cells with, select Cell Value equal to and enter “(blank)”.
  7. Click Format. A dialog box appears.
  8. Click the Number tab.
  9. Click the Custom category on the left.
  10. In the General area, enter three semi-colons (;;;). This will format positive, negative and zero or blank values as blank.
  11. Continue clicking OK until you return to the worksheet.

Below is the Conditional Formatting dialog box:

Conditional formatting dialog box in Microsoft Excel to find blanks in pivot tables.

The Format Cells dialog box appears as follows:

Format cells dialog box in Microsoft Excel with custom format to hide blank cells in a pivot table.

Finding and replacing blanks

You can use the Replace command to find and replace blanks.

To find and replace blanks:

  1. Click in the worksheet with the pivot table.
  2. Click Ctrl + H to display the Replace dialog box.
  3. In the Find What box, enter “(blank)”.
  4. In the Replace with box, type a space if you want to blanks to be removed or type a word such as “Other” to replace the blanks with text.
  5. Click Replace Al. If you want to replace specific cells, click Find Next and then Replace for each instance you want to replace.
  6. Click OK.

In the following example, we’ve replaced blanks with the word “Other”:

Microsoft Excel Find and Replace dialog box to replace blanks in pivot table.

Changing pivot table design settings

Some users may have set options to display a blank row after each group of values.

To remove blanks using pivot table design settings:

  1. Click in the pivot table.
  2. Click the PivotTable Tools Design tab in the Ribbon.
  3. In the Layout Group, select Blank Rows. A drop-down menu appears.
  4. Select Remove Blank line after each item.

Cleaning up blanks in source data

Another option is to clean up blanks in the source data and enter a value instead. You can use the Go to Special dialog box to find blanks and then enter data in the blank cells.

Below is the Go to Special dialog box:

Microsoft Excel Go to Special dialog box.

To use Go to Special to find blanks in source data and enter values in the blank cells:

  1. Select the range of cells in the source data with blank cells or missing values (this range is often in one column).
  2. Press Ctrl + G to display the Go To dialog box and then click Special to display the Go To Special dialog box. Alternatively, you can click the Home tab in the Ribbon and then select Go to Special from the Find & Select drop-down menu.
  3. Select Blanks in the Go To Special dialog box and click OK. Excel will select all of the blank cells within the range.
  4. Type the value you want to appear in the blanks such as “0” or “Other”. Do not click in a cell first. This data will be entered in the active cell.
  5. Press Ctrl + Enter. The data should be entered in all of the blank cells.
  6. Right-click in the pivot table and select Refresh.

The method you use to remove blanks in a pivot table depends on the area in which the blanks appear.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.

To request this page in an alternate format, contact us.

Copyright 2020 Avantix® Learning Inc.

You may also like

How to Group by Month and Year in a Pivot Table in Excel

How to Group by Month and Year in a Pivot Table in Excel

You can group by month, year or other date period in a pivot table in Excel. One option is to group by date periods in a pivot table using the Grouping feature. Alternatively, you can also create calculations in source data to extract the month name and the year from a date field and use the fields in your pivot table.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
How to Remove Blanks in a Pivot Table in Excel (6 Ways)
Article Name
How to Remove Blanks in a Pivot Table in Excel (6 Ways)
Description
When you create a pivot table in Excel, blank cells may appear if you have blanks in your data source. Sometimes, the word “blank” appears in brackets or parentheses in cells. To remove blanks in pivot tables, you can set pivot table options to display data in empty cells, filter to remove blanks, apply conditional formatting, find and replace blanks, change pivot table design settings or clean up the source data.
Author
Publisher Name
Avantix Learning Inc.

Pin It on Pinterest

Share This