Function Problems

Remove Duplicates from Dependent Drop-Down Lists in Excel

Natalie TaylorNatalie Taylor Nov 14, 2026 868 views

Question details

Create dependent Excel drop-down lists that show unique values while preserving relationships between five data columns.

Remove Duplicates from Dependent Drop-Down Lists in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the Scope list

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.

2
Create the Level 1 list

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.

3
Create the remaining dependent lists

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<>""))),"")).

4
Name the spilled ranges

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#.

5
Add the drop-downs

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.

Build unique dependent lists with FILTER and UNIQUE
Clear outdated selections: Changing a parent selection does not automatically clear existing child selections. Clear the cells to its right and select new values so the displayed choices match the current hierarchy.
Work with dependent lists in WPS

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. 1. Open the workbook: In WPS Spreadsheets, open the XLSX file and check that each source row contains the complete hierarchy.
  2. 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. 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. 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.
Generate unique choices for each level of a dependent list.Open and edit Microsoft Excel XLSX workbooks.Use a familiar spreadsheet interface for formulas and data validation.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.