如何去掉 Excel 資料透視表中的空白項

Tested in: The COUNT / COUNTA / SUM checks on a mixed range were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click a pivot filter in Excel this session.

This is about a (blank) row or column in a pivot that already exists. If the totals look like a row count, see count instead of sum. If dates will not group, empty date cells are a common reason, covered with the other grouping failures in cannot group dates.

Find the empty source cells

Look at the column that is in Rows or Columns, not only at Revenue. A missing Region, Product, or Date is what produces the (blank) label. A missing amount usually does not.

On a short range used for other tests in this workbook — 4800, 3000, and the text n/a — Google Sheets on 12 November 2026 returned COUNT 2, COUNTA 3, and SUM 7800. COUNTA counts the text; it does not count a truly empty cell. To count blanks in a Region column you can use:

=COUNTBLANK(B2:B20)

or

=COUNTIF(B2:B20,"")

A result above 0 is the number of empties the pivot can turn into (blank). n/a is not a blank — it will show up as its own item, not as (blank). Replace placeholders you did not mean to keep, or leave a real category name.

Also check that the pivot’s source does not run past the last data row. A source of A1:F500 when the list ends on row 9 includes hundreds of empty Regions. Change the source to the used range, or convert the list to a table and point the pivot at the table, then refresh.

Hide (blank) in the pivot, then fix the source

In Excel, click the filter arrow on the Row or Column label, uncheck (blank), and click OK. In Google Sheets, open the field in the pivot editor and filter out the blank item, or uncheck it in the on-sheet filter if the pivot shows one.

That is a display filter. The empty cells are still in the source, and a new empty row will bring (blank) back. Fill the cells with the right category, or delete rows that should not be in the list. Then refresh and, if (blank) is gone from the field list, you can clear the filter so you are not hiding something you have already removed.

Do not type the word “blank” into an empty cell to “fill” it; that just creates a new item named blank. Do not put a space in the cell either — it looks empty and still is not, and it will not match a true blank in a filter.

Blanks in Values, and a leftover (blank) after cleanup

An empty Revenue cell is ignored by SUM the same way a text n/a was skipped in the 7800 total above (the text was not a blank, but the point is the same: Sum does not need a value in every row). If many amounts are empty, confirm you still want those rows in the pivot; they can pull a (blank) out of another field on the same row.

If you already cleaned the source and (blank) remains, the pivot cache may be stale: refresh, or remove the field from Rows and drag it back. A second copy of the field, or a leftover grouping, can also keep an old item. In Excel, PivotTable Analyze > Change Data Source is the place to confirm the range no longer includes extra empty rows.

Once COUNTBLANK on the source column is 0 and the pivot has been refreshed, (blank) should disappear from the item list. If a single category still looks empty, you are probably looking at a 0 in Values, not at a blank label.