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.


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.

excel drop down list remove 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())

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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

Use Helper Columns to Remove Used Items from Drop Down List in Excel

  • Finally, we will now get all the rows numbers of cells of the Employee.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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

Use Helper Columns to Remove Used Items from Drop Down List in Excel

  • Finally, we will now get all the employee names in the Employee column.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

Step 3:

  • Next, we will click on the Define Name under the Formulas.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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

FILTER and COUNTIF Function

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.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

  • Now, a new window titled Data Validation will appear. Then, we will select List from the Allow drop-down menu.

FILTER and COUNTIF Function

  • Then, we will insert =Employee in the Source input box.
  • After that, we will click on OK.

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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

Use Helper Columns to Remove Used Items from Drop Down List in Excel

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

Use Helper Columns

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

Use Helper Columns to Remove Used Items from Drop Down List in Excel

Read More: How to Create Drop Down List in Multiple Columns in Excel (3 Ways)


Similar Readings:


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)

: Remove Used Items from Drop Down List in Excel Using FILTER and COUNTIF Function

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.

: Remove Used Items from Drop Down List in Excel Using FILTER and COUNTIF Function

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.

: Remove Used Items from Drop Down List in Excel Using FILTER and COUNTIF Function

  • Now, a new window titled Data Validation will appear. We will select List from the Allow drop-down menu.

FILTER and COUNTIF Function

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

: Remove Used Items from Drop Down List in Excel Using FILTER and COUNTIF Function

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

FILTER and COUNTIF Function

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

: Remove Used Items from Drop Down List in Excel Using FILTER and COUNTIF Function

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

FILTER and COUNTIF Function

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!!!


Related Articles

ASM Arman
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo