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

defined name list

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

data validation

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 WORKBOOK

Dynamic Data Validation

HELPFUL RESOURCE:

Zack Barresse Excel Tables Podcast

 

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Pivot Table Count Transactions The default Value Field Setting when analyzing data with a Pivot Table is to Sum, but you can also analyze a Pivot Table with a Count.This is useful when you want to see how many sales transactions  took place within a region, a month, a year or per business unit.DOWNLOAD...
Excel´s Fill Justify Tool An interesting tool within Excel is the Fill Justify.  It allows you to select text from several rows and merge them in to one cell.So if you have data that gets downloaded in to separate rows and want to join them up in to one sentence, then the Excel´s Fill Justify option i...
Clear a #REF! Error in Excel When your Excel workbook has formulas that are linked to other workbooks/cells that have been deleted, you will get a #REF! error.This means that the formula refers to a cell that isn’t valid.To get rid of this error message we have to select the cell(s) with this error, ...
Top Excel Data Cleansing Techniques Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it.So what is data cleansing?In the words of Oz du Soleil, Excel MVP, it is like "Hercules being sent out to capture the three-he...