Excel Drop Down List 2010: Stop, Drop and Scroll

There 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:

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:

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:

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 (=):

Check out the new drop-down list in C2:

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.

• Maybe you want to ensure the cell cannot be left blank.  In this case, select the Ignore blank checkbox.   If not, leave it unchecked.
• If you want a reminder or warning message to display, select the Input Message tab.  Check the box beside Show input message when cell is selected.  Now all you have to do is think of a message and give it a title:

• The last thing you can do is specify a response when invalid data is entered or selected.  Select the Error Alert tab.  This will look almost identical to the Input Message tab and it works the same way; click the box beside Show error alert after invalid data is entered and choose an appropriate title and message.  You can also pick a logo style: Stop, Warning, or Information:

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.