How to Extract Not-Done Tasks to an Excel Dashboard Using FILTER
Question details
The user wants to automatically extract rows marked as 'Not done' from a source table and display them on a separate dashboard worksheet without manual filtering.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a task tracking dashboard that requires a dynamically updating list of incomplete tasks pulled from a master dataset.
- Observed behavior
- The goal state is to implement a dynamic formula that pulls specific rows based on their status and handles empty results gracefully if all tasks are complete.
Ensure your source task list is formatted as an official Excel Table (Insert > Table) and identify the exact names of your table and the status column.
Use the FILTER Function to Extract Incomplete Tasks
The FILTER function is the most efficient dynamic array method to extract and display rows meeting specific criteria across different worksheets.
By utilizing the FILTER function, you can create a dynamic spill range. This means that as soon as you update a task's status in the source table, the dashboard will automatically update without needing a manual refresh.
Open your Excel workbook and click on the dashboard sheet where you want the 'Not done' tasks to be displayed. Select the top-left cell of the intended output area.
Type the formula: =FILTER(TableName, TableName[Complete / Not done]="Not done", ""). Replace 'TableName' with the actual name of your source table, and ensure the column header matches exactly.
Press Enter. The formula will extract all rows matching the 'Not done' criteria and automatically spill the data into the adjacent cells below and to the right.
Create Dynamic Dashboards with WPS Spreadsheet
WPS Spreadsheet supports powerful dynamic array functions like FILTER, allowing you to easily build automated dashboards and track incomplete tasks without any manual effort.
- 1. Format Your Data as a Table: Select your raw task data in WPS Spreadsheet and press Ctrl+T to format it as a Table.
- 2. Select the Target Cell: Switch to your dashboard worksheet and click on the cell where the extracted task list should start.
- 3. Input the FILTER Formula: Type =FILTER(TableName, TableName[Status]="Not done", "") and press Enter.
- 4. Watch It Update Dynamically: As you change task statuses to 'Complete' in the source table, watch the dashboard list shrink and update automatically.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
A #CALC! error typically occurs when the FILTER function finds no matching results (e.g., when all tasks are done) and the 'if_empty' argument is missing. To fix this, add ",""" at the end of your formula: =FILTER(Table, Criteria, "").
Can I extract tasks based on multiple conditions, like 'Not done' AND 'High Priority'?
Yes, you can use the asterisk (*) to represent AND logic. For example, use the formula: =FILTER(TableName, (TableName[Status]="Not done") * (TableName[Priority]="High"), "") to extract rows that meet both criteria.
Will the extracted task list update if I add new tasks to the bottom of the source data?
Yes, as long as your source data is formatted as an official Table (e.g., Table1). Tables expand automatically when new rows are added, and the FILTER formula referencing the table will immediately include the new data on your dashboard.
