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

See also  Summarize Data With Dynamic Subtotals

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 WORKBOOK

 

HELPFUL RESOURCE:

Dynamic Data List using Excel Tables

 

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin