Excel Pivot Table: How to Hide Blank Rows and Values

Charts & pivot tables · Updated November 21, 2026

To hide a blank category in an Excel PivotTable, open the filter arrow for its Row Labels or Column Labels field, clear the check box beside (blank), and select OK. To leave empty cells in the Values area visually empty, use PivotTable Options instead; these are two different kinds of blanks and require different fixes.

Why a PivotTable shows blanks

A PivotTable summarizes records from a source range or Excel table. If a field used for row or column labels has empty entries in the source data, Excel may display an item named (blank) in the PivotTable. For example, if a sales list has a missing Region for one transaction, the Region field can include a blank category alongside East, Central, and West.

There is another kind of blank: an empty cell in the PivotTable’s Values area. A summary cell can be empty when there is no matching record for that combination of row and column items, or when the underlying value is empty. These empty result cells are not necessarily a category called (blank). Hiding the blank category does not automatically change how empty value cells appear.

It is useful to identify which case you have before changing settings. If (blank) appears as an item in a field’s drop-down list, filter that item out. If the blank is inside the body of the report, adjust the display setting or inspect the source data.

Hide the (blank) item in row or column labels

In Excel for Microsoft 365 on Windows, click the filter arrow beside the row or column field that contains the blank item. The arrow may be beside a field heading such as Region, or beside Row Labels when the report uses a compact layout. Clear the check box for (blank), then click OK. If there are several fields with missing values, repeat the step for each field where you want to hide the category.

  1. Click a cell in the PivotTable so that the PivotTable tabs appear on the ribbon.
  2. Open the drop-down arrow for the relevant row or column field.
  3. Clear (blank). If the list is long, use the search box to locate it.
  4. Click OK and check that the report still includes the categories you need.

For example, suppose a PivotTable totals order amounts by Region and shows a separate (blank) row. Clearing that item hides the row from the report. If the same field is in the Columns area, use its column-field filter instead. The filter changes the displayed PivotTable; it does not remove or repair the missing Region in the source records.

To show the category again, reopen the same field filter and select (blank). Selecting the option to clear a field’s filter, where available, also restores all items. Be careful not to choose a command that clears filters for the entire PivotTable if you only mean to restore one field.

Hide empty cells in the Values area

If the issue is an empty result cell rather than a (blank) label, you can specify what PivotTable cells should display when they are empty. Select a cell in the PivotTable, then choose PivotTable Analyze > Options on the Windows ribbon to open PivotTable Options. On the Layout & Format tab, find For empty cells show. Leave its box empty to display no text, or enter a replacement such as a hyphen or 0, then select OK.

This setting controls the appearance of empty cells in the report. It does not filter out a row or column item, fill the original source data, or change the underlying calculation. A displayed zero is also not the same as an empty cell: if the summary actually evaluates to zero, the empty-cell setting will not turn it into a blank. Similarly, the setting is not a general way to hide cells that contain errors or text.

In Excel for Mac, select the PivotTable and look for PivotTable Options on the PivotTable Analyze tab; ribbon labels and placement can vary somewhat by release. Excel for the web has a more limited interface than the desktop application, and some PivotTable options may not be available there. If you cannot find the empty-cell setting in your version, open the workbook in the Excel desktop app.

Choose the right fix for your report

The best choice depends on what the blank means and whether excluding its records is acceptable. Filtering out (blank) is quick, but it can change totals because the records in that category are no longer included in the visible summary. The source rows remain in the workbook, yet readers may interpret the filtered result as covering all records unless you make the filter clear.

  • Hide a label from the report: Filter (blank) out of the relevant row or column field. Use this when the records should not appear as a separate category.
  • Show no text for empty result cells: Set For empty cells show to an empty display. Use this when a report grid has combinations with no value.
  • Make missing information understandable: Correct the source entries or use an explicit label such as Not assigned. This is often better when the records still matter to the analysis.
  • Remove invalid records: Correct or delete source records only when that is appropriate for the data, not merely to make the PivotTable look tidy.

Consider an order report with 100 orders, 8 of which have no assigned salesperson. Hiding the blank salesperson item may make the named salespeople’s subtotal look like the whole company total, even though those eight orders are excluded from the visible breakdown. Keeping a category such as Not assigned makes the gap visible and may help someone resolve it. A clean-looking report is not always a more informative report.

Fix blanks in the source data

If blank categories represent incomplete records, fix them in the source when you can. You might fill in the correct value, or use a consistent label such as Unknown, Not provided, or Not assigned. Choose a label that describes the situation accurately: Unknown can mean the value is not known, while Not applicable means the field does not apply. Do not replace blanks with 0 unless zero is genuinely the correct value.

Source cleanup is particularly useful when several PivotTables use the same data. Correcting the source once can make the category meaningful in every report after each PivotTable is refreshed. If the source is an Excel table, adding or correcting records in the table is generally easier to maintain than relying on a fixed cell range. When the source is an ordinary range and records have been added outside its original bounds, use PivotTable Analyze > Change Data Source to check or update the range before refreshing.

Not every visually empty source cell is identical. A cell that has never contained a value is truly empty; a formula may instead return an empty text string. Depending on the field and the PivotTable’s source and refresh state, these can be represented or treated differently. If a blank category persists after you believe you filled all missing entries, inspect the actual source cells, including formulas, and refresh the PivotTable. Do not assume that formatting a cell to look empty removes its underlying content.

Refreshes, filters, and blanks that return

A PivotTable filter can remain in place after a refresh, but refreshing updates the summary from its source; it does not correct missing values in that source. A newly added record with an empty Region may therefore add or restore a (blank) item. Check the source records and refresh again after correcting them. On Windows, use PivotTable Analyze > Refresh, or choose Data > Refresh All to update all workbook connections and PivotTables that support refresh.

If the blank appears again, check these common causes:

  • A recently added source row has an empty field value.
  • The PivotTable’s source range includes extra rows with missing values.
  • The source range does not include the latest records, so the report and source do not match.
  • A formula in the source returns an empty string, making the cell look empty.
  • The filter was cleared, or another user changed the PivotTable view.

Also distinguish a blank item from a field that has no visible filter arrow. Depending on the layout, the drop-down may be on a field heading, or the report may use field buttons that are hidden. Clicking within the PivotTable and checking the field arrangement in the PivotTable Fields pane can help identify which field is responsible.

Ways to avoid hiding important information

When blanks are meaningful, a clear label in the source is often safer than excluding them. For a small report, manually correcting missing entries may be practical. For recurring reports, standardizing how data is entered can prevent inconsistent values such as an empty cell, a dash, and the word Unknown from becoming separate categories. Excel’s data validation features can help limit entries in a controlled source table, though they do not automatically repair existing missing values.

You can also make a filtered report’s scope obvious. Keep the field filter visible, add a note that blank categories are excluded, or show a separate count of unassigned records. This matters when a PivotTable is used for operational decisions: a filtered total can otherwise be mistaken for a complete total. Filtering is a presentation choice, not a data-cleaning operation.

Frequently Asked Questions

How do I remove (blank) from a PivotTable drop-down?
Open the filter for the field where (blank) appears, clear its check box, and select OK. That hides the item in the PivotTable but does not change the source records.

Why does my PivotTable still show blank after filtering?
Check that you filtered the field that actually contains the blank and that the blank is an item label, not an empty cell in the Values area. New or uncorrected source records can also bring the category back after refresh.

Can I replace blank PivotTable cells with a dash?
Yes, for empty cells in the report, open PivotTable Options and enter a dash in For empty cells show. This changes the display of empty result cells; it does not replace missing source data or change genuine zeros.

Does hiding (blank) change the PivotTable total?
It can. Records assigned to the blank category are excluded from the visible filtered summary, so its displayed totals may be lower. The source records are still present.

How do I stop blanks from returning after refresh?
Correct or consistently label the missing values in the PivotTable’s source, confirm that the source range includes the intended records, and refresh. A filter alone cannot prevent new blank source values from appearing.

Should I use 0 instead of a blank?
Only if zero is the true value. A missing value means information is absent; zero is a numeric result. Replacing one with the other can mislead calculations and readers.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.