Add New Items To Excel Drop-down Lists Automatically In Seconds!

In this example we will use Excel Tables and named ranges to create the dynamic lists. The named range references a single Table column using structured references (Table Name[Column Name]). The named range includes all cells in the column and never needs to be updated or maintained.

This means we don't have to remember to update the source data range every time we make changes to it. It saves us a lot of time and helps prevent errors.

Compatibility: This solution uses Excel Tables that were introduced in Excel 2007 for Windows. It will work in all versions of Excel including Excel 2007, 2010, 2016, 2019, Office 365 for Windows and Excel 2011, 2016, and beyond for Mac.

The Free List Search Add-in:
Unfortunately we can't directly search the drop down lists within Excel. So I created a free add-in called List Search that allows you to search any drop down list and input the value in the cell.

It's packed with additional time saving features. Click the link below to learn more and download List Search.

Other Videos and Resources:

00:00 Introduction
00:35 Insert a Table
01:04 Insert a Named Range
03:20 Add New Items
04:18 Dynamic Named Ranges

[GamingRevenant]

This video is THE solution for everyone struggling with making organised and dynamic Data Validation lists. Many, MANY thanks!

[jaypogi]

by far, the best tutorial video for this topic. Straightforward and easy to follow.

[vtetreault]

Great quick tutorial. Super easy to follow. Short and to the point.

[ryanpanes]

this is by far the neatest procedure ive seen. many thanks!

[gerrybaker]

fab instruction - I have created a whole load of pick lists on my current spreadsheets, which will stop others putting erroneous information my tracker!! THANKS!!

[GearheadExplorer]

Many thanks for making this video! This will definitely be a time saver in my shop tool database.

[tejsingh]

Step to step instructions, nice and clear

[mariviclelis]

Thanks Jon it really helps me! Clear explanation easy to follow steps love it

[andrewmcdermott]

Really helpful, thank you! Brilliant tutorial.

[angelagarine]

Thank you for making the use of Excel quicker and easier

[francinethings]

Dude, you were all over the place! I see I have a lot to learn.

[wayneedmondson]

Hi Jon.. thanks for another helpful and informative video tutorial. Thumbs up!

[billleonard]

Subtle, as is usually the case. It makes sense though. I've been tripped up by having cell-references within some named ranged, within tables. That now sticks out as a dumb oversight on my part. Well done and many thanks.

[victorzarate]

Nice tutorial, I will definitely be coming for more and message you for solutions if needed :)....Thanks for sharing!

[ashutosh]

Very informative lession a lot and keep it up

[MarkRoche]

Thanks so much! Really well explained and has helped me a lot.

[richards]

Excellent, keep up the great tutorials

[treajwortham]

Really great video well explained not to confusing

[goyal]

Thank you so much man, This is incredible. I love this...

[ahmedimam]

Your videos are great! I'm looking for format the drop down list fonts, increase the contents 8 items max??