Excel Pivot Table: How to Ignore Blank Rows and Values

Charts & pivot tables · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, open the pivot field’s drop-down and clear the (blank) item to hide records with an empty value in that field. For a lasting cleanup across several fields, add a helper column to the source data, mark rows that should be included, and filter the PivotTable to keep only those rows.

Filter out the (blank) item in a PivotTable

This is the quickest fix when a row or column field contains empty cells. Excel represents missing entries in a PivotTable field with the label (blank). It is a category in the report, not a worksheet cell that you can erase from the PivotTable without affecting its source.

  1. Click anywhere inside the PivotTable.
  2. Find the drop-down for the field that contains blanks. In Compact Form, the button may be labelled Row Labels or Column Labels; in other layouts it may show the field name.
  3. Clear the checkbox beside (blank), then select OK.

The PivotTable now omits that category and its associated records from the displayed report. The source data remains unchanged, and the records are not deleted. If the blank field is in the Filters area, open its filter and deselect (blank) there. To filter more than one field, apply the filter to each relevant field separately.

To show the records again, reopen the same filter and select (blank). If you want every item back, choose Clear Filter From followed by the field name from the field’s drop-down menu, where that command is available.

What Excel considers blank

A PivotTable’s blank category usually means that source records have no usable value in that particular field. For example, if a sales table has Region, Product, and Amount columns, a blank Region means the Region cell is empty for a record; it does not mean the whole record is empty. The Amount may still contribute to a total under the blank Region category.

Not every cell that looks empty is truly empty. A cell containing a formula that returns an empty string, such as a formula whose result is "", may behave differently from a cell that has never contained a value. A cell with spaces is also not genuinely empty, even if the spaces are hard to see. Imported data may contain nonprinting characters, too. These differences explain why a filter can show a blank-looking item that does not match every visually empty cell in the source.

Check the source column directly if the filter behaves unexpectedly. Select a few cells that look blank and inspect the formula bar; use Find, or temporarily apply a worksheet filter to the source table, to locate inconsistent entries. Be careful when treating a zero as blank: zero is a number and is normally a meaningful value, so it should not be removed unless the data rules say it represents missing information.

Hide blank results versus exclude blank records

These are different tasks. Filtering out (blank) in a row or column field removes the blank category and the associated records from that PivotTable view. It does not remove every record that has a blank in any column. If you filter Region but leave Product unfiltered, records with a missing Product can still appear under their Region.

By contrast, an empty cell in the PivotTable’s Values area is not necessarily a blank category. Sum generally ignores empty source cells when calculating a total. Count counts nonempty entries, while Count Numbers counts numeric entries; Average excludes empty cells but includes zeros. The displayed result therefore depends on the selected summary calculation and on whether the source cells contain true blanks, zeros, text, or formulas returning empty strings.

If the goal is simply to make empty value cells look different, the display setting is separate from a field filter. Right-click a PivotTable cell, choose PivotTable Options, open Layout & Format, and use For empty cells show to specify what appears in empty result cells. That setting changes presentation; it does not remove source records or exclude the (blank) member from a row or column field.

Exclude incomplete rows with a helper column

Use a helper column when a row should be included only if a required source field is populated, or when blanks need to be handled consistently across a report. In the source table, add a column such as Include and give each row a TRUE/FALSE test. Then add Include to the PivotTable’s Filters area or use its field drop-down to display only TRUE.

For a source layout with Region in A2 and down, where Region must contain a value, enter this formula in the helper column’s first data row and fill it down:

=LEN(TRIM(A2))>0

For a normal text value such as West, the result is TRUE; for an empty cell or a value made only of ordinary spaces, the result is FALSE. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces, so consider whether that is appropriate for the data. It does not remove every possible nonbreaking or other special space. If those occur in imported data, clean or normalize the source values using a suitable method before relying on this test.

To require two fields, such as Region in A2 and Product in B2, use a helper test that requires both to contain text after trimming: =AND(LEN(TRIM(A2))>0,LEN(TRIM(B2))>0). This returns TRUE only when both fields are populated. Decide which fields are genuinely mandatory before applying such a rule; excluding a row because an optional field is empty can incorrectly remove valid sales or other records.

Clean the source data when blanks are errors

If blanks indicate a data-entry problem rather than a legitimate “unknown” value, correcting the source is often better than hiding the category in every PivotTable. First identify what a blank means. It may mean not applicable, not yet reported, unknown, or accidentally omitted. Replacing all blanks with a word such as “Unknown” makes the distinction visible, but only do this if that label is an accurate description and consistent with the reporting rules.

In Excel for Microsoft 365 on Windows, use the source table’s filters or Find to locate affected records, then correct them or apply an agreed label. Avoid using Find and Replace across the entire workbook without checking the selected range: it can change values beyond the intended source column. Also avoid filling blank categories with zero unless zero is the true value. A zero amount and a missing amount have different meanings in totals and averages.

Keeping source data in an Excel Table is useful when records are added over time. Select a source cell and choose Insert > Table if the data is not already a table. A PivotTable based on that table can use the table as its source, so new table rows are included when you refresh. A worksheet filter that merely hides rows is not a reliable way to remove those records from a PivotTable’s source; use a helper field or a deliberately prepared source range instead.

Refresh the PivotTable after source changes

A PivotTable does not always reflect edits to its source immediately. After correcting blanks, adding a helper column, or entering new records, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update all PivotTables in the workbook, use Data > Refresh All. The field list may need to be updated if you added a new source column and it does not yet appear as a field.

If the source is a fixed cell range and you added rows or columns outside it, refresh alone will not necessarily expand that source. Click in the PivotTable and choose PivotTable Analyze > Change Data Source, then select the corrected range. Using an Excel Table as the source helps avoid this problem for appended rows, though you still need to refresh the PivotTable to recalculate the report.

Filters can also make a refreshed report look as if records are missing. Check every row, column, and report-filter field, not just the field where you first noticed blanks. If the source was cleaned but (blank) remains, confirm that the PivotTable uses the intended source and refresh it. A different field may still have empty entries, or a formula may still return an empty string.

Choose the right method for the job

  • Hide one blank category: Deselect (blank) in the relevant PivotTable field filter. This is fast and reversible.
  • Exclude rows missing required information: Add a helper field that explicitly tests the required columns, then filter the PivotTable to include qualifying records.
  • Correct inaccurate or inconsistent source values: Fix the source data and refresh the PivotTable. This improves other reports that use the same data as well.
  • Change how empty value cells display: Use the PivotTable Options setting for empty cells. Do not use it as a substitute for filtering source records.

For a one-off presentation, filtering the blank item is usually enough. For recurring reports, a documented helper rule is more dependable because the report makes its inclusion criteria visible. For data quality work, repair the source as well as the PivotTable; otherwise a later refresh or a second report can bring the same blank category back.

Common problems and version notes

(blank) is missing from the filter list: There may be no blank entries in that field in the current source, or the relevant field may not be in the Rows, Columns, or Filters area. Check the source and refresh. If blanks appear in another field, its filter has a separate item list.

The blank item returns after refresh: New source rows may contain missing values, or the original source cells may not have been corrected. Check the data source, including formula results and spaces. A filter hides a category; it does not prevent future rows from creating it.

The total changes when blanks are filtered: That is expected when the filtered field defines which records are included in the visible PivotTable. Compare the filtered result with the unfiltered total before using it as a complete overall figure. Filtering one field can remove records that contain values in other fields.

Excel for Mac or the web: The basic approach is the same: use the field’s filter menu to deselect the blank item, then refresh after source edits. Ribbon labels and placement can vary by platform and update, so the Windows path names above are for Microsoft 365 on Windows. On Mac, use the PivotTable contextual ribbon after selecting the PivotTable; Excel for the web offers PivotTable filtering and refresh capabilities, though some advanced source and PivotTable options may require opening the workbook in desktop Excel.

Frequently Asked Questions

Can I make an Excel PivotTable ignore blanks automatically?

There is no universal switch that treats every blank in every source field as excluded. Filter (blank) in the relevant PivotTable field, or create a helper column that defines which records qualify and filter that helper field. The helper approach is more predictable when several fields matter.

Why does my PivotTable show (blank) when the source cell looks empty?

The cell may contain spaces, a formula returning an empty string, or an invisible character rather than being truly empty. Inspect the source cell and check the formula bar. Imported data can also contain special spaces that are not obvious on the worksheet.

Does filtering out (blank) delete the source data?

No. It only hides the blank category and its associated records in the PivotTable view. The records stay in the source data and can be shown again by changing or clearing the field filter.

How do I stop blank rows from affecting a PivotTable total?

Filter the relevant category if every record with a blank in that field should be excluded. If multiple required fields determine whether a record belongs in the calculation, create a helper field that checks all of them and include only qualifying rows. Confirm that excluding those records is appropriate for the total you are reporting.

Why does a blank still appear after I refreshed?

Refresh updates the PivotTable from its source; it does not repair blank source values. Check for blanks in the specific field, verify the source range includes the corrected data, and inspect formulas or whitespace. Then refresh and reapply the field filter if necessary.

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.