{"id":4727,"date":"2026-09-10T03:02:00","date_gmt":"2026-09-10T07:02:00","guid":{"rendered":"https:\/\/exceltheatre.com\/blog\/?p=4727"},"modified":"2026-09-09T13:36:47","modified_gmt":"2026-09-09T17:36:47","slug":"hide-used-names-in-excel-drop-down-list","status":"publish","type":"post","link":"https:\/\/exceltheatre.com\/blog\/archives\/2026\/09\/10\/hide-used-names-in-excel-drop-down-list\/","title":{"rendered":"Hide Used Names in Excel Drop Down List"},"content":{"rendered":"<p>In some Excel worksheets, you might want each item entered only once. For example, assign employees for one weekly on-call assignment, without duplicates. Instead of a normal drop-down, use this trick, to make a drop down list that hides all the used names.<\/p>\n<p><!--more--><\/p>\n<h2>Video: Hide Used Items in Drop Down List<\/h2>\n<p>In this video, there\u2019s a weekly on-call schedule, where each employee should only be assigned to a single day.<\/p>\n<p>I show how to make a list of the unused names, using dynamic functions in Excel 365. After that, I set up a data validation drop down to show those unused names, instead of the full list.<\/p>\n<ul>\n<li>To get the Excel workbook for this video, go to the <a href=\"https:\/\/www.contextures.com\/xlDataVal03.html\" target=\"_blank\" rel=\"noopener\">Hide Used Items in Excel Drop Down<\/a> page of my Contextures site<\/li>\n<li>That page also has detailed steps for Excel 365, and for older versions of Excel<\/li>\n<\/ul>\n<p><iframe loading=\"lazy\" title=\"Hide Used Items in Excel Drop Down List Data Validation\" width=\"840\" height=\"473\" src=\"https:\/\/www.youtube.com\/embed\/fweVaEIk-fw?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<h3>Video Timeline<\/h3>\n<p>Here\u2019s the video timeline, so you can go back and watch any section again:<\/p>\n<ul>\n<li>00:00\u00a0\u00a0\u00a0 Drop Down All Names<\/li>\n<li>01:06\u00a0\u00a0\u00a0 Formula for Unused Names<\/li>\n<li>03:04\u00a0\u00a0\u00a0 Change the Drop Down<\/li>\n<\/ul>\n<h2>Employee Schedule<\/h2>\n<p>IN the sample file, on the <strong>Schedule<\/strong> sheet, there is a named table &#8211; <strong>tblSched<\/strong>.<\/p>\n<ul>\n<li>Scheduled employee names are entered in the <strong>Employee<\/strong> column<\/li>\n<li>The data validation drop down list only shows names that haven\u2019t been selected already \u2013 the unused names.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2026\/09\/datavalhideuseddynarr01.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"drop down list with unused employee names\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2026\/09\/datavalhideuseddynarr01_thumb.png\" alt=\"drop down list with unused employee names\" width=\"333\" height=\"333\" border=\"0\" \/><\/a><\/p>\n<h2>Formula for List of Unused Names<\/h2>\n<p>On another sheet, named Lists, there\u2019s a table named <strong>tblEmp<\/strong>. It has the full list of employees, and names can be added or removed, as needed.<\/p>\n<p>To get the unused names, I created a formula in cell D2:<\/p>\n<ul>\n<li><strong>=SORT(FILTER(tblEmp[EmpList], <br \/>COUNTIF(tblSched[Employee], tblEmp[EmpList])=0))<\/strong><\/li>\n<\/ul>\n<p>The formula results <strong>spill down<\/strong> from cell D2, to the cells below, in as many rows as necessary.<\/p>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2026\/09\/datavalhideuseddynarr04.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"ormula for list of unused employee names\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2026\/09\/datavalhideuseddynarr04_thumb.png\" alt=\"ormula for list of unused employee names\" width=\"341\" height=\"249\" border=\"0\" \/><\/a><\/p>\n<h3>How it Works<\/h3>\n<p>Here\u2019s how the unused names formula works:<\/p>\n<ul>\n<li>First, the COUNTIF function checks for each name in the Scheduled table\n<ul>\n<li>If a name is not found, its count is zero<\/li>\n<\/ul>\n<\/li>\n<li>Next, the FILTER function returns all the names with a zero count (unused)<\/li>\n<li>Finally, the SORT function puts those names in alphabetical order.<\/li>\n<\/ul>\n<h2>Drop Down List Setup<\/h2>\n<p>The <a href=\"https:\/\/www.contextures.com\/xldataval01.html\" target=\"_blank\" rel=\"noopener\">data validation drop down list<\/a> on the Schedule sheet uses the unused names list as its source.<\/p>\n<p>Here\u2019s how to set that up:<\/p>\n<ul>\n<li>On the Schedule sheet, select all the employee cells in the table named tblSched<\/li>\n<li>On the Excel Ribbon, go to the Data tab, and click Data Validation<\/li>\n<li>For Allow, select List<\/li>\n<li>As the Source, go to the List sheet, and click on cell D2 ( the cell with the SORT FILTER formula)<\/li>\n<li>Add a number sign (#) at the end of the cell reference\n<ul>\n<li><strong>=Lists!$D$2#<\/strong><\/li>\n<\/ul>\n<\/li>\n<li>Click OK, to save the changes<\/li>\n<\/ul>\n<p><strong><br \/>Note<\/strong>: The number sign (#) is a <strong>spill operator<\/strong>. It tells Excel to use the entire spill range that starts in D2, not just the single D2 cell.<\/p>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2026\/09\/datavalhideuseddynarr03.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"data validation drop down with unused employee names\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2026\/09\/datavalhideuseddynarr03_thumb.png\" alt=\"data validation drop down with unused employee names\" width=\"341\" height=\"278\" border=\"0\" \/><\/a><\/p>\n<h2>Test the Dynamic List<\/h2>\n<p>To test the updated drop down, follow these steps:<\/p>\n<ul>\n<li>On the Schedule sheet, for a date with no employee selected, choose a name from the employee drop down.<\/li>\n<li>Then, go down to the next row, and click the drop-down arrow.<\/li>\n<li>The name that you just assigned is removed from the list, so you can&#8217;t accidentally assign it again.<\/li>\n<\/ul>\n<p><strong>Note<\/strong>: If you delete any names from the schedule, those names automatically return to the unused list.<\/p>\n<h2>Get the Workbook<\/h2>\n<p>To get the Excel file for this video, go to <a href=\"https:\/\/www.contextures.com\/xlDataVal03.html\">the Hide Used Items in Excel Drop Down page on my Contextures site<\/a>. The zipped Excel file is in xlsx format, and does not contain any macros.<\/p>\n<ul><!--StartFragment--><\/ul>\n<p>That page also has detailed steps for Excel 365, and for older versions of<br \/>Excel.<\/p>\n<h2>More Excel Links<\/h2>\n<p><a href=\"https:\/\/www.contextures.com\/xldataval01.html\">Data Validation Basics<\/a><\/p>\n<p><a href=\"https:\/\/www.contextures.com\/xldataval02.html\">Dependent Drop Down Lists<\/a><\/p>\n<p><a href=\"https:\/\/www.contextures.com\/xldataval08.html\">Data Validation Tips<\/a><\/p>\n<ul><!--EndFragment--><\/ul>\n<p>____________________<\/p>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In some Excel worksheets, you might want each item entered only once. For example, assign employees for one weekly on-call assignment, without duplicates. Instead of a normal drop-down, use this trick, to make a drop down list that hides all the used names.<\/p>\n","protected":false},"author":2,"featured_media":4730,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_kadence_starter_templates_imported_post":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-4727","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-videos"],"_links":{"self":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4727","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/comments?post=4727"}],"version-history":[{"count":3,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4727\/revisions"}],"predecessor-version":[{"id":4734,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4727\/revisions\/4734"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media\/4730"}],"wp:attachment":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media?parent=4727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/categories?post=4727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/tags?post=4727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}