Create a Searchable Drop-Down List Without Duplicates in Excel
Question details
The user wants to create dynamic, searchable drop-down lists that automatically exclude items that have already been selected or are blank.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Setting up a worksheet for team selections where each team can only be chosen once across multiple drop-downs without showing blank or duplicate options.
- Observed behavior
- Standard drop-down lists display all options regardless of prior selections and natively lack built-in searchable text typing capabilities in older versions.
Ensure your spreadsheet application supports dynamic array formulas, such as FILTER and XMATCH, which are required for generating the dynamic helper columns used in this method.
Use Dynamic Array Formulas and Data Validation
Generate a dynamic spill range using the FILTER and XMATCH functions to provide a list of available items that updates automatically when a selection is made.
This method utilizes a temporary helper column to filter out options that have already been selected. As you pick an item from the drop-down, the formula recalculates and removes that item from the available list.
Prepare your master list of options. It is recommended to format it as a Table (e.g., named 'Data' with a column header 'info') so the formulas can reference it dynamically.
In an empty cell (for example, O9), enter the formula: =FILTER(Data[info],(Data[info]<>"")*ISNA(XMATCH(Data[info],J9:M9)),""). In this formula, J9:M9 represents the cells where users will make their selections.
Highlight the cells where you want the drop-down lists to appear (e.g., J9:M9).
Go to the Data tab on the ribbon and click 'Data Validation'. Choose 'List' from the Allow drop-down menu.
In the Source field, enter the reference to your helper cell followed by a hash '#' (e.g., =$O$9#). Click OK to apply the drop-down lists.
Effortlessly Manage Dynamic Data with WPS Spreadsheet
WPS Spreadsheet provides robust support for dynamic array functions like FILTER and XMATCH, empowering you to build advanced data validation rules and dynamic drop-down lists seamlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your master list.
- 2. Create a helper column: Use the FILTER function in an empty cell to dynamically extract available options that haven't been selected yet.
- 3. Apply Data Validation: Navigate to Data > Validation, select 'List', and reference your dynamic spill range (e.g., =$O$9#).
Frequently Asked Questions
Why doesn't my drop-down list update when I make a new selection?
Ensure your calculation options are set to 'Automatic' in the Formulas tab. Also, verify that your Data Validation source correctly references the spill range using the '#' symbol at the end of the cell reference.
What does the XMATCH function do in this data validation formula?
XMATCH looks for each item from the master list inside your selected range. When combined with ISNA, it identifies which items have not yet been selected, allowing the FILTER function to display only the remaining available options.
Can I set this up for multiple independent selection groups?
Yes. You will need to create a separate helper column with the FILTER formula for each independent group, adjusting the selection range reference (like J9:M9) to match the input cells for that specific group.
How can I make the drop-down searchable in older versions of Excel?
In older versions, data validation lists are not natively searchable by typing. You need to insert an ActiveX ComboBox from the Developer tab and use VBA code to filter the list options dynamically as you type.
