How to Filter Full Rows to Another Excel Sheet with a Drop-Down List
Question details
The user wants to dynamically extract and display entire rows from a main data sheet to a separate sheet based on a category selected from a drop-down menu.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Building an interactive report or dashboard where selecting an item from a drop-down automatically fetches all matching records from a source dataset.
- Observed behavior
- The filtered results need to update automatically when new records are added to the source data, without manually refreshing or re-applying static filters.
Ensure your spreadsheet software supports dynamic array functions like FILTER. Converting your source data range into a formal Table is highly recommended so that new data entries are automatically included in your filtered results.
Use the FILTER Function with Data Validation
This method uses the dynamic FILTER array formula combined with a drop-down list to instantly pull matching rows. It is the most efficient and automated way to solve this problem.
By turning your source data into a Table, you ensure that the formula references expand dynamically. The FILTER function will then look at the entire table and output every row that matches the criteria you specify in the drop-down cell.
Highlight your entire source data range. Navigate to the Insert tab and click 'Table', or press the shortcut Ctrl+T. In the Table Design tab, rename this table to 'Table1' (or another easily recognizable name).
Go to the destination sheet where you want the filtered data to appear. Select a cell (for example, G1), navigate to the Data tab, and click 'Data Validation'. Under the 'Allow' drop-down, choose 'List', and select or type the categories you want to filter by.
Click on the top-left cell where the filtered results should begin. Enter the formula: =FILTER(Table1, Table1[Category]=G1). Replace 'Category' with the actual column header you are filtering against.
If you want the filtered rows to be organized by a specific column, wrap your formula in the SORT function. For example, to sort by the third column in ascending order, use: =SORT(FILTER(Table1, Table1[Category]=G1), 3).
Easily Filter and Manage Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and SORT, allowing you to build interactive, auto-updating reports effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the source data.
- 2. Format Data as Table: Highlight your data, go to the 'Insert' tab, and click 'Table' to enable dynamic data ranges.
- 3. Insert Drop-Down Menu: Select your target cell in the new sheet, navigate to 'Data' > 'Validation', and choose 'List' to set up your categories.
- 4. Input the Formula: Type =FILTER(Table_Name, Table_Name[Column]=Drop_Down_Cell) and press Enter to instantly display your matching rows.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error usually appears when the FILTER function finds zero matching records for the selected drop-down category. You can handle this gracefully by adding the optional 'if_empty' argument to your formula, like this: =FILTER(Table1, Table1[Category]=G1, "No records found").
Can I filter full rows based on multiple drop-down selections?
Yes, you can filter based on multiple conditions by using the asterisk (*) symbol, which acts as an AND operator. For example, if you have a second drop-down in cell H1 for 'Status', you would use: =FILTER(Table1, (Table1[Category]=G1)*(Table1[Status]=H1)).
Does the FILTER function work in older versions of Excel?
The FILTER function is a dynamic array formula that is only available in Microsoft 365, Excel 2021, and modern alternatives like WPS Office. If you are using Excel 2019 or older, you will need to rely on Advanced Filter, Pivot Tables, or complex INDEX/MATCH array formulas.
