Excel applies AutoFilter criteria only to rows it has registered as data records. If only the first row below your headers responds to the filter, the most common cause is that Excel does not see the lower rows as part of the same contiguous data range, either because blank rows or columns interrupt the block or because the filter range was attached to a single row. Selecting the full dataset and reapplying the filter (Data > Filter, or Ctrl+Shift+L twice) fixes most cases and turns on the complete filter scope.
The symptom feels inconsistent because Excel often does filter the whole region while a display artifact, such as a hidden row height, makes it look like one row survived. Diagnosing which of the three failure classes is active takes under a minute once you know where Excel stores the filter's range reference. The sections below explain the mechanism, show how to measure your data region, and separate real filters from lookalikes.
How AutoFilter range capture works
AutoFilter is not a property of a column. It is a saved range reference in the worksheet, roughly "rows 1 through N of columns A through D," plus one filter criterion per filtered column. When you click Filter, Excel evaluates the current selection and expands it using a contiguous-block rule that has been stable in Windows and Mac desktop builds for years.
The rule: starting from the active cell, Excel expands outward until it hits a fully blank row or a fully blank column. It stops at the first fully blank boundary in each direction. Every row above the header row is treated as header text; every row below within the block is treated as data.
That gives you two predictable failure modes. First, if row 2 is the last populated row before a blank row, the captured range ends at row 2, and rows 4 and beyond are outside the filter. Second, if a column inside your table is entirely blank, the expansion stops at that column, so filter dropdowns appear on the left block while the real data extends right.
A single blank cell inside a column does not break the range. Only a fully blank row or a fully blank column does. This is why a table with occasional blanks in one field still filters correctly, while a "spacer" row between sections silently halves the filter scope.
Selection direction also matters. If you select only A2 before clicking Filter, Excel may capture a narrow or one-row range. If you select the entire block first, or click a single cell inside a table with no internal blank rows, the expansion captures the full block. The professional caveat: merged cells and subtotal rows count as populated for the top and bottom scan, so Excel may capture a range that includes subtotals as data records, which then get filtered out unpredictably.
Quantifying the range you actually have
Before changing anything, measure. Press Ctrl+End and note the last used cell. If it sits far below the row you believe is last, stray formatting or a leftover value is extending the used range and can change what Excel captures. Then press Ctrl+Down from the header row's first data cell; the cursor stops at the last contiguous populated cell in that column, which reveals the internal blank row that is cutting off your filter.
Check the filter's stored range directly by selecting any cell in the filtered range and pressing Ctrl+A, which selects the current region under the same contiguous rule. The Name Box then displays the range, for example A1:D2 rather than A1:D40. That single readout is the fastest confirmation of a truncated filter.
Sizing matters because Excel's row limit is 1,048,576 in current desktop versions, so "too many rows for the filter" is almost never the real answer below that ceiling. A filter applied to a table with a few thousand rows is well inside engine limits. If the Name Box shows a range ending two rows below the headers, the problem is structural, not a capacity limit.
Why the symptom appears as one row
Four distinct situations produce a "first row only" appearance, and they need different fixes.
The first is the genuinely truncated range described above. The second is a manually hidden-row state: if rows 3 through 40 have zero row height set manually, filtering rows 2 onward still leaves row 2 visible and everything else invisible, which reads as "the filter isn't working."
The third is Table objects. If you converted a range to a real Excel Table (Ctrl+T), structured references define the range automatically. A filter applied outside the table range can behave inconsistently with the table's own header filter buttons, and rows added at the bottom get pulled into the table while rows added above the headers do not.
The fourth is a sorted or grouped outline. If rows are collapsed into an outline group, filtering does not expand the group, so the surviving row may be the only one outside the collapsed band.
Red-flag warnings to rule out a data problem rather than a range problem:
The last visible row shows a valid result, and every hidden row shares one identical value, which suggests a filter criterion is matching exactly one record. The column has trailing spaces in the header, so the header and data column are treated as different fields. The first data row was pasted as values while lower rows are formulas returning errors, which can make the filter dropdown show blank or error categories only. The sheet is protected with filtering allowed but row insertion or deletion disallowed, so the range cannot be extended. The file is shared from a web or mobile source with limited feature support, and the range was captured on a device with a smaller visible area.
AutoFilter versus Table versus Advanced Filter
Excel offers several filtering systems, and they store scope differently. Choosing the wrong one for your data shape creates the symptom.
| Filter type | How the range is defined | Rows added later | Best for |
|---|---|---|---|
| AutoFilter (Data > Filter) | Contiguous block from active cell | Not included until range is reapplied | Fixed, one-off reports |
| Excel Table filter (Ctrl+T) | Structured table name | Automatically included at bottom | Growing datasets |
| Advanced Filter | Explicit list range or criteria range | Not included until rerun | Complex multi-condition extraction |
| Filter inside a PivotTable | Pivot cache source range | Depends on cache refresh | Summaries, not raw rows |
| Slicer-driven filter | Bound to a Table or PivotTable | Same as bound object | Dashboards |
| Sort + hidden rows (manual) | None; rows merely hidden | No effect | Never a substitute for filtering |
The practical distinction: AutoFilter glues itself to the block present at the moment you apply it, while a Table redefines the block on every entry. If your data grows, the Table is the more durable choice. If your data is a fixed exported report, AutoFilter is fine as long as you select the whole block first.
Practical steps that fix it
Work through these in order and stop when the Name Box shows the full range.
Select the entire data block including headers, from first header cell to the last populated data cell. Do not select full columns unless the sheet contains only that table. Press Ctrl+Shift+L twice. The first press removes the existing filter, and the second reapplies it across the current selection, resetting the stored range. Confirm the range in the Name Box after pressing Ctrl+A from a data cell. A correct filter shows A1 through the last data row. Delete fully blank rows and any fully blank columns that sit inside the table. If the blank rows carry meaning, move that separation to a helper column instead. Remove merged cells from the data body. Keep merges confined to the header row, or replace merged headers with centered alignment across unmerged cells. If the block will grow, convert it to a Table and use the table's filter buttons rather than Data > Filter. Reset manual row heights: select all rows, then Home > Format > Row Height and set a numeric value such as 15, which unhides zero-height rows.
Threshold guidance: if the filter's stored range covers fewer than 90 percent of the rows you consider data, treat the range as structurally wrong and fix the blank boundaries rather than reapplying the filter over it. Reapplying a filter does not merge separate blocks; it only resets to the contiguous block under the active cell.
Limits and what the symptom cannot tell you
AutoFilter cannot span two non-adjacent blocks in a single filter. If your report has a title row, a blank row, and then the table, Excel will treat the title and the table as separate regions. The correct fix is to remove the blank separator or to build the table on its own sheet.
Filtering also cannot distinguish visual rows from data rows. Outline groups, manually hidden rows, and zero-height rows all remain "in range" for the filter engine, so a filter can appear broken while it is operating correctly on rows you cannot see.
Performance limits are rarely the cause. A filter over tens of thousands of rows recalculates in well under a second on modern desktop hardware when the data is local. If filtering feels slow, the cause is usually volatile formulas in the filtered columns or an external data connection, not the filter range itself.
Last, no diagnostic can tell you what the original author intended. A truncated filter may be deliberate, protecting rows above from a filter meant only for a summary line. Before reapplying a filter across a larger block, check whether the lower rows are reference data or calculation notes that should stay unfiltered. When in doubt, work on a copy, confirm the selection, and only then save over the original.