How to Hide Blanks in Excel Pivot Table: Easy Guide

Sharing is caring!

Have you ever created a pivot table in Excel only to find it littered with distracting empty cells and “(blank)” labels? Those blank cells can make your pivot table look messy and harder to analyze. Luckily, there are several easy ways to hide blanks in an Excel pivot table, which we’ll cover step-by-step in this guide. By the end, you’ll know exactly how to clean up your pivot tables for better readability and a more professional appearance.

What Causes Blank Cells in a Pivot Table?

Before we get into the solutions, it’s helpful to understand why blank cells appear in pivot tables in the first place. The most common causes are:

• Empty cells in the source data – If there are empty cells in the underlying data used to create the pivot table, they will show up as blanks in the table.
• Combinations with no data – A pivot table shows all possible combinations of row and column labels. If a particular combination has no corresponding data, it will display as blank.
• Improperly formatted data – Text, errors, or inconsistently formatted values in the source data can lead to blank cells.

Identifying the root cause will help determine the best method to resolve the blanks. Now let’s look at how to clean them up.

Method 1: Hiding Blanks with the “Remove Blank Line After Each Item” Setting

The easiest way to hide blanks in a pivot table is using Excel’s built-in “Remove Blank Line After Each Item” option:

1. Click anywhere inside the pivot table to activate the PivotTable Fields pane.
2. Right-click on any field in the Rows or Columns area where you want to hide the blanks.
3. Select “Field Settings” from the context menu.
4. In the Field Settings dialog box, go to the Layout & Print tab.
5. Check the box for “Remove Blank Line After Each Item“.
6. Click OK to apply the change and close the dialog.

Excel will immediately remove any blank rows or columns associated with that field. Repeat the steps for other row/column fields as needed. This is a quick fix if you have a few specific fields you need to tidy up.

Pros and Cons of the Remove Blank Lines Setting

Method 2: Using the “Show Items with No Data On Rows” Option

Another simple solution is to use Excel’s “Show Items with No Data On Rows” feature to consolidate all blank cells into a single “(blank)” row:

1. Click the PivotTable Tools Options tab in the Excel ribbon.
2. Look for the Display group and uncheck the box for “Show Items with No Data On Rows“.
3. Click OK if prompted to confirm the change.

The pivot table will gather all the blank cells into one “(blank)” row at the bottom. This keeps the blanks but makes them less obtrusive. You can then filter out the “(blank)” row if desired.

Note that this option only works for rows, not columns. But consolidating blanks into a single row is often enough to greatly improve readability.

Step-by-Step Example

To illustrate, say you have a pivot table summarizing sales data by region and product:

After unchecking “Show Items with No Data On Rows”, the table becomes:

The two blank cells under “North” and “South” have been consolidated into the “(blank)” row at the bottom, cleaning up the main data.

Method 3: Filtering Out Blanks

For more control over which blanks are displayed, you can use the pivot table filters to hide specific blank cells:

1. Click the dropdown arrow next to Row Labels or Column Labels.
2. Uncheck the box for (blank) to hide blank cells for that field.
3. Click OK to apply the filter.

You can filter out blanks for multiple row and column fields. The filtered blanks will be completely hidden, not just collapsed into a single row.

However, keep in mind that filters don’t permanently remove blanks and will need to be reapplied if the pivot table is refreshed or updated with new data.

Filtering Blanks from Value Fields

In some cases, you may have blank cells in the Values area of the pivot table, not just in rows or columns. To filter these out:

1. Click the dropdown arrow next to the desired value field.
2. Choose Value Filters and then select Non-Empty to hide blanks or Empty to show only blanks.

This can be useful for focusing your analysis on data points with values and excluding irrelevant blanks.

Method 4: Cleaning Up the Source Data

While the above options work well for cosmetic changes, the most effective long-term solution is often to clean up the underlying source data:

• Remove empty rows and columns – Delete completely empty rows and columns that are causing blank cells in the pivot table.
• Fill in missing data – If blank cells represent missing information, try to fill in the gaps with the correct data.
• Correct inconsistent formatting – Ensure values are entered consistently, without stray spaces, different capitalizations, etc.
• Use error handling – Replace error values like #N/A or #DIV/0! with 0s or another appropriate placeholder.

Keeping the source data clean and complete will prevent many blank cells from appearing in your pivot tables in the first place. It also makes the data more accurate and reliable overall.

Of course, fixing the source data can be time-consuming, especially with large datasets. And in some cases, blank cells are unavoidable based on the available information.

So, in practice, it’s often best to combine cleaning the source data with the other methods above for hiding blanks. That way, you can optimize both the underlying information and its presentation in the pivot table.

Summary

As we’ve seen, there are several methods for controlling blank cells in Excel pivot tables:

1. Use the “Remove Blank Line After Each Item” setting to hide blanks for individual row/column fields.
2. Turn off “Show Items with No Data On Rows” to collapse blanks into a single “(blank)” row.
3. Use pivot table filters to hide blanks in row, column, or value fields as needed.
4. Clean up the source data to prevent blanks by removing empty rows/columns, filling in missing data, fixing formatting, and handling errors.

By understanding and applying these techniques, you can keep your pivot tables polished, professional, and focused on the data that matters. Don’t let blank cells diminish your data – take control of them today!

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.