How to Extract Excel Table Data to a Dashboard with FILTER in WPS Office Spreadsheets
Question details
The user needs a formula to dynamically return company names from an Excel table into a dashboard whenever a selected category column contains Yes, without manually filtering the source table.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Create a dashboard that updates company names automatically based on the category header selected in a cell.
- Observed behavior
- The dashboard should return only the matching company names and update dynamically when the selected category changes.
Confirm the source worksheet name, header row, and data range before entering the formula. If your workbook does not use a sheet named 'Data Sheet' or columns B:P for categories, adjust the formula references to match the actual layout.
Extract matching company names using FILTER, INDEX, and MATCH
Use this formula when you want a drop-down-driven dashboard that updates automatically and returns company names from column A for any selected category column containing Yes.
This formula combines MATCH to locate the selected category column, INDEX to return that column as the FILTER criteria array, and FILTER to output the company names from column A where the selected column equals Yes. The result spills dynamically into empty cells.
Launch WPS Office Spreadsheets, open the workbook, and click the dashboard worksheet where you want the dynamic company list to appear.
Click an empty cell and enter: =FILTER('Data Sheet'!A:A,INDEX('Data Sheet'!B:P,,MATCH(A1,'Data Sheet'!B1:P1,0))="Yes","")
Press Enter to confirm the formula. WPS Spreadsheets returns the matching company names in a spilled range below the formula cell.
Create a Dynamic Dashboard with FILTER in WPS Office Spreadsheets
Build the dashboard directly in WPS Office Spreadsheets using MATCH, INDEX, and FILTER. Open the workbook, add a drop-down for the selected category header, enter the formula, and WPS will spill only the matching company names without changing the original Excel table.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook that contains the Data Sheet and the dashboard worksheet.
- 2. Set up a drop-down selection cell: On the dashboard, click cell A1, go to Data > Data Validation, and set the list source to 'Data Sheet'!B1:P1 so A1 can select any category header.
- 3. Enter the dynamic FILTER formula: Select an empty result cell and enter =FILTER('Data Sheet'!A:A,INDEX('Data Sheet'!B:P,,MATCH(A1,'Data Sheet'!B1:P1,0))="Yes",""), then press Enter.
- 4. Test the dashboard output: Change the drop-down selection in A1 and confirm the company list updates automatically in the spill range.
Frequently Asked Questions
How does MATCH find the correct category column in this formula?
MATCH(A1,'Data Sheet'!B1:P1,0) looks for the header selected in cell A1 within the header row B1:P1 on the Data Sheet and returns its relative column position. INDEX then uses that position to return the full category column for FILTER.
Can I use structured table references instead of full column ranges?
Yes, convert the source data to a structured Excel table and use the table name and column headers. Structured references can improve performance and make the formula easier to read when the data range changes.
Why does the formula return an empty string when no rows match Yes?
The final "" argument in FILTER is the if_empty value. If no rows in the selected category column contain Yes, FILTER returns an empty string instead of showing a #CALC! error.
Do I have to use a drop-down in cell A1?
No, you can type a header directly into A1. A drop-down created with Data Validation helps ensure the selection exactly matches a header in the source table and avoids formula mismatches.
What should I adjust if my category columns are not exactly B:P?
Update the formula ranges to match your workbook layout. Change B:P to the actual category column range and B1:P1 to the actual header row, and adjust the return column A:A if company names are stored elsewhere.
