Filter Excel for Empty Cells: Blanks and Formulas

To filter for empty cells in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data > Filter, open the filter arrow for the column, and select (Blanks). To return matching rows with a formula instead, use FILTER with a condition such as =FILTER(A2:C6,B2:B6="","No blank rows"); it returns rows where column B is blank.

What Excel means by an empty cell

Filtering for empty cells sounds straightforward, but cells that look empty can contain different things. A cell may truly have no value, contain a formula whose displayed result is an empty string, or contain spaces or other invisible characters. Those cases can look alike on the worksheet but behave differently in formulas and may not be grouped identically by every filter method.

A truly empty cell has no value or formula. A formula such as one that returns “” is still present in the cell, even though it displays nothing. A cell containing a space is also not empty: it contains a text character. This distinction matters if you are checking required fields, cleaning imported data, or deciding whether a record is complete.

In a filter menu, Excel may label the blank category (Blanks). That option is useful for ordinary data cleanup, but it does not tell you why a cell looks blank. If you need to distinguish a genuinely empty cell from a formula result or whitespace, use a formula-based check or inspect the cell contents.

Filter a column for blanks using the ribbon

AutoFilter is the quickest way to show records where a particular column is blank. First make sure the data has a header row and that the table is a continuous rectangular range. Then:

Select a cell inside the data range. You usually do not need to select every row and column.
Choose Data > Filter. Excel adds a dropdown arrow to each header.
Open the arrow in the column you want to check.
Clear Select All if needed, select (Blanks), and confirm with OK.

Excel hides rows that do not meet the filter, rather than deleting them. For example, if the Status column has blank cells for records awaiting review, choosing (Blanks) displays those records while hiding the rows with a status. Clear the filter later to show all records again. In the column menu, use Clear Filter From followed by the column name, or choose Data > Clear to clear filters across the range.

If the header arrows are already present, the range is already filtered; open the relevant arrow and change its selection. In Excel for Mac, the Data tab also provides Filter and the same basic dropdown workflow. Excel for the web has a similar filter interface, though the placement and appearance of menu options can vary slightly.

Show rows with a formula

The FILTER function is useful when you want a separate, automatically updating result rather than hiding rows in the original list. It is available in Microsoft 365 and Excel 2021 or later. The formula returns matching rows in a spill range, so leave enough empty cells below and to the right of the formula for the results.

Assume the records occupy A2:C6, with the status in B2:B6. If B3 and B5 are blank and the other status cells contain values, enter =FILTER(A2:C6,B2:B6="","No blank rows") in an unused cell. The result is the full rows A3:C3 and A5:C5, because those are the two records whose status cells are blank. If no status cell is blank, the formula displays No blank rows instead of returning a no-results error.

To show records with a value in the status column instead, use the same layout and enter =FILTER(A2:C6,B2:B6<>"","No nonblank rows"). With the example above, it returns rows A2:C2, A4:C4, and A6:C6. The condition checks each cell in B2:B6, while the first argument tells Excel which complete rows to return.

FILTER recalculates when its source data changes, but it does not alter the original list. If you type a value into a previously blank status cell, that row drops out of the blank-results spill and can appear in the nonblank results instead. The formula’s source and condition ranges must be the same height; mismatched dimensions can produce an error.

Tell true blanks apart from formulas and spaces

Use a helper column when a filter must distinguish truly empty cells from cells that merely appear empty. With source values in B2:B6, enter =ISBLANK(B2) in C2 and fill it down through C6. ISBLANK returns TRUE only when the referenced cell is genuinely empty. A cell containing a formula that displays an empty string returns FALSE because the formula itself occupies the cell.

If you want to treat both genuinely empty cells and formulas that return an empty string as blank, use a different test. With the same values in B2:B6, enter =B2="" in C2 and fill it down. This comparison returns TRUE for a truly empty cell and for a formula result equal to an empty string. Filter the helper column for TRUE to show those records. It does not treat a cell containing a space as empty.

To flag cells containing only ordinary spaces as well, a helper formula can trim those spaces before comparing the result. With the value to check in B2, enter =TRIM(B2)="". This identifies a truly empty cell, an empty-string result, or text made up only of ordinary spaces. TRIM does not remove every possible invisible character, such as some nonbreaking spaces commonly found in copied web data; those may need separate cleanup.

Filter for nonblank cells or incomplete records

Finding filled-in cells is the reverse of filtering for blanks. In the dropdown for the relevant column, select (Nonblanks) if that option is available, or select all values and deselect (Blanks). In a FILTER formula, the not-equal-to-empty-string condition shown above returns cells that are not blank under that comparison. Be aware that a formula returning “” compares as an empty string, while text containing a space does not.

To find incomplete records, filter the column that contains the required field. For instance, a blank value in an Order ID column may identify a record that needs attention. Filtering one column does not mean that every cell in the displayed row is empty: it means only that the selected column meets the blank condition. A row can have a customer name and date but still appear because its Order ID is missing.

If a record should count as incomplete when any one of several required fields is missing, checking just one column is not enough. You can filter each required column in turn, or add a helper column that tests the relevant fields and then filter that helper. Define what “incomplete” means first—for example, missing either an email address or a phone number—so the check matches the task rather than treating every optional field as mandatory.

Use filters with Excel tables

For a list that grows over time, converting the range to an Excel table can make filtering easier to maintain. Select a cell in the data and choose Insert > Table, confirm that the range includes headers, and select OK. Tables include filter arrows in their header row, and new rows added directly beneath the table are generally included in the table as it expands.

Table filters use the same basic idea as range filters: open the column menu and choose the blank category to show rows with blank cells in that column. A table also makes it easier to read the meaning of each field because its header row stays distinct from the records. If you use a formula to create a separate filtered result, ensure its output area does not overlap the source table or other occupied cells.

Filtering is a view of the data, not a correction to it. If the blanks represent missing information, enter or import the correct values only after confirming them. If they mean not applicable, consider using a clear marker such as Not applicable when that distinction matters to reports or downstream calculations. Do not replace blanks with zero automatically: zero is a real numeric value and can change sums, averages, and business meaning.

Why the Blanks option may be missing

If you cannot find (Blanks), check the range and the column before assuming Excel has lost the data. Common causes include:

The filter range is incomplete. If blank records sit below or beside the selected range, they may not be included. Clear the filter, select the full data area, and apply Data > Filter again.
The column is not actually blank. A space, apostrophe, formula, or imported character may make a cell look empty while it contains something. Click the cell and inspect the formula bar, or use a helper test such as ISBLANK.
The data has no blank cells in that column. Excel cannot offer a blank category when there are no blank entries in the filtered records.
Another filter is narrowing the records. Clear other column filters and check whether blank entries become available in the list.
There are blank rows inside the data. A completely empty row can interrupt the range Excel detects. Select the whole intended range, including the rows below the gap, before applying the filter.

Also check whether the header row is correct. If a data row was mistakenly included as a header, or the range has multiple header-like rows, Excel may not filter the records as expected. A clean structure—one header row, one record per row, and consistent fields in each column—makes filters more reliable.

Choose between AutoFilter and a formula

AutoFilter is usually the better choice when you want to inspect or edit the original records. It keeps the list in place, hides nonmatching rows, and lets you change criteria interactively. It is also convenient for one-off checks, such as reviewing all records with a missing date.

FILTER is useful when you want a second view that updates automatically, such as a report of unassigned tasks beside the source list. It leaves the source visible and can return several columns at once. It requires a version that supports dynamic arrays—Microsoft 365 or Excel 2021 and later—and the result needs room to spill. It can also be easier to share a formula-based result than a worksheet left in a filtered state, provided the recipient has a compatible version.

A helper column is often the clearest option when the meaning of “empty” is more specific than Excel’s ordinary blank category. It makes the test visible and can separate truly empty cells, empty-string formulas, and whitespace. The tradeoff is that it adds a column that may need to be maintained as the dataset changes.

Frequently Asked Questions

How do I filter blank cells in Excel?
Select a cell in the data, choose Data > Filter, open the arrow for the column, and select (Blanks). This displays records whose cells in that column are blank; it does not require the entire row to be empty.

How do I filter out blank cells?
Open the column’s filter menu and select (Nonblanks), or clear the selection for (Blanks) while leaving the other values selected. You can also use a FILTER formula with a not-equal-to-empty-string condition to return rows with a value.

Does Excel treat a formula returning an empty string as a blank?
A formula returning “” looks blank but is still a formula in the cell. For a dependable distinction, test with ISBLANK for truly empty cells or compare the cell with “” when both true blanks and empty-string results should count as blank.

Why does my blank cell not appear under (Blanks)?
The cell may contain a space or another invisible character, fall outside the filtered range, or be excluded by another active filter. Check the formula bar, clear other filters, and confirm that the full data range is included.

Can I filter for blanks without hiding rows in the original list?
Yes. In Microsoft 365 or Excel 2021 and later, use FILTER in an unused area to return matching rows as a separate spill range. The source stays visible, and the results update when the source data changes.

Does filtering delete rows from my workbook?
No. AutoFilter hides rows that do not match the selected criteria; it does not remove them. Clear the filter to display the rows again. Deleting visible rows is a separate action and should not be confused with filtering.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.