Pinterest Pixel

Dynamic Data List using Excel Tables

John Michaloudis
Excel Tables have many great features to them and one of them is their ability to create a dynamic drop down list.
Dynamic Data List using Excel Tables | MyExcelOnline Dynamic Data List using Excel Tables | MyExcelOnline

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)

Dynamic Data List using Excel Tables

STEP 2: Select your Table column by hovering over the Excel Table and left clicking when the arrow pointer shows

Dynamic Data List using Excel Tables

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

Dynamic Data List using Excel Tables

Dynamic Data List using Excel Tables

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

Dynamic Data List using Excel Tables

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!)

Dynamic Data List using Excel Tables

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.

Dynamic Data List using Excel Tables

STEP 7: Enter a new entry and this will automatically be updated in your drop down list

Dynamic Data List using Excel Tables

Dynamic Data List using Excel Tables | MyExcelOnline

Download excel workbookDynamic-Data-Validation.xlsx

 

HELPFUL RESOURCE:

Dynamic Data List using Excel Tables

 

If you like this Excel tip, please share it
Founder & Chief Inspirational Officer at

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.

See also  Add a Leading Zero in Excel

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...