Download Excel Workbook
Excel Tables have many great features to them and one of them is their ability to create a dynamic drop down list.
A dynamic drop down list expands as the Excel Table expands when new data gets added to it.
This is great when you want to have users select from a predefined text or value list rather than having them manually enter data, which can lead to mistakes.
Here is how to create a dynamic data validation list using Excel Tables:
STEP 1: Convert your list in to an Excel Table (Ctrl+T)
STEP 2: Select your Table column by hovering over the Excel Table and left clicking when the arrow pointer shows
STEP 3: In the ribbon menu, go to Formulas > Define Name > enter a custom name with no spaces (we will put this name in step 5) and press OK
STEP 4: Click on the other Excel Table column that you want to enter the dynamic list into and go to the ribbon and choose Data > Data Validation > List
STEP 5: In the Source box enter the name you created in Step 3 and press OK or click in Source box, press F3 and select the named range from there (thanks to Michael from www.excelpogodzinach.pl for introducing this tip to us!)
STEP 6: If you want to add extra data into your Excel Table list, hover with your mouse in the bottom right hand corner and when you see a double arrow, drag down.
STEP 7: Enter a new entry and this will automatically be updated in your drop down list
Download excel workbookDynamic-Data-Validation.xlsx
HELPFUL RESOURCE:
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 Academy Online Course.
