How to Automatically Create a Summary Report with FILTER in Excel
Question details
The user needs to automatically display project records in a summary report when they meet conditions such as being overdue by a specific number of days, using the FILTER function to return multiple columns at once.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Building a project summary report from source data and showing only rows that meet a condition, such as overdue projects.
- Observed behavior
- The user wants a formula-driven summary that returns matching rows and several columns automatically instead of copying data manually.
Make sure the source data is organized as an Excel Table or a consistent range with clear headers. Check that the column used for filtering contains comparable values, such as numeric overdue days.
Use the FILTER function with an Excel Table
Best when the source data is already formatted as an Excel Table and you want a dynamic project summary that updates automatically.
The FILTER function returns all columns from a table or range that meet the condition. You can adjust the table name, column name, and threshold to match your workbook.
Click the cell where the summary report should start, such as cell A1 on a new Summary worksheet.
Type =FILTER(ProjectTable,ProjectTable[Days Overdue]>=29,"No matching projects") and press Enter.
Replace ProjectTable with your actual table name, replace Days Overdue with the column you need to check, and change 29 to the required number of days.
Confirm that matching project rows appear in the summary area and that the fallback message shows when no rows match.
Use FILTER with a regular range and multiple criteria
Use this when the data is not formatted as an Excel Table, or when you need to combine several conditions in one formula.
Use INDEX, AGGREGATE, or Power Query when FILTER is not available
Use this in older Excel versions that do not include FILTER, or when you prefer a non-dynamic summary process.
Create a Dynamic FILTER Summary Report in WPS Spreadsheets
WPS Spreadsheets lets you use the FILTER function to build a live project summary report without copying data. Enter the formula against a table or range, set the overdue condition, and WPS returns all matching rows automatically.
- 1. Open WPS Spreadsheets: Download and launch WPS Office, then open the Excel workbook in WPS Spreadsheets.
- 2. Select the summary sheet: Click the cell where the filtered summary should begin, such as Summary!A1.
- 3. Enter the FILTER formula: Type =FILTER(ProjectTable,ProjectTable[Days Overdue]>=29,"No matching projects") and press Enter.
- 4. Save the workbook: Save the file as .xlsx to keep the formula and continue sharing it with Excel users.
Frequently Asked Questions
Does FILTER work in all versions of Excel?
No. FILTER requires Excel 2021 or Microsoft 365. In older Excel versions, use INDEX and AGGREGATE formulas or Power Query instead.
Can I return only some columns with FILTER?
Yes. Set the array argument to the specific columns you need, for example =FILTER(ProjectTable[[Project]:[Owner]],ProjectTable[Days Overdue]>=29,"No matching projects").
What does the third argument in FILTER do?
The third argument is the value returned when no rows meet the condition. In the example, "No matching projects" appears instead of an error.
Can I use FILTER if my data is not an Excel Table?
Yes. Use a range reference such as A2:F100 in the formula, but make sure all range arguments cover the same number of rows.
Does WPS Spreadsheets support FILTER formulas with .xlsx files?
Yes. WPS Spreadsheets supports FILTER and can save the workbook in .xlsx format, keeping it compatible with Excel.
