Fix Excel Filter Displays Rows That Do Not Match Criteria
Question details
The user is experiencing issues where applying a filter in Excel displays rows that do not match the selected filtering 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 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.
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.
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).
Select the cell and press F2, or click directly into the formula bar at the top of the window.
Replace the existing formula (e.g., =SUBTOTAL(9, C2:C100)) with the standard SUM formula (e.g., =SUM(C2:C100)).
Press Enter to save the changes, then reapply your filter from the Data tab to ensure only the correct rows are displayed.

Add a Blank Row Below the Data Range
Insert a blank row between your raw data and the formula rows to isolate the dataset and stop the filter from capturing formula results.
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. Open your dataset: Launch WPS Spreadsheet and open the file containing the data you wish to filter.
- 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. Apply AutoFilter: Navigate to the 'Data' tab on the top ribbon and click on the 'AutoFilter' button.
- 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.

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.




