Function Problems

How to Copy Excel Rows to Separate Sheets Based on a Drop-Down Value

Emma Brown Oct 16, 2026 870 views

Question details

The user needs to automatically distribute rows of data into separate worksheets based on a selection from a drop-down validation list without deleting the original data.

How to Copy Excel Rows to Separate Sheets Based on a Drop-Down Value

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing a master report where data entries are categorized by a drop-down list (e.g., YES or NO) and need to be viewed on separate dedicated sheets.
Observed behavior: The user wants rows to be copied dynamically to the YES or NO worksheets whenever the corresponding drop-down value is selected in the master sheet.

Before you start

Ensure your master data is organized in a clear tabular format, and note the exact sheet name and column letter that contains your drop-down validation list.

Solution 1Recommended

Extract Rows Dynamically Using the FILTER Function

The most efficient way to pull rows into separate sheets based on a specific value is by using the dynamic array FILTER function available in modern spreadsheet software.

The FILTER function automatically updates the extracted data whenever the original drop-down selection changes, meaning you do not need to manually copy and paste rows.

Create Target Worksheets

Add two new worksheets to your workbook and name them appropriately, such as 'YES' and 'NO'.

Apply the FILTER Formula in the First Sheet

Navigate to cell A2 in the 'YES' worksheet and enter the formula: =FILTER(DATA!A2:Z10000, DATA!F2:F10000="YES", ""). Replace 'DATA' with your actual source sheet name and column 'F' with the column containing your drop-down list.

Apply the Formula to the Second Sheet

Go to the 'NO' worksheet, select cell A2, and enter the same formula but change the criteria: =FILTER(DATA!A2:Z10000, DATA!F2:F10000="NO", "").

Format and Adjust the Range

Adjust the column range (A2:Z10000) based on how many columns your original table has. The extracted rows will populate automatically.

Extract Rows Dynamically Using the FILTER Function

Automatic Updates: Because this method uses formulas instead of a macro, the separate sheets will update instantly whenever you change a drop-down value on the main data sheet.

Solution 2

Use INDEX, SMALL, and IF for Legacy Compatibility

If you are using an older spreadsheet version that does not support the FILTER function, you can extract rows using a complex array formula.

Efficient Data Management

Organize Your Data Quickly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, allowing you to easily separate data into different worksheets based on drop-down selections without complex macros.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your master data workbook.
2. Create your category sheets: Use the '+' icon at the bottom to add your separate sheets (e.g., YES and NO).
3. Use the FILTER function: Type the =FILTER() formula into the target sheets to instantly extract the corresponding rows.

Fully compatible with Microsoft Excel .xlsx formats and functions.Supports modern dynamic array formulas like FILTER out of the box.Lightweight, fast, and completely free for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Do I need to use VBA to copy rows based on a drop-down list?

No, VBA is not required. You can use dynamic array formulas like FILTER to automatically display the corresponding rows on different sheets without needing to write or run macros.

Will the separated sheets update automatically when the master sheet changes?

Yes. Because the extraction relies on dynamic formulas, any changes made to the drop-down values in the original data sheet will instantly reflect in the separated worksheets.

What does the empty string ("") at the end of the FILTER formula mean?

The "" at the end of the =FILTER(...) formula is the 'if_empty' argument. It tells the spreadsheet to return a blank cell instead of a calculation error if no rows match the specified YES or NO criteria.

Excel & Data Problems