1. Computing
Ted French

Creating a Drop Down List in Excel

By May 6, 2011

Follow me on:

Create a Drop Down List in Excel
Excel Drop Down List
© Ted French
Since entering data into a spreadsheet is probably the most commonly performed operation in Excel, Microsoft has included a number of tools and features that make it easier to carry out this task.

One of these, and one that can save you some time if you have to enter the same data repeatedly, is the drop down list.

A drop down list gives you a set number of entries to choose from when entering data. Not only can this speed up data entry, but it can also prevent people from misspelling entries or even from entering the wrong data altogether.

To find out how easy it is to use this feature, read the tutorial on Creating a Drop Down List in Excel.

Related Articles
Comments
February 7, 2011 at 7:50 pm
(1) Matthew says:

Thanks for this clear article. I’ve seen many like it while searching for a solution to my current problem. See, I need to create one of these drop down lists in an Excel cell but I need it to allow users to also type in something that’s not in the list. My intent is to use the drop down list as a hint/auto completion field but I cannot find anything like it in all of my searches. If you have any information that can shove me in the right direction I’d sure appreciate it. Thank you!

– Matthew

October 27, 2011 at 4:09 pm
(2) Josh says:

In the Data Validation dialog box there is a tab called “Error Alert.” If you uncheck “Show error alert after invalid data is entered” then people can enter whatever they want without getting a popup dialog.

December 27, 2011 at 4:13 pm
(3) Raelene says:

I am using a drop down list of yes and no. I would like if they choose yes the cell is green and if they choose the cell would be red. Is this possible?

January 5, 2012 at 7:42 am
(4) Gareth Wood says:

Raelene – change the colour of the cells on the data source – the list of options that will be selected when in data validation.

January 20, 2012 at 6:02 am
(5) Vignesh says:

I was success with this Option.
But when I am deleting the original content (the Column)
The Drop Down List does not work any more.
Is there anyway that I can have the Drop down List even after deleting the Column where I have created the List.
Waiting for your Reply.

February 16, 2012 at 9:25 am
(6) DEBRA says:

Just hide it do not delete it.

March 8, 2012 at 8:43 pm
(7) Catherine says:

Is it possible to insert a pop-up calendar so that you can easily select a date in a field? We have a large spreadsheet that is updated by a number of people. I’d like to be able to have someone click in the field and choose the date from a pop-up calendar.

March 13, 2012 at 7:31 pm
(8) Charlie says:

Great guide! Helped me with what I needed to do.. Thanks

March 29, 2012 at 1:22 pm
(9) Joe says:

I understand how to do a drop down list but I need to still be able to add names into those cells that the drop down list is in. But I want the new names to be added to the drop down list and saved into the drop down list. Can anyone help me?

March 30, 2012 at 7:15 am
(10) Marie Fiebig says:

I would like to use a dropdown that allows users to select multiple options. How do I set that up?

April 19, 2012 at 11:40 am
(11) Joy says:

Perfect!!! This is a much easier way of creating a drop down box than using the controls on the developer ribbon. Thank you!!!

June 11, 2012 at 7:34 am
(12) Vimalkar says:

How is it possible to show a symbol in a drop down list. For eg. the symbol “Tick” appear as an alphabet “P” in the list of values instead of as a symbol

June 20, 2012 at 12:49 pm
(13) shoumo das says:

HI IS IT POSSSIBLE TO CREATE DROP DOWN FIELD WHERE IF THE USER TYPE `A `THEN ALL VALUE STARTING WITH A SHOWING AND IF USER TYPE `C` THEN ALLTHE VALUE STARTING WITH C SHOWING.

July 12, 2012 at 4:33 pm
(14) Tom says:

Difficulty when using numbers that you want to appear just as text. To rectify, precede the number you enter with the single-quote (forces Excel to treat numerals as text).

August 13, 2012 at 4:44 pm
(15) Tim says:

I have created a drop down list and it has been working great. But recently, when clicking on the dropdown, it brings it up at the bottom of the list rather than at the top of the list (i made it so the most common choices were at the top). So now I have to scroll up to the top of the dropdown every single time. Annoying and time consuming. Is there a setting or preference where I can have it show the top of the list, rather than the bottom of the list? I tried deleting the list and then recreating it but it didn’t change.

October 12, 2012 at 8:47 am
(16) Gloria says:

HI IS IT POSSSIBLE TO CREATE DROP DOWN FIELD WHERE IF THE USER TYPE `A `THEN ALL VALUE STARTING WITH A SHOWING AND IF USER TYPE `C` THEN ALLTHE VALUE STARTING WITH C SHOWING.

October 24, 2012 at 10:30 am
(17) Kari says:

HI IS IT POSSSIBLE TO CREATE DROP DOWN FIELD WHERE IF THE USER TYPE `A `THEN ALL VALUE STARTING WITH A SHOWING AND IF USER TYPE `C` THEN ALLTHE VALUE STARTING WITH C SHOWING.

February 1, 2013 at 2:07 pm
(18) Tiffany says:

Is there a way to color code whats in the drop down box?

February 25, 2013 at 6:12 am
(19) Muriel says:

HI IS IT POSSSIBLE TO CREATE DROP DOWN FIELD WHERE IF THE USER TYPE `A `THEN ALL VALUE STARTING WITH A SHOWING AND IF USER TYPE `C` THEN ALLTHE VALUE STARTING WITH C SHOWING.

July 31, 2013 at 7:29 am
(20) Scorpio1975 says:

Can anyone help!!??!!

I’m trying to create an information database based on drop down menus.
To be more precise, I want customers to be able to choose options from drop down menus & for an answer to be given based on these choices. For example, If a customer requires a Filter for a car, by selecting their vehicle brand followed by vehicle model followed by engine size followed by fuel type, etc, etc. I would like excel to produce an answer.

Can this be done?

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>

©2014 About.com. All rights reserved.