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

How to Insert an Excel Table Excel Tables are very powerful and have many advantages when using them.  You should start using them asap regardless of the size of your data set, as their benefits are HUUUGE:1. Structured referencing; 2. Many different built in Table Styles with color formatting; 3. Use ...
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, ...
Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
Text to Columns – Emails If you have a data set with text consisting of names and email addresses that are wrapped inside a parenthesis, like:John (john@email.com) ...then you can use the Text to Columns feature in Excel to take out the email addresses and put them in a separate ...