ASM Arman
While working with data validation in Excel, you might need to remove used items from the drop-down list to avoid assigning an item in the list twice. For example, you might have to assign multiple employees to different working shifts and you do not want to assign an employee more than once. Another scenario might be that you are assigning players to different positions in a score game and you have to assign a player to a specific position. In such cases, if you have a drop-down list to assign employees to work shifts or players to different positions, you might want to remove the name of the employee or the player from the drop-down list once he/she has been assigned. In this tutorial, I will show you how to remove used items from drop down list in Excel.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
2 Easy Ways to Remove Used Items from Drop Down List in Excel
Let’s assume a scenario where we have an Excel worksheet with the name of the employees of an organization. You need to assign each of these employees to a different working shift and you do not want to assign an employee more than once. So, you need a drop-down list with the name of the employees that will automatically remove an employee once he/she has been assigned to a task. I will use this worksheet to show you 2 easy ways to remove used items from the drop-down list. The image below shows the worksheet we are going to work with that has the drop-down list with removed used items.
Method 1: Use Helper Columns to Remove Used Items from Drop Down List in Excel
An easy way to remove used items from the drop-down list is to use two helper columns. Let’s see how we can do that.
Step 1:
- First, write the following formula in cell C5 under the Row Number
=IF(COUNTIF($F$5:$F$14,B5)>=1,"",ROW())
Formula Breakdown:
- The IF function will run the logical test COUNTIF($F$5:$F$14, B5)>=1.
- The COUNTIF function will find out if cell B5 appears in the absolute range $F$5:$F$14 more than once.
- If cell B5 appears once or more in the absolute range $F$5:$F$14, the IF function will return an empty string (“”).
- Otherwise, the IF function will return the row number of cell B5 using the ROW.
- Then, upon pressing ENTER, we will find out the row number of cell B5 in cell C5.
- Now, we will drag the fill-handle of cell C5 downward to apply the formula to the rest of the cells in the Row Number.
- Finally, we will now get all the rows numbers of cells of the Employee.
Step 2:
- Next, write the following formula in cell D5 under the Employee Name.
=IF(ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14),"",INDEX(B:B,SMALL(C$5:C$14,1+ROW(B5)-ROW(B$5))))
Formula Breakdown:
- The IF function will run the logical test ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14).
- The COUNT function will count the number of cells in the absolute range C$5:C$14.
- The SMALL function will find out the kth smallest value in the absolute range C$5:C$14. Here, k will be determined by 1+ROW(B5)-ROW(B$5).
- The INDEX function will take the kth smallest value in the absolute range C$5:C$14 determined by the SMALL function as an only argument(row_num) and return references of the cells.
- Then, upon pressing ENTER, we will get the employee name of cell B5 in cell D5.
- Now, we will drag the fill-handle of cell D5 downward to apply the formula to the rest of the cells in the Employee Name.
- Finally, we will now get all the employee names in the Employee column.
Step 3:
- Next, we will click on the Define Name under the Formulas.
- Now, a new window titled Edit Name will appear. We will insert Employee in the Name input box.
- Then, we will insert the below formula in the Refers to the input box.
=Helper!$B$4:$D$14=OFFSET(Helper!$D$5,0,0, COUNTA(Helper!$D$5:$D$14)-COUNTBLANK(Helper!$D$5:$D$14),1)Formula Breakdown:
- Helper is the name of the worksheet we are working on.
- The COUNTA function will count all the cell values in the absolute range $D$5:$D$14.
- The COUNTBLANK function will count the number of empty cells in the absolute range $D$5:$D$14.
- After that, we will click on OK.
Step 4:
- Next, we will select all the cells in the Drop-Down column to create a drop-down list.
- Now, we will click on the Data Validation drop-down under the Data.
- Then, we will select the Data Validation from the drop-down.
- Now, a new window titled Data Validation will appear. Then, we will select List from the Allow drop-down menu.
- Then, we will insert =Employee in the Source input box.
- After that, we will click on OK.
- Finally, we will see drop-down lists in each cell of the Drop-Down.
- Now, we will select the name Gus Fring from the drop-down list in cell F5.
- Now, if we click on the second drop-down, we will see that the name Gus Fring is not included in this drop-down list. As we have already used this item, so it will be removed from the following drop-down lists.
- Next, if we select names from other drop-down lists, we will see that the selected items or names will be removed from the following drop-down lists.
Read More: How to Create Drop Down List in Multiple Columns in Excel (3 Ways)
Similar Readings:
- How to Create Multi Select Listbox in Excel
- Excel Drop Down List Depending on Selection
- How to Link a Cell Value with a Drop Down List in Excel (5 Ways)
- Conditional Drop Down List in Excel (Create, Sort and Use)
- How to Create Dynamic Dependent Drop Down List in Excel
Method 2: Remove Used Items from Drop Down List in Excel Combining the FILTER and COUNTIF Functions
If you have access to Microsoft Office 365, the easiest way then will be to use the FILTER function exclusive to Excel 365 to remove used items from the drop-down list. We have to follow the below steps.
Step 1:
- First, write the following formula in cell C5 under the Row Number
=FILTER(B5:B14, COUNTIF(E5:E14,B5:B14)=0)
- Then, upon pressing ENTER, we will now get all the employee names of the Employee column.
Step 2:
- Next, we will select all the cells in the Drop-Down column to create a drop-down list.
- Now, we will click on the Data Validation drop-down under the Data.
- Then, we will select the Data Validation from the drop-down.
- Now, a new window titled Data Validation will appear. We will select List from the Allow drop-down menu.
- Then, we will insert $C$5:$C$14 in the Source input box. Alternatively, you can also insert =$C$5# in the Source input box.
- After that, we will click on OK.
- Finally, we will see drop-down lists in each cell of the Drop-Down.
- Now, we will select the name Stuart Bloom from the drop-down list in cell F5.
- Now, if we click on the second drop-down, we will see that the name Stuart Bloom is not included in this drop-down list. As we have already used this item, so it will be removed from the following drop-down lists.
- Next, if we select names from other drop-down lists, we will see that the selected items or names will be removed from the following drop-down lists.
Read More: Creating a Drop Down Filter to Extract Data Based on Selection in Excel
Quick Notes
🎯 The FILTER function is an exclusive function currently available only for Excel 365. So, it will not work in your worksheet if you do not have Excel 365 on your PC.
🎯 And read this article to learn how to create a drop-down list with unique values in Excel.
Conclusion
In this article, we have learned how to remove used items from drop down list in Excel. I hope from now on you can remove used items from drop down list in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
