To filter blank cells in Excel, turn on filtering for your data, open the dropdown in the column you want to check, and select only (Blanks). Excel will hide rows with a value in that column and show rows where it is blank. First make sure your data has a header row and that the filter range includes all the records you want to check.
What You’ll Need
- A workbook in Excel for Microsoft 365. The steps below are for Excel on Windows; the ribbon names are very similar in Excel for Mac and Excel for the web.
- A table or a continuous range of data with a heading at the top of each column. If your data has no headings, add them first so Excel does not treat the first record as a heading.
- The name of the column whose blank cells you want to find. Filtering one column finds rows where that particular field is blank, not necessarily rows that are empty across every column.
Step-by-Step
- Prepare the data. Check that each column has a clear heading, such as Order Date or Email, and that each record is on its own row. Avoid merged cells in the range. If there are completely empty rows between sections, either remove those separators or select the full data range explicitly before applying the filter; a gap can make Excel treat part of the list as a separate block.
- Choose the data range. If the records form one uninterrupted block, click any cell inside it. Excel can usually detect the surrounding range. If the block has gaps or you want to be certain about its boundaries, select the whole range, including the headings and all records. Do not include unrelated notes or totals below the list unless you want those included in the filtering range.
- Turn on filters. On the Windows ribbon, select Data > Filter. Dropdown arrows should appear beside the column headings. If the data is already an Excel table, its header row normally has filter arrows; you do not need to switch filtering on again. In Excel for Mac and Excel for the web, use Data > Filter as well. If you prefer a keyboard shortcut on Windows, Ctrl+Shift+L toggles filters for the selected range; shortcut behavior can vary on Mac, so the ribbon command is a reliable alternative.
- Open the dropdown for the right column. Select the arrow in the heading of the column where you want to find missing entries. For example, to find records with no email address, open the dropdown in the Email column. Do not choose a different column just because its dropdown is easier to reach: the result depends on which field you filter.
- Show only blanks. In the dropdown checklist, clear (Select All), then check (Blanks) and select OK. Depending on the Excel build and the list, the item may be shown as Blanks without parentheses. The worksheet now displays rows whose selected-column cells are blank; other records are temporarily hidden, not deleted.
- Review the filtered records. Look at the row numbers and the selected column to confirm the results make sense. A filter on Email, for example, can show a row with a blank Email cell even when its Name and Phone cells contain information. That is expected: Excel filters whole records according to the chosen field, rather than showing only isolated cells.
- Clear the filter when finished. Open the same column dropdown and choose Clear Filter From [column name], or use Data > Clear in the Sort & Filter group. Clearing restores the hidden records. You can also open the dropdown and select (Select All) again. Turning off Data > Filter removes the arrows, but clearing the filter first makes it easier to see that all records are included.
Common Mistakes
Filtering the wrong field. A blank in one column does not mean the entire row is empty. If you filter the Status column, you will see records with no Status value, even if other cells in those rows are filled in. Decide whether you are looking for missing values in one field or completely empty records before choosing a method.
Selecting only part of the list. If the filter range leaves out lower rows or a column, those records will not respond as expected. Check that the dropdowns appear on the intended header row and that the range covers all relevant records. For a list that changes often, formatting it as a table with Home > Format as Table can make the data range easier to manage.
Assuming hidden means deleted. Filtering hides rows that do not match; it does not remove them. Be careful when copying, editing, or deleting filtered results. Before deleting any records, verify the filter and consider saving a copy of the workbook. Clearing the filter brings the other rows back into view.
Confusing empty-looking cells with truly empty cells. A cell may look blank but contain spaces, a formula that displays an empty string, or other content. Such cells may not behave like genuinely empty cells in every filtering situation. If the blank option is missing or results seem incomplete, use the checks in Troubleshooting.
Tips
- Filter more than one column to find fully empty records. Apply a blanks filter to each relevant column. Excel combines filters across columns, so the visible rows must meet both conditions. For instance, filtering blanks in both Name and Email shows records where both fields are blank. It does not require unrelated columns to be blank.
- Use a helper column when the definition of blank needs to be explicit. This is useful when cells contain formulas or spaces, or when you need every cell across a row to be blank. Put the check in a new column, fill it down through the records, then filter that helper column for TRUE. Keep the helper column outside the original fields or give it a clear heading so it is easy to remove later.
- Keep headings visible and descriptive. A heading such as Missing Email makes it easier to understand the purpose of the filter later. If the workbook is shared, note whether the filter is intended to find genuinely empty cells or values that only appear empty.
- Use a table for ongoing lists. Select the data and choose Home > Format as Table, then confirm whether the table has headers. Table filter arrows are built into the header, and new rows added directly below the table are usually included in it. Check the table boundaries if you paste data elsewhere or add a separate block.
Troubleshooting
There is no (Blanks) option. First confirm that the correct column is selected and that the filter covers all the rows. If the column has no empty cells within the filtered range, Excel has no blank category to offer. Also check for completely blank separator rows: the list may have been detected as a smaller range. Select the intended range, including its headers, and choose Data > Filter again. If the cells only look empty, test them with a helper formula rather than relying on appearance.
With the source values in A2:A20 and a helper column beside them, enter this in B2 and fill it down. The formula returns TRUE when A2 is genuinely empty or contains a formula whose displayed result is an empty string; it returns FALSE for a cell containing ordinary text or a space.
=A2=""
Filter the helper column for TRUE to see those rows. A cell containing one or more spaces does not equal an empty string, so it returns FALSE. To check only for a genuinely empty cell, use the following instead; with the same A2:A20 source layout, it returns TRUE only when A2 has no value or formula in it.
=ISBLANK(A2)
You need rows that are blank across several columns. Filtering blanks in one field is not enough if your goal is to locate rows with nothing in the whole record. If the relevant fields are in A:D and the first record is row 2, put this in E2 and fill it down. It returns TRUE when all four cells in A2:D2 are blank or display an empty string, and FALSE if any contains a value. Then filter column E for TRUE. Cells containing spaces count as nonblank.
=COUNTBLANK(A2:D2)=COLUMNS(A2:D2)
The filter hides unexpected records or shows none. Confirm that the dropdown belongs to the column you intended, and clear filters in other columns that may still be active. Check that the heading row is not being treated as a record and that the selected range includes all data. If the values were recently changed, reopen the dropdown and reapply the filter so the checklist reflects the current entries.
You cannot see all the matching rows. The worksheet may have other filters applied, or the matching rows may be outside the visible part of the sheet. Use Data > Clear to remove active filters, then apply the blanks filter again to the intended range. Also check whether rows are manually hidden; clearing a filter does not necessarily unhide rows hidden by a separate command.
Frequently Asked Questions
Does filtering blanks delete the other rows?
No. It temporarily hides rows that do not meet the filter condition. Use Data > Clear or clear the filter from the column dropdown to show them again.
Can I filter blanks in two columns at once?
Yes. Filter the first column for blanks, then filter the second column for blanks. Excel applies both conditions together, so a row remains visible only if it is blank in both selected columns.
How do I find rows that are completely empty?
Filter every relevant column for blanks, or use a helper column such as the COUNTBLANK check above. Be clear about which columns define a complete record; a row can be empty in the fields you care about while containing a note in another column.
Will Excel treat a formula returning an empty string as blank?
It may look blank, but formula-generated empty strings and truly empty cells are different. If filtering does not catch the formula results as expected, use a helper formula such as =A2="" to test both, or =ISBLANK(A2) when you want only genuinely empty cells.
Why does a cell with spaces not appear as blank?
Spaces are characters, so a cell containing them is not truly empty. Remove the spaces or use a helper check designed for the data you have. Inspect a few results before changing values, especially if spaces may be meaningful.
Are the steps different in Excel for Mac or the web?
The filter dropdown method is essentially the same: use Data > Filter, then choose the blank item in the column menu. Ribbon layout and keyboard shortcuts can differ by platform, so use the visible menu command if a Windows shortcut does not work.
Related
- How to Duplicate an Excel File Shortcut on Windows
- How to Duplicate a Page in Excel and Keep Its Layout
- Duplicate a Sheet in Excel: Keyboard Shortcut and Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNT Formula Examples: Numbers, Blanks, and TextIn Excel for Microsoft 365, use =COUNT(A2:A20) to count cells containing numbers, including dates and times. For other…
- Excel Goal Seeking: Find a Target Value with What-If ToolsExcel Goal Seek finds the input value that makes a formula return a result you specify. In Excel…
- How to Filter Highest to Lowest in Excel: A Quick GuideTo show the largest values first in Excel, open the filter arrow on the column you want to…
- XLOOKUP Excel Conditional Search: Formulas and ExamplesTo use XLOOKUP for a conditional search in Excel, put the condition in its lookup array; for multiple…
- Delete an Array Formula in Excel Without Losing DataTo delete an array formula in Excel for Microsoft 365 on Windows, select the formula cell and press…
- Calculate Age in Excel by Birth Date: Exact FormulasTo calculate age in completed years, put the birth date in A2 and enter =DATEDIF(A2,TODAY(),"Y") in another cell.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.