Refresh an Excel Table Filter After a Criteria Cell Changes
Question details
The user needs to update Excel filter results after a criteria cell changes and a formula-based helper column recalculates.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- A table uses a helper column, such as a match result based on a search name or membership number, as the filter criterion.
- Observed behavior
- The helper-column formulas update, but the existing table filter does not automatically reapply to show the newly matching or nonmatching rows.
Confirm that the helper-column formula returns a consistent value such as TRUE/FALSE for every table row. If your data is in an Excel Table, verify the filter arrow is enabled on the helper-column header before reapplying the filter.
Reapply the existing Excel table filter
Use this method when you want to keep the current table filter and refresh its results after the criteria cell or helper-column formula changes.
Excel stores the result of a table filter when it is applied. When formulas in the filtered column recalculate later, use Reapply to evaluate the current filter condition against the updated values.
In the worksheet, enter or select the new name, membership number, or other value in the cell used by your helper-column formula.
On the Excel ribbon, select the Data tab while the workbook is open.
In the Sort & Filter group, select Reapply. In English Excel for Windows, you can also press Alt+A, then Y.
Check the filtered table. Rows whose helper-column result now meets the active filter condition should be displayed.
Use a dynamic FILTER formula for automatically updated results
Use this method in Microsoft 365 or Excel 2021 when you want a separate result range that recalculates immediately whenever the search criteria changes.
Refresh filter results or create live matching lists in WPS Spreadsheets
WPS Spreadsheets lets you filter table data through the Data tab and use formula-driven matching columns. For search results that should change immediately, use a FILTER formula in an empty output range instead of relying on a previously applied filter state.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the .xlsx or .xls workbook containing the table and helper column.
- 2. Update the criteria cell: Enter the new search value, name, or membership number in the cell referenced by the helper-column formula.
- 3. Refresh the current filter: Select a cell in the filtered table, open the Data tab, and use the available filter refresh or reapply command to evaluate the updated helper-column values.
- 4. Create a live output list if needed: Click an empty cell and enter a FILTER formula based on the matching helper column, such as =FILTER(Table1,Table1[Match]=TRUE,"No match").
Frequently Asked Questions
Why does my Excel filter not update after a formula changes?
A table filter can retain the results from when it was originally applied. If a formula-based helper column changes afterward, select Data > Reapply to evaluate the active filter against the new formula results.
What is the shortcut to reapply a filter in Excel for Windows?
In English Excel for Windows, press Alt+A, then Y. You can also use the ribbon command at Data > Reapply.
Can I make filtered results update automatically when I change a search cell?
Yes. In Microsoft 365 or Excel 2021, use a dynamic formula such as =FILTER(Table1,Table1[Match]=TRUE,"No match") in an empty output area. The results recalculate when the criteria or helper column changes.
Does FILTER replace the filter arrows in an Excel table?
FILTER does not change the table's filter arrows. It creates a separate dynamic list of matching records, while table filters hide and show rows within the original table.
Why does my FILTER formula show a spill error?
A FILTER formula needs enough empty cells for all returned rows and columns. Clear or move any content blocking the output range, then enter the formula again.
