Hide Used Items in Drop Down List With Excel 365 Formula

In some data entry lists, you only want each item selected once. For example, pick players for a baseball lineup, or assign employees for their weekly on-call assignment. To prevent entries from being selected twice, use this data validation trick, to hide the used items in an Excel drop down list.

Drop Down List with Unused Items

Here’s a screen shot of the drop-down list, with only the unused names showing.

Bert and Joe have already been assigned to the schedule, so their names aren’t in the list.

NOTE: This technique uses the new SORT and FILTER functions from Excel 365.

hideuseditems09

Hide Used Items

In this video, there’s a weekly on-call schedule, where each employee should only be assigned to one day.

Instead of choosing from a drop down that shows all the names, see how to hide the used names.

  • With the new functions in Excel 365, you only need one formula in a worksheet cell, to build a list of the unused names.
  • Then, use that list as the source for the drop down on the schedule sheet.

Video Timeline

Here’s a list of topics that are covered in the video, so you can go back and watch any section again:

  • 00:00    Introduction
  • 00:19    New Functions
  • 00:35    Drop Down All Names
  • 01:06    List of Names
  • 01:45    Build the Formula
  • 03:04    Change the Drop Down
  • 03:59    Get the Workbook

Older Versions of Excel

You can hide used items in earlier versions of Excel too, but it has two worksheet formulas, and a named range formula:

  • formula to check if each name was used
  • array formula to list unused names
  • named range for unused names list

There are step-by-step instructions on the Hide Used Items in Excel Drop Down page of my Contextures site.

Really Old Version of Excel

I’ve been using this technique for a long time, and “Hide Used Items” is  one of the earliest tutorials that I posted on Contextures.

I can’t remember the exact date that I created the page, but the Internet Archive (Wayback Machine) shows my Hide Used Items page from August 17th, 2002!

Here’s a screen shot from that page, showing the drop down list. Fred and Joe have already been assigned, so their names aren’t in the list.

What version of Excel was I using? Maybe it was Excel 97 – I loved that version!

hideuseditems_2002_dv35

More Examples

There are more “Hide Used Items” examples on my site, like this one, that lets you choose baseball players for each inning, from separate drop down lists.

datavalhidden01

You can also use this technique for dependent drop down lists. Choose a Type in column A, and the drop down in column B shows available items for that Type.

There are a couple of other examples too, so check them out!

Get the Workbook

To get the Excel workbook for this video, go to the Hide Used Items in Excel Drop Down page on my Contextures site. The zipped Excel file is in xlsx format, and does not contain any macros.

That page also has instructions for earlier versions of Excel, using different formulas, and other ways to use the “Hide Used Items” technique.

____________________

Hide Used Items in Drop Down List With Excel 365 Formula

Hide Used Items in Drop Down List With Excel 365 Formula

Hide Used Items in Drop Down List With Excel 365 Formula

____________________

One thought on “Hide Used Items in Drop Down List With Excel 365 Formula”

  1. This is a great technique! One slight improvement is instead of the original list being a statistical named range, if you make it a dynamic named range you can continue to add items to the list. The only caveat is there can be no blanks in the middle of the list and you can’t have any other info in the column above or below the items in the original list.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.