Filter in Excel With Blank Rows: Keep Records Together

Formulas & functions · Updated November 20, 2026

To filter data that contains blank rows, select the entire data range—including the blank rows—and choose Data > Filter in Excel for Microsoft 365 on Windows. Then use the dropdown in a header cell to choose what to show. If blank rows are just visual separators, consider removing them or turning the range into a table so the filter covers the intended records consistently.

What You’ll Need

  • An Excel worksheet with one header row and data arranged in columns.
  • A clear idea of whether the blank rows are part of the dataset or only separate sections visually.
  • For the steps below, Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web have similar filtering controls; differences are noted where useful.

Step-by-Step

  1. Check the layout before applying a filter. Make sure each column has a descriptive heading, such as Date, Department, and Status, and that the headings are all on the same row. Excel uses that row for the filter dropdowns. Avoid merged heading cells, extra title rows within the data, or separate subheadings between groups. If the worksheet has a title above the column headings, start the selected range at the actual header row rather than the title.
  2. Decide what the blank rows mean. A blank row in the middle of a list can be included in the filter range, but it does not behave like a record with values. If it is only a spacer between records, you may prefer to remove it. If it separates meaningful sections or you need to preserve the layout, keep it and select the full range explicitly. Filtering is row-based: it displays or hides complete rows according to the values in the selected column.
  3. Select the full range, including the gaps. Click the first header cell, then select through the last used column and the final row of data, making sure the selection crosses every blank row. For a precisely defined range, click the Name Box to the left of the formula bar, type a range such as A1:F200, and press Enter. Replace those cell references with the actual top-left and bottom-right cells in your sheet. Avoid relying on Ctrl+A or a double-click to find the range when there are gaps: Excel may stop at a blank row and select only one section.
  4. Turn on the filter for the selected range. On Windows, open Data > Filter. Excel adds a dropdown arrow to each header in the selected range. You can also use Home > Sort & Filter > Filter. The Windows shortcut Ctrl+Shift+L toggles filtering on and off; on a Mac, use Command+Shift+F if that shortcut is available in your Excel setup, or use Data > Filter. In Excel for the web, use the Data tab’s Filter command.
  5. Confirm that the filter covers both sides of each blank row. Open a dropdown in a header near the left or right of the list and check that the arrow is present on the headers throughout the intended range. If the dropdown arrows appear only above the first block of data, turn the filter off, select the whole range again, and apply it. Do not assume that Excel has included rows below a gap just because the upper section has dropdowns.
  6. Choose the values to display. Click the dropdown arrow for the column you want to filter. Clear Select All, tick the value or values you want to keep, and select OK. For example, to show only orders marked Open, select Open and clear the other status values. To show rows where that column has no entry, choose (Blanks). The exact list depends on the values in the selected range, and blanks may appear as a separate choice.
  7. Check what happened to the empty rows. When you filter for a nonblank value, a completely empty row will normally be hidden because it has no matching value in that column. When you filter for (Blanks), Excel shows rows with a blank in that column; those rows may still have information in other columns. A row is not necessarily completely empty just because one field is blank. Look across the row before treating it as a spacer or deleting it.
  8. Change or clear the filter when finished. Open a column’s dropdown to select different values, or use Data > Clear to remove the active filter criteria while keeping the dropdowns. To turn off the dropdowns, use Data > Filter again. Clearing a filter restores hidden records; it does not delete them. If you are working in a table, use the dropdowns in the table headers and the table will retain its filter controls as you add records.

Optional: make the range a table. If you will filter this list regularly, select the full range, including the blank rows, and choose Insert > Table. Check the selected cell range, tick My table has headers if the first row contains headings, and choose OK. A table supplies filter dropdowns and can expand when you add data beneath it. Empty rows inside the selected range remain empty table rows; they are not a replacement for consistent records or a well-structured list.

Common Mistakes

  • Selecting only the first block. When a list has a fully blank row between sections, selecting a cell and using Ctrl+A can select only the connected block around that cell. Apply Filter to an explicitly selected range instead, or remove spacer rows first if they are not needed.
  • Filtering the wrong column. Choosing (Blanks) in the Status column finds rows with an empty Status cell, not necessarily rows that are entirely empty. Inspect the rest of the row before deciding what those results mean.
  • Expecting Excel to understand grouped records. A filter evaluates each row, not the visual section it appears under. If a category is written once and the cells beneath it are blank, filtering that category column will not treat those blank cells as if they inherited the label.
  • Deleting every blank cell or row without checking. A blank field can be intentional, and deleting entire worksheet rows may remove other information outside the list. If you need to remove separators, select only the relevant rows in the dataset and confirm that the records are genuinely empty first.
  • Using multiple header rows or merged cells. A title row, repeated headings, or merged cells inside the range can make Excel identify the wrong header or filter range. Keep one heading row at the top of a simple rectangular list.

Tips

  • Keep the list rectangular. Put one record on each row and one field in each column. Avoid blank rows within a list if they are only there for appearance; use borders, banded rows, or a table style to distinguish records instead.
  • Use a helper column for section labels. If records belong to a group but the group name appears only once above several blank cells, fill the group name into each record’s row or add a separate helper column with a label on every row. Then filter that column to find the full group. Make a copy first if you need to preserve the original visual layout.
  • Use more than one dropdown for combined criteria. You can filter one column and then another; Excel applies both criteria at once. For example, show the Open status and then choose a particular department. To return to all departments while keeping the status filter, clear only the department filter.
  • Use a table for a growing list. Tables are convenient when new records are added regularly because the structured range and its header controls can expand. Before creating one, make sure the selection starts at the right header and reaches the last record you intend to include.
  • Keep an untouched copy before restructuring. Removing blank lines or filling down labels changes the worksheet. Duplicate the sheet or save a copy before making those changes, especially if the gaps communicate section breaks to other readers.

Troubleshooting

  • Dropdown arrows appear only above part of the list: Turn off Filter, select the entire intended range from its header row through its last record—including the rows below gaps—and apply Data > Filter again. If you are unsure of the last row, inspect the bottom of the data before entering the range in the Name Box.
  • The filter dropdown does not show (Blanks): Check that the selected range includes rows with empty cells in that column and that you are opening the correct column’s dropdown. A cell containing a space or a formula that returns an empty string may not be treated the same as a truly empty cell. Inspect a sample cell and remove unintended spaces or formulas if appropriate.
  • Rows you expected to see disappear: Review every active filter. Excel combines filters across columns, so a record must meet all active criteria to remain visible. Open the dropdowns and clear filters one at a time to identify which condition is excluding the row.
  • Rows appear under the wrong category after filtering: Check for category labels that appear only in the first row of a section. Filtering does not carry a label down over blank cells. Add a label to every applicable record row, then filter by that populated field.
  • Filter is unavailable or behaves unexpectedly: Check whether the worksheet is protected, whether the selection contains merged cells, and whether the range has a single header row. If the data is already a table, use its header dropdowns. If Excel shows arrows but seems to omit records, clear the filters and reapply them to a verified range.

Frequently Asked Questions

Can Excel filter a list that has completely blank rows in the middle?
Yes. Select the full range explicitly, including those rows, before choosing Data > Filter. A blank row has no values to match, so it will usually disappear when you filter for a specific nonblank value.

How do I show only blank rows?
Open the dropdown for the column you want to check, clear Select All, select (Blanks), and choose OK. This finds rows where that field is blank; check other columns if you mean rows that are empty across the entire list.

Why does Ctrl+A select only part of my data?
Excel’s current-region selection can stop at a completely blank row or column. Use the Name Box to enter the full address of the list, or drag-select from the header to the final row and column before applying the filter.

Will filtering delete blank rows or records?
No. Filtering hides rows that do not meet the criteria; it does not remove them. Use Data > Clear or clear the relevant dropdown selections to show the hidden records again.

Should I remove blank rows before filtering?
Remove them if they are merely visual separators and you want a clean, continuous list. Keep them if they have a purpose, but include them in the selected range. Never delete rows just because one cell is blank; other cells may contain needed information.

Does this work in Excel for Mac and Excel for the web?
Yes. Select the complete range and use the Filter command on the Data tab. Ribbon details and shortcuts can differ by platform or configuration, so use the menu command if a keyboard shortcut does not work.

Related

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.