How to hide previously used items in drop-down list?
In Excel, creating a standard dropdown list is a common data entry technique, but what if you want the dropdown list to become shorter each time you make a selection—removing previously chosen items so that each option can only be picked once? For instance, imagine you have a dropdown list with 100 unique names: after you select one name, it is removed from the dropdown choices, leaving 99 options. As you continue making selections, the list automatically shrinks until no options remain. This level of interactivity can be quite practical in scenarios such as assigning tasks without duplication, seating arrangements, or lottery draws where every choice should be unique. However, Excel does not offer this functionality directly, so you need to employ specific workarounds. In the following sections, you'll find step-by-step instructions on how to achieve this.
Hide previously used items in drop-down list with helper columns
Hide previously used items in drop-down list with helper columns
Suppose you have a list of names in Column A, as shown in the screenshot below. To set up a dropdown list that hides previously used items, proceed step by step as described. This approach uses extra helper columns to track which items have already been selected and to build the dynamic dropdown source list. Although this method may seem elaborate, it is straightforward and does not require programming skills.
Applicable scenarios include scheduling, resource assignment, or any case where an item should only be chosen once until the list is exhausted. The advantage is clarity through visible formulas and traceable logic; however, it does require maintaining additional columns on your worksheet.
1. Next to your list of names, in cell B1, enter the following formula to check if a name has already been picked in the target dropdown range:
=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())This formula compares each name against the selections made in the dropdown (range F1:F11). If the name has already been chosen, it returns a blank cell; otherwise, it returns the row number as a helper value. Be sure to adjust the F1:F11 range to match the location where you intend to place your dropdown lists, and the A1 reference to the location of your name list.
Note: Double-check that the 'F1:F11' range encompasses all dropdown cells. The 'A1' should point to the current row in your list of names.
2. Drag the fill handle down to apply this formula to all rows in your name list. This will create a series of helper results identifying unused names.
3. In Column C, set up another helper formula in cell C1 to dynamically build a clean list of only the unused names:
=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1))))This formula collects all unused names together by extracting the appropriate items from Column A based on the helper values in Column B. As names are chosen and removed from B, this list in Column C updates automatically. If your list is longer than 11 names, make sure to adjust all ranges accordingly.
4. Copy this formula down to match the length of your original name list. The range you fill should be as long as your list in Column A.
5. To make this dynamically updated list usable for your dropdown, define a named range. Select the newly created list in Column C (for example, C1:C11), then click Formulas > Define Name.
6. In the New Name dialog box, enter a name (e.g., namecheck), and use this dynamic reference formula to keep the named range correctly sized as names are selected:
=OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1)This ensures that only non-blank values in Column C are used for the dropdown options. Carefully check your sheet name and cell references—using exact address formats—to match your own worksheet.
Note: If you change the name list, add or remove rows, or use a different worksheet, make sure to update the formula accordingly to prevent errors.
7. Now, to create the actual dropdown list, select the cells where you want users to make their selections (e.g., F1:F11). Go to Data > Data Validation > Data Validation.
8. In the Data Validation dialog, under the Settings tab, choose List and type =namecheck into the Source field, referencing the dynamic named range you defined.
Click OK to finish. Each time a name is picked in the dropdown, it is omitted from the list for the other dropdowns, ensuring all choices are unique. If you attempt to pick the same name in another cell, you’ll find it is no longer available as an option.
Tip: Do not delete or overwrite any of the helper columns (Columns B and C), as they are essential for the dropdown list to update correctly. Consider hiding these columns if you want to keep your worksheet tidy without disrupting functionality. If you encounter issues with list updates, check formulas for range mismatches, or ensure that all data validation links are correct and refer to the intended named range.
A limitation of this approach is that if many users will be making selections at the same time (for example, on a shared worksheet), conflicts can still arise. For more advanced, scalable solutions or to automate this task with less visible worksheet clutter, consider using VBA—the following section demonstrates this alternative.
Related articles:
How to insert drop down list in Excel?
How to create drop down list with images in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in