How to Automatically Update Multiple Excel Tables with Drop-Down Lists
Question details
Dynamically populate and update multiple separate tables based on a drop-down list selection from a single source dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an interactive dashboard or report where changing a central drop-down menu automatically updates associated tables categorized by different sections.
- Observed behavior
- The goal is to use dynamic array formulas to automatically filter and spill data into designated section tables without manual copying, pasting, or refreshing.
Ensure your main dataset is formatted as an official Excel Table (e.g., named 'DataTable') and that your spreadsheet software supports dynamic array functions like FILTER.
Use Data Validation and the FILTER Function
Create a drop-down list for user selection and use the FILTER dynamic array formula with multiple criteria to automatically populate each section's table.
By combining Data Validation with the dynamic FILTER function, you can create interactive tables that instantly update when a new category is selected. Multiplying conditions inside the FILTER function allows you to extract data matching both the selected service and a specific section number.
Select your source dataset (including columns like Department, Section, Service, and Expenses), navigate to Insert > Table, and check 'My table has headers'. Under the Table Design tab, name the table 'DataTable'.
Click on the cell where you want the drop-down list (e.g., D1). Go to Data > Data Validation. Under the Settings tab, select 'List' from the Allow drop-down. In the Source box, type your service options (e.g., 'A,B') or highlight a range containing the options, then click OK.
Select the top-left empty cell where your Section 1 table should begin. Enter the formula: =FILTER(DataTable,(DataTable[Service]=D1)*(DataTable[Section]=1),"No matches"). Press Enter to let the data spill automatically.
For your Section 2 table, click its starting cell and enter the same formula, modifying the section condition: =FILTER(DataTable,(DataTable[Service]=D1)*(DataTable[Section]=2),"No matches"). Repeat this process for Section 3 by changing the number to 3.
Automate Your Data Tables in WPS Spreadsheet
WPS Spreadsheet fully supports advanced data validation and dynamic array functions like FILTER. You can easily build automated reports and interconnected tables in a fast, lightweight application.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the master dataset.
- 2. Set up Data Validation: Navigate to the Data tab, click Data Validation, and set up your interactive drop-down list to act as the primary control.
- 3. Apply dynamic formulas: Type your =FILTER(...) formulas into the target areas to instantly filter and distribute the data across multiple tables based on your drop-down selection.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the FILTER function?
This error occurs when the dynamic array formula doesn't have enough empty adjacent cells to display all the filtered results. To fix this, simply clear any text, spaces, or data in the cells directly below and to the right of your formula.
Can I filter data based on more than two criteria?
Yes. You can add more conditions by wrapping each in parentheses and multiplying them together inside the FILTER function. For example: =FILTER(DataTable, (DataTable[Service]=D1)*(DataTable[Section]=1)*(DataTable[Department]="Sales")).
How can I make the drop-down list update automatically when new options are added?
Instead of typing manual, comma-separated values in the Data Validation Source box, reference a dynamic named range or an array on a separate reference sheet. When new items are added to that specific range, the drop-down list will update automatically.
