Function Problems

Create Excel Drop-Down Lists That Prevent Duplicate Selections in WPS Spreadsheets

Olivia Miller Nov 13, 2026 870 views

Question details

The user needs to create Excel-style drop-down lists that prevent duplicate selections by removing values already chosen in earlier columns, using Data Validation and dynamic array formulas such as FILTER, without VBA.

Create Excel Drop-Down Lists That Prevent Duplicate Selections in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: Building a worksheet in which each row must select unique values from a master list across multiple drop-down columns.
Observed behavior: The desired behavior is that each subsequent drop-down list automatically excludes values already selected in the same row, so duplicate entries cannot be made.

Before you start

Create the master list of allowed values in a spare range, such as A2:A20 on a separate sheet, and confirm that your WPS Spreadsheets version supports dynamic array functions like FILTER. Keep the target drop-down cells in a continuous row range so relative references in the formula work correctly.

Solution 1Recommended

Create a dynamic list with FILTER and COUNTIF to remove selected values

Use this method when your version of WPS Spreadsheets supports dynamic array formulas; it creates a spill range that automatically updates the Data Validation source as values are selected.

The FILTER function returns only master-list values whose count in the current selection row is zero. The spill range then feeds the drop-down list, so duplicates are blocked without VBA.

You only need to maintain one master list and one filtered spill range per row or helper column.

Create the master list

Enter the valid choices in a spare range, for example A2:A20 on a sheet named Lists.

Enter the FILTER formula

In a blank cell such as H2 on the data sheet, type =FILTER(Lists!$A$2:$A$20,COUNTIF($B2:$F2,Lists!$A$2:$A$20)=0). This returns only the values not already selected in B2:F2.

Apply Data Validation to the drop-down cell

Select the cell that should contain the drop-down list, go to the Data tab, click Data Validation, choose List, and enter the spill reference =$H$2# as the source.

Expand the setup to additional rows

Copy the formula down or adjust relative references so each row filters against its own selected values, then point each row's Data Validation source to its own spill cell.

Create a dynamic list with FILTER and COUNTIF to remove selected values

Duplicate selections are now blocked: When you choose a value in the row, the FILTER spill range refreshes and the drop-down list no longer shows that value for the remaining columns.

Create Duplicate-Proof Drop-Down Lists in WPS Spreadsheets

Use WPS Spreadsheets' Data Validation and supported dynamic array formulas to build a drop-down list that automatically removes already selected values, preventing duplicate entries without VBA.

1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook where you need duplicate-proof drop-down lists, and enable editing if prompted.
2. Create the master list and enter the FILTER formula: Place the valid values in a range such as A2:A20, then in a blank cell enter =FILTER($A$2:$A$20,COUNTIF($B2:$F2,$A$2:$A$20)=0) to generate the filtered list.
3. Set up Data Validation: Select the drop-down cell, go to the Data tab, click Data Validation, choose List, and enter the spill reference such as =$H$2# in the Source box.
4. Test the drop-down behavior: Choose a value in the first drop-down, then open the next drop-down to confirm the selected value is no longer listed.

Prevents duplicate selections across multiple drop-down columns in the same rowFully compatible with Excel .xlsx workbooks and Excel data validation sourcesNo VBA or macros required, making the workbook easier to maintainFree to use in WPS Office with a familiar Excel-like interface

microsoft office alternative - wps office

Frequently Asked Questions

Does this duplicate-prevention drop-down method work in WPS Spreadsheets?

Yes. WPS Spreadsheets supports Data Validation lists and many dynamic array formulas. If FILTER is not available in your version, update WPS Office or use helper-column formulas to create the filtered list.

Why does my FILTER formula return #SPILL! in WPS Spreadsheets?

The spill range is likely blocked by existing data. Clear all cells below and to the right of the formula, or move the formula to an empty area so WPS can output the full filtered array.

Can I prevent duplicates without using VBA or macros?

Yes. This method uses only Data Validation and formulas such as FILTER and COUNTIF, so you do not need any VBA code or macros.

How do I apply the filtered list to multiple rows?

Make the row references in the formula relative, or create one filtered spill range per row, then point each row's Data Validation source to its own spill reference using the # operator.

Can I make the next drop-down stay blank until the previous value is selected?

Yes. You can build the validation source with an IF condition that returns no list until the previous cell is filled, for example =IF($B2="","",$H$2#). Test it in your WPS version, because dynamic-range behavior can vary by release.

Excel & Data Problems