Watch it on YouTube and give it a thumbs-up!
What´s a dynamic data validation drop down list in Excel, you say?
Well, as you add new data into your Excel Table, your drop down list automatically gets updated.
That is a cool feature and it means that you do not need to update your data validation source reference each time you update your data with a new entry, saving you heaps of time in the long run.
In a few steps you too can create this:
1. Convert your data to an Excel Table (Ctrl+T)
2. Select your table´s column by hovering over the Excel Table and left clicking when the arrow pointer shows
3. In the ribbon go to Formulas > Define Name > enter a custom name with no spaces (we will put this name in step 5) and press OK
4. Click in a cell and go to the ribbon and choose Data > Data Validation > List
5. In the Source box enter the name you created in Step 3 and press OK
6. If you want to add extra data into your Excel Table, hover with your mouse in the bottom right hand corner and when you see a double arrow, drag down.
7. Enter a new entry and this will automatically be updated in your drop down list.
Download workbookDynamic-Data-Validation.xlsx
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.