Function Problems

Fix Excel Filter Displays Rows That Do Not Match Criteria

Kushani NimanthikaKushani Nimanthika Nov 2, 2026 868 views

Question details

The user is experiencing issues where applying a filter in Excel displays rows that do not match the selected filtering criteria.

Fix Excel Filter Displays Rows That Do Not Match Criteria
Product
Excel
Device & OS
not provided
Scenario
Filtering a dataset that contains summary formulas, specifically SUBTOTAL, at the bottom of the columns.
Observed behavior
The filter results unexpectedly include rows that should be hidden, and the filter choices or results change when related columns or rows are modified or deleted.
Before you start

Before troubleshooting, carefully review the bottom of your dataset to see if any calculation rows, especially those using the SUBTOTAL function, are included in the filtered range.

Solution 1Recommended

Replace SUBTOTAL Formulas with SUM

Change the SUBTOTAL function to the SUM function to prevent Excel from confusing calculation rows with actual data rows during filtering.

Excel's AutoFilter sometimes attempts to keep SUBTOTAL formula rows visible so users can see dynamic totals. However, this can cause the filter criteria to behave unpredictably or include unmatched rows.

1
Locate the formula cell

Scroll to the bottom of your data range and locate the cells containing the SUBTOTAL formula (often found in related numeric columns like column C).

2
Edit the formula

Select the cell and press F2, or click directly into the formula bar at the top of the window.

3
Change to SUM

Replace the existing formula (e.g., =SUBTOTAL(9, C2:C100)) with the standard SUM formula (e.g., =SUM(C2:C100)).

4
Reapply the filter

Press Enter to save the changes, then reapply your filter from the Data tab to ensure only the correct rows are displayed.

Replace SUBTOTAL Formulas with SUM
Formula Compatibility: Using SUM instead of SUBTOTAL completely detaches the summary row from Excel's dynamic AutoFilter logic, ensuring strict adherence to your filter criteria.
Data Management in WPS

Easily Filter Data and Manage Formulas with WPS Office

WPS Spreadsheet provides robust filtering tools and full compatibility with Excel functions, allowing you to accurately organize your data without unexpected errors.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing the data you wish to filter.
  2. 2. Select the correct range: Highlight the exact data range you want to filter, explicitly excluding any summary or formula rows at the bottom.
  3. 3. Apply AutoFilter: Navigate to the 'Data' tab on the top ribbon and click on the 'AutoFilter' button.
  4. 4. Set filter criteria: Click the drop-down arrows on your column headers to select or type your specific filtering criteria. The data will filter accurately without pulling in mismatched formula rows.
Fully compatible with Microsoft Excel (.xlsx, .xls) files.Accurate data filtering and advanced sorting capabilities.Comprehensive formula library including SUBTOTAL and SUM.Free, lightweight, and user-friendly interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the SUBTOTAL function affect Excel filters?

Excel's AutoFilter feature tries to automatically detect summary rows at the bottom of a dataset. When it detects a SUBTOTAL function, it may intentionally keep that row visible so you can see the summary of the filtered data, which sometimes disrupts the standard filtering logic and pulls in unexpected rows.

Can I use filters without affecting my total calculations?

Yes. If you convert your raw data into an official Excel Table (by selecting the data and pressing Ctrl+T), you can enable the 'Total Row' feature from the Table Design tab. This built-in feature automatically manages SUBTOTAL formulas and filtering correctly without displaying mismatched rows.

How do I completely reset a corrupted filter in my spreadsheet?

To fully reset your filters, go to the Data tab and click the 'Clear' button in the Sort & Filter group to remove any hidden criteria. For a harder reset, click the 'Filter' icon to turn filtering off entirely, select your clean data range again, and click 'Filter' to re-enable it.