Udemy logo

excel drop down list 2010There is not a sane person alive who would refuse advice on how to make data entry easier.  If time is money, then data entry is an invasive species of inchworm that eats away at your money tree.  In Excel, you can save gobs of time with drop-down lists.  Even if you have time to waste and money coming out of your ears, drop-down lists allow you to restrain entries to certain items that you define; in other words, they give you power.  I’ll teach you how to create a drop-down list in Excel 2010 (screenshots are from 2011, but I will note relevant differences when applicable), tell you everything you need to know about customizing, and then harness the power of letting go by learning how to safely delete them forever.

The main function of a drop-down list is to create shortcuts to valid entries that exist elsewhere in your workbook; namely, those that would save a lot of time if centrally located.

The first thing we need is a list.  Perhaps you are a coffee aficionado and have a well-documented list of Arabica coffees (in which case, you’d probably enjoy the chance to get privy to the secrets of espresso machines). Anyway:

Screen Shot 2013-12-15 at 3.01.25 PM

A couple things to note: the list must be in a single column or row, make sure there are no blank cells, and structure the items in the order you wish to appear in the drop-down list.  The cell width will determine the width of your list; entries that are too long will be truncated with ellipses (. . .), so take this into consideration.

Also, if you want to use a list from another worksheet, just define a name for it first.  This can be done by selecting the range of cells, clicking the Name box at the left side of the formula bar, and entering a name of your choosing.

Ok.  Go ahead and pick a cell for your drop-down list (I’m going to name C1 “Today’s Cup” and then use C2 for my drop-down list) and click Data Validation, which will be located under the Data tab.  In Excel 2010, Data Validation will be its own option; in 2011, it is located in the same place, but within Validate:

Screen Shot 2013-12-15 at 3.14.26 PM

The Data Validation dialogue box will launch.  Under the Settings tab you will find an Allow option; select List.  This will automatically prompt you to the Source box:

Screen Shot 2013-12-15 at 5.18.15 PM

We have to specify the location of our list, for which we have two options.  If our list resides in a different worksheet, this step is actually easier; just type the list name in the Source box.  But if our list is in our current worksheet, we have to either enter a reference to our list or manually select a range from the current spreadsheet.  To be fair, both methods are quite easy.  It almost goes without saying, but make sure that you enter your reference or list name as you would a formula and precede it with an equal sign (=):

Screen Shot 2013-12-15 at 4.06.36 PM

Check out the new drop-down list in C2:

Screen Shot 2013-12-15 at 3.58.33 PM

Not working?  Select your list cell again and re-open the Data Validation dialogue box.  Make sure In-Cell dropdown is selected; that’s an obvious prerequisite.  While we have the dialogue box open, let’s look at some of the ways you can customize your drop-down list.

Screen Shot 2013-12-15 at 4.18.02 PM

Screen Shot 2013-12-15 at 4.26.47 PM

If you don’t want to fuss over all that and will be satisfied with a default error message, just click the Show error alert box and let Excel do the rest (FYI, the title will be “Microsoft Excel” and the message “The value you entered is not valid.  A user has restricted values that can be entered into this cell.”).  Boring but to the point.

Unfortunately, all good things must come to an end.  Maybe it’s a stomach ulcer.  Maybe  all the caffeine you’re consuming is making you anxious.  Whatever the scenario, you need to delete your drop-down list and rid your brain of its delicious memory.  Waste no time in selecting the cell that contains the list.  Open the Data Validation dialogue box.  Select the Settings tab and click Clear All.  You can still click Cancel at this point if you aren’t quite ready to quit, but I recommend you reclaim your health and finalize it by clicking Ok.

I had some fun with my example, but anyone even mildly proficient in Excel can foresee huge advantages of the drop-down list; in finance, sales, IT, etc.  The applications are endless; get more ideas from these Excel for business tutorials.  Before I sign out, I want to leave you with some relevant food for thought: the maximum number of entries that you can have in a drop-down list is 32,767.  I’m sure you could Google the answer, but frankly, the world could use more mysteries like that.

Page Last Updated: December 2013

Top courses in Excel

Microsoft Excel - Excel from Beginner to Advanced
Kyle Pew, Office Newb
4.6 (267,595)
Bestseller
Microsoft Excel - Data Visualization, Excel Charts & Graphs
Maven Analytics, Chris Dutton
4.6 (26,751)
Bestseller
Microsoft Excel Certification Exam Prep: MO-201 Excel Expert
Maven Analytics, Enrique Ruiz
4.7 (629)
Bestseller
Microsoft Excel - Data Analysis with Excel Pivot Tables
Maven Analytics, Chris Dutton
4.6 (32,434)
Bestseller
Microsoft Excel Pro Tips: Go from Beginner to Advanced Excel
Maven Analytics, Chris Dutton
4.6 (2,854)
Bestseller
Data Analysis Essentials Using Excel
Symon He, Travis Chow
4.5 (7,668)
PowerPoint & Excel Fusion (+250 PowerPoint Slides)
Dragos Stefanescu, Richard Korbut
4.6 (1,094)

More Excel Courses

Excel students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo