# How to Remove Used Items from Drop Down List in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

### 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)`

Formula Breakdown:

• The FILTER function will let us filter the range B5:B14 based on the criteria COUNTIF(E5:E14, B5:B14)=0.
• The COUNTIF function will determine if the range B5:B14 appears in the range E5:E14 or not.
• 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.

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

## Related Articles

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF