Remove Duplicates from Dependent Drop-Down Lists in Excel
Question details
Create dependent Excel drop-down lists that show unique values while preserving relationships between five data columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A large dataset contains Scope and Level 1 through Level 4 values, and each drop-down should depend on the preceding selections.
- Observed behavior
- The user needs each drop-down to show distinct values that match the selected values in all preceding columns.
Check that each source row contains the correct Scope-to-Level 4 relationship. The formula method below requires an Excel version that supports UNIQUE, FILTER, and spilled arrays, such as Microsoft 365 or Excel 2021.
Build unique dependent lists with FILTER and UNIQUE
Use helper ranges to generate distinct choices for each selection while retaining the relationships in the source rows.
This example assumes the source data is on a sheet named Data, with Scope and Levels 1–4 in columns A–E, rows 2–10000. The selection cells are Form!B2:F2. Adjust the sheet names and ranges to match your workbook.
The helper formulas spill down columns H–L on the Form sheet. Keep those columns clear below row 2, and extend the source ranges if your dataset has more than 9,999 data rows.
On the Form sheet, enter =IFERROR(UNIQUE(FILTER(Data!$A$2:$A$10000,Data!$A$2:$A$10000<>"")),"") in H2. This returns distinct, nonblank Scope values.
In Form!I2, enter =IF($B$2="","",IFERROR(UNIQUE(FILTER(Data!$B$2:$B$10000,(Data!$A$2:$A$10000=$B$2)*(Data!$B$2:$B$10000<>""))),"")). It returns unique Level 1 values for the Scope selected in B2.
Enter these formulas on Form: J2: =IF(OR($B$2="",$C$2=""),"",IFERROR(UNIQUE(FILTER(Data!$C$2:$C$10000,(Data!$A$2:$A$10000=$B$2)*(Data!$B$2:$B$10000=$C$2)*(Data!$C$2:$C$10000<>""))),"")); K2: =IF(OR($B$2="",$C$2="",$D$2=""),"",IFERROR(UNIQUE(FILTER(Data!$D$2:$D$10000,(Data!$A$2:$A$10000=$B$2)*(Data!$B$2:$B$10000=$C$2)*(Data!$C$2:$C$10000=$D$2)*(Data!$D$2:$D$10000<>""))),"")); L2: =IF(OR($B$2="",$C$2="",$D$2="",$E$2=""),"",IFERROR(UNIQUE(FILTER(Data!$E$2:$E$10000,(Data!$A$2:$A$10000=$B$2)*(Data!$B$2:$B$10000=$C$2)*(Data!$C$2:$C$10000=$D$2)*(Data!$D$2:$D$10000=$E$2)*(Data!$E$2:$E$10000<>""))),"")).
Open Formulas > Name Manager > New and create workbook-level names ScopeList, Level1List, Level2List, Level3List, and Level4List. Set their respective Refers to values to =Form!$H$2#, =Form!$I$2#, =Form!$J$2#, =Form!$K$2#, and =Form!$L$2#.
Select Form!B2, open Data > Data Validation, choose List, and set Source to =ScopeList. Repeat for C2:F2 using =Level1List, =Level2List, =Level3List, and =Level4List respectively. Test the lists by selecting values from left to right.

Create unique dependent lists in WPS Spreadsheets
Use helper ranges and data validation in WPS Spreadsheets to organize unique choices for a hierarchy. Check that your WPS version supports the dynamic-array formulas and spilled-range references used below.
- 1. Open the workbook: In WPS Spreadsheets, open the XLSX file and check that each source row contains the complete hierarchy.
- 2. Generate unique choices: In empty helper columns, enter UNIQUE and FILTER formulas that match each level against all preceding selections. Confirm that the results spill into the cells below.
- 3. Name the helper ranges: Use the name-management controls on the Formulas tab to name each spilled range, if your WPS version supports spilled-range references.
- 4. Create and test the lists: Select each input cell, open Data > Data Validation, choose a list, and use the corresponding named range as its source. Test each level and clear child cells after changing a parent.

Frequently Asked Questions
Why isn't OFFSET enough to remove duplicates?
OFFSET returns a range but does not itself extract distinct values or preserve the full hierarchy. Generate unique values from rows matching all parent selections, then use those results as the validation source.
How do I preserve links between Scope and every level?
Filter the original rows using every preceding selection. For example, the Level 3 list should match Scope, Level 1, and Level 2 before UNIQUE extracts the Level 3 values.
Why does a previous selection remain after I change its parent?
Excel data validation does not automatically erase a cell when its source list changes. Clear the dependent cells to the right and select valid values again.
What if my Excel version does not have FILTER or UNIQUE?
Create separate unique lists for each parent combination, for example by filtering copied source data and using Data > Remove Duplicates. Use those lists as data-validation sources; the setup requires more manual maintenance.
Why does a helper formula show a spill error?
Check the cells below the formula for existing content and clear anything blocking the spill range. Also ensure the helper formula is outside an Excel table, where spilled arrays cannot expand.




