Excel Pivot Table: How to Exclude Blank Rows and Items

Charts & pivot tables · Updated November 18, 2026

To exclude blanks from an Excel PivotTable in Microsoft 365 for Windows, open the dropdown for the row, column, or report-filter field that shows “(blank),” clear the checkbox beside “(blank),” and select OK or Apply if shown. This hides that blank category from the PivotTable; it does not delete or repair blank cells in the source data.

What “blank” means in a PivotTable

In a PivotTable, “(blank)” usually represents one or more empty values in a source-data field used in the report. For example, a sales list might have a Region column with North, South, and some empty cells. If Region is placed in the Rows area, Excel can add a “(blank)” row for records with no Region value. The same idea applies when the field is placed in Columns or Filters.

That is different from a completely empty worksheet row. Entirely empty rows generally do not create a meaningful category, but a row with a blank in one field and data in another can contribute to “(blank).” It is also different from zero: a numeric value of 0 is a real value, not an empty cell, so filtering out “(blank)” will not remove zeroes.

Cells that look empty may not be empty in the same way. A formula that returns an empty string, a cell containing spaces, or a cell with an invisible character can appear blank on the sheet but still behave differently in a PivotTable. If a supposedly blank item will not filter as expected, inspect the source values rather than assuming the display alone tells the whole story.

Exclude “(blank)” from a row or column field

Use this method when the unwanted blank appears as a row or column label. It changes what the PivotTable displays, while leaving the original records intact.

  1. Click anywhere inside the PivotTable.
  2. Open the dropdown beside the row or column labels containing “(blank).” Depending on the layout, the control may appear beside a field name or beside Row Labels or Column Labels.
  3. In the item list, clear the checkbox for “(blank).” If the list is set to Select All, you can clear Select All first and then select only the items you want to keep.
  4. Select OK or Apply if the dialog shows that button. Confirm that the “(blank)” item no longer appears in the report.

For example, suppose a PivotTable lists orders by salesperson and includes a “(blank)” salesperson row. Clearing that item hides orders with no salesperson value from this view. The other salesperson totals remain visible, but the unassigned orders still exist in the source data and may still be included in other calculations or reports.

If the dropdown arrows are missing, click inside the PivotTable and check whether field headers are displayed. In Excel for Windows, the PivotTable Analyze tab has a Field Headers control in the Show group. Turning field headers on can make the filter controls visible again. Ribbon placement can vary slightly with window size and Excel updates.

Filter a report-filter field

A field placed in the Filters area appears above the main body of the PivotTable as a report filter. To exclude blanks there, open that filter’s dropdown and clear “(blank)” in the item list. This limits the report to the selected nonblank values, just as a row or column filter does.

Report filters are useful when readers need to switch among categories without seeing every category in the rows or columns. For instance, placing Department in Filters lets someone view one department at a time. If blank departments are not meaningful, exclude “(blank)” from the report filter list. Remember that choosing one department is an additional restriction: it is not the same as showing all nonblank departments.

To restore the item later, reopen the same dropdown and select “(blank)” again. To remove a filter entirely, use the dropdown’s Clear Filter option when available, or select all the items you want included. Clearing a filter can reveal blanks again if the source still contains them.

Exclude records because another field is blank

A common source of confusion is filtering one field and expecting Excel to remove every record that has a blank anywhere. A PivotTable filter applies to the field being filtered. If you remove “(blank)” from Region, you exclude records with a blank Region; you do not automatically exclude records with a blank Product, Date, or Salesperson.

Decide which missing value should disqualify a record. If the rule is “show only orders with a customer,” filter the Customer field to exclude “(blank).” If the rule is “show only rows where both Customer and Region are filled in,” filtering Customer alone is insufficient. You must also filter Region, or prepare the source data so that records meeting the full rule can be identified.

For a repeatable report, one practical option is to add a status column to the source table, such as Record status, and mark rows as Complete or Needs review according to the required fields. Add that field to the PivotTable and filter it to Complete. This makes the rule visible to other users and avoids relying on several separate filters. Make sure the status is updated whenever the underlying data changes, then refresh the PivotTable.

Also consider whether excluding incomplete records is appropriate for the question being answered. A blank category can represent missing data worth investigating. Hiding it may make a chart cleaner, but it can also make totals appear lower or conceal a data-quality issue. For operational checks, keeping a separate count of incomplete records is often more informative than simply hiding them.

What happens to totals and counts?

Filtering out “(blank)” removes that item from the displayed PivotTable calculation. A grand total shown in the filtered report therefore reflects the visible, included records rather than necessarily representing every source record. If the blank category contains sales amounts, excluding it can reduce a sum. If it contains orders without a category, it can also change counts.

Blank cells in a value field behave differently from blank labels. For example, a PivotTable that sums Amount may ignore empty Amount cells in the sum, while a count calculation can depend on what is being counted and which field is placed in Values. Filtering a row label called Region does not fill in missing Amount values, and filtering a blank Amount item is not the same operation as excluding blank Regions.

When a result matters, compare the filtered report with the source data or create a second view that includes the blank category. This helps confirm whether a changed total comes from the filter or from another issue, such as duplicated source rows, a stale PivotTable, or an unexpected value stored as text.

Refresh the PivotTable after source changes

Changing a filter only changes the current report. If you fill in missing source values, remove records, or add rows, refresh the PivotTable so it reflects the updated data. In Windows, click inside the PivotTable and choose PivotTable Analyze > Refresh. To refresh all PivotTables and connections in the workbook, use Data > Refresh All.

If the source is an Excel Table, newly added rows are generally included when the PivotTable is refreshed. If the source is a fixed cell range, rows added beyond that range may not be included; check the PivotTable’s data source and expand it if needed. A refresh cannot correct values that remain blank in the source, and a filter that excludes “(blank)” may continue to hide the item even after new blank records are added.

Excel can also retain old field items in the PivotTable cache after they have disappeared from the current source. If a stale “(blank)” or other old item remains in the filter list after a refresh, right-click inside the PivotTable, choose PivotTable Options, open the Data tab, set Number of items to retain per field to None, and refresh again. This option affects retained items in the cache; it does not clean the source data.

Why “(blank)” may still appear

If you have excluded blanks but still see something that looks like an empty category, check these common causes:

  • The filter was applied to a different field. Each field has its own item list. Check the field that actually displays the blank category.
  • A value contains spaces. A cell containing one or more spaces is not necessarily an empty cell, even though it looks blank. Clean or correct the source value, then refresh.
  • The source contains a formula returning an empty string. Such a cell may appear empty but can be handled differently from a truly empty cell. Inspect the source formula and test the PivotTable after refreshing.
  • The PivotTable is showing old cached items. Refresh the report and, if needed, adjust the retained-items setting described above.
  • The source range does not include the corrected data. Check the PivotTable data source, especially if the list grew beyond a fixed range.

Do not replace missing values with zero unless zero is factually correct. A zero can change averages, sums, counts, and charts; it means something different from “unknown” or “not supplied.” If the blank represents a real missing value, use an explicit label such as Not assigned only when that label accurately describes the data and is suitable for the analysis.

Filtering versus cleaning the source

Filtering is the quickest choice when you want a cleaner view without changing the underlying records. It is reversible and works well for a one-off summary. Source cleanup is better when the blank is an error, when reports must consistently follow a data rule, or when other analyses also need the corrected values.

Before changing source cells, identify what a blank means. It might mean not applicable, not yet entered, unknown, or accidentally omitted. Those cases should not automatically be treated as equivalent. A useful workflow is to preserve the original data, agree on the meaning of missing values, correct only values you can verify, and refresh the PivotTable afterward.

Excel for Mac has PivotTable filter controls that work on the same basic principle: open the field’s item dropdown and exclude the blank item. Ribbon arrangement and wording can differ by release. Excel for the web also supports common PivotTable filtering, though some PivotTable management and cache options available in desktop Excel may not be exposed there. If a control is missing in the web version, open the workbook in desktop Excel for the relevant settings.

Frequently Asked Questions

How do I remove “(blank)” from an Excel PivotTable?
Open the dropdown for the field that displays “(blank),” clear its checkbox, and select OK or Apply if offered. The item is hidden from that PivotTable view, not deleted from the source data.

Can I exclude blank cells from every field at once?
Not with one universal “remove all blanks” filter. Filters apply to individual fields. Exclude blanks in each relevant field, or add a source-data status field that identifies records meeting your complete-data rule.

Why does my PivotTable show “(blank)” when the source cell looks filled?
The displayed category may come from another source row with a missing value, or the apparent value may contain spaces or invisible characters. Inspect the source field, correct values as appropriate, and refresh the PivotTable.

Does filtering out “(blank)” delete my original records?
No. It changes what the PivotTable displays. The source rows remain in the worksheet, and you can include the blank item again by selecting it in the field filter.

Why is “(blank)” still in the dropdown after I fix the source?
Refresh the PivotTable first. If an old item remains in the list, set Number of items to retain per field to None in PivotTable Options > Data, then refresh again. Also confirm the PivotTable source includes the corrected data.

Will filtering blanks remove zero values?
No. Zero is a numeric value, not a blank. The “(blank)” filter excludes missing entries in the selected field; use a separate filter if you also need to exclude zeroes.

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.