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.

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)

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

See also  3 Quick Ways on How To Create A List In Excel

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


Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

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...