Look Who it Was Random video

Excel - Mastering Data Validation: Create Unique Dropdown Lists in Excel - Episode 637

933 views · Published 26 March 2009 · 3:52 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Data Validation: Create Unique Dropdown Lists in Excel.

Welcome back to the MrExcel netcast! In today's episode, we have a tough question sent in by Ben. At first glance, it seemed impossible to achieve, but after some puzzling, we found a solution. Ben wants to use data validation to ensure that once an item is selected from a list, it cannot be entered again anywhere else in the list. 

To demonstrate this, we have set up a spreadsheet with a green area where values can be entered. In column E, we have a list of all the possible values. The challenge is to make sure that once a value is selected, it is removed from the data validation list. To do this, we created a column in column F that checks if the value has been used using a COUNTIF function. If it has been used, it gives a -999, otherwise it gives the largest value in the column plus 1. This forces Excel to give us a list of numbers that we can use for our data validation. 

Next, we used an INDEX and MATCH formula in column I to create a dynamic list that ignores the values with -999. However, we noticed that as we entered more values, we got some N/A's at the bottom of the list. To fix this, we used an ARRAY formula to count the number of N/A's and used that in our dynamic range name. Finally, we set up our data validation to use this dynamic list, and voila! We have a data validation list that updates as we enter values and removes the used values. 

When Ben sent in this question, I thought it was impossible, but it turns out there is a way to achieve it. It may not be the most conventional method, but it gets the job done. So, a big thank you to Ben for sending in this question and thank you for tuning in to another netcast from MrExcel. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Ben asks todays question: Can I set up Validation Dropdowns where the list of items requires a unique entry? For example, once I choose ABC in a column, it should no longer be offered in that column. Episode 637 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction
(00:26) The Solution
(00:37) Setting up the Spreadsheet
(01:00) Using the COUNTIF Function
(01:33) Using INDEX and MATCH
(01:56) Dealing with N/A's
(02:25) Using Dynamic Range Names
(02:50) Setting up Data Validation
(03:19) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
ARRAY formula in Excel
Creating dynamic range names in Excel
Excel data validation tips and tricks
Excel tutorial on advanced data validation
How to restrict duplicate entries in Excel
Index and Match function in Excel
Inserting names and using them in data validation
MrExcel netcast tutorial on advanced data validation
Understanding N/A errors in Excel
Using COUNTIF function in Excel
Using INDEX and MATCH together in Excel
YouTube video on data validation in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152297/

More from this channel