Office Settings & Configuration

Fix an Excel Filter That Leaves the Last Row Visible

Ayan MasoodAyan Masood Nov 20, 2026 868 views

Question details

The user needs the final data row to be included when filtering an Excel worksheet.

Why Does an Excel Filter Leave the Last Row Visible?
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet is filtered, but the final row appears to remain visible regardless of the filter.
Observed behavior
The final row may be outside the filter range. The user also reports that an Advanced Filter range reverts after being changed.
Before you start

Identify the header row, the last data row, and the columns that should be filtered. Check whether the data is a regular range or an Excel table, and whether a blank row separates the last record from the rest.

Solution 1Recommended

Redefine the Excel filter range to include the last row

Use this method when the last row is outside a regular filter range or an Advanced Filter list range.

1
Check for blank rows

Inspect the rows above the final record. If a fully blank row separates it from the data, select the blank row and delete it, or explicitly include it when selecting the range.

2
Select the complete range

Turn off the existing filter with Data > Filter, then select the headers and all data rows and columns, including the final row. Choose Data > Filter again and apply the desired filter.

3
Check Advanced Filter if used

For an Advanced Filter, open Data > Advanced and inspect the List range. Select or enter the complete range, such as $A$1:$D$101, adjusted to match your worksheet, then click OK.

4
Test the result

Apply a filter that should exclude the last record and check whether the row is hidden. If the range changes back, inspect the workbook structure and test the same steps in a redacted copy.

Redefine the Excel filter range to include the last row
Use the matching filter control: The List range in Data > Advanced applies to Advanced Filter. For a standard AutoFilter, select the full range before turning Data > Filter back on.
Free Microsoft Office alternative

Filter spreadsheet data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use WPS Spreadsheets to filter data as you move between office applications.

Filter spreadsheet rows using familiar column controlsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for an easier transition from ExcelWork with a free, lightweight office suite
QA img-9

Frequently Asked Questions

Why does the last row remain visible after I filter in Excel?

It may be outside the selected filter range, especially if a blank row separates it from the other records. Select the full range, including the headers and last row, and reapply Data > Filter.

How do I check the range used by an Advanced Filter?

Open Data > Advanced and inspect the List range. Make sure its final cell includes the last required row and column before clicking OK.

Why is my Excel table's total row still visible?

The table total row is separate from the data rows and normally remains visible when filtering. Click the table and check the Total Row setting on the Table Design tab.

What if the Advanced Filter range keeps changing back?

Reopen Data > Advanced to check the List range, inspect the workbook structure, and try the same filter in a redacted copy. The cause may depend on the workbook, so the range change alone does not identify it.