How to Remove Blanks in a Pivot Table in Excel (6 Ways)

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

by Avantix Learning Team | Updated April 5, 2021

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: How to Delete Blank Rows in Excel  (5 Easy Ways)

Do you want to learn more about Excel? Check out our virtual classroom or live classroom Microsoft Excel courses >

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:

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

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

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:

The Format Cells dialog box appears as follows:

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":

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:

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.

More Resources

10 Great Excel Pivot Table Shortcuts

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

How to Hide or Unhide Excel Worksheets (and Unhide All Sheets)

How to Fill Blanks in Excel from the Cell Above (using a Few Excel Tricks)

How To Fill Blanks in Excel with Zeros, Dashes or Other Values in an Excel Range

Related courses

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 courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix^® Learning

You may also like

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 |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca