1. Computing
Ted French

Creating a Drop Down List in Excel

By November 16, 2007

Follow me on:

Creating a Drop Down List in Excel 2007
Creating a Drop Down List in Excel 2007
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 article on Creating a Drop Down List in Excel.

Related Articles
Comments
January 20, 2010 at 11:55 am
(1) Sara says:

This was very easy to do and I did it with ease, EXCEPT I came across one problem nobody seems to be addressing. I’m trying to do a drop-down list that does not appear elsewhere on my spreadsheet. I typed the list specifically FOR the drop-down. Therefore, when I delete the items I typed, the choices in the drop-down disappear as well. Is there a way to remedy this?

January 21, 2010 at 9:46 pm
(2) Hannah says:

You can only hide them from the sheet or put them in another sheet

February 8, 2010 at 6:20 am
(3) Nick, UK says:

You can label your cells and do a drop down list from that.

If you select the cells with the information you want as a drop list then goto the area that shows what you have selected (if you slected the first cell on the page it would say A1)

Type a label (mine was Name)

Go to validate data on another worksheet and type in the area where you would pick the cells and type

=Name (this was my label)

it will create a list from your labeled coloum or row.

Hope this is clear it was a bit tricky to exlpain.

TTFN
Nick

April 1, 2010 at 3:39 pm
(4) Trena says:

You can create a list where the data is not located on your spreadsheet.
Click on Data
Select Data Validation
On the settings tab select “List” from the “Allow:” drop down
In the section marked source type in your list separating each listed item by a comma.

April 11, 2010 at 5:37 pm
(5) Yash says:

Perfect! Thanks. Exactly what I needed. :)

June 23, 2010 at 3:56 pm
(6) karyn says:

Thank you Trena! That worked PERFECTLY!!! I am doing the Happy Dance!! :)

July 14, 2010 at 3:05 am
(7) Kapil says:

Hi,
I am not able to create a drop down list from another reference sheet.able to only in same sheet,please help me out of this problem.

October 13, 2010 at 7:27 am
(8) Jan says:

Hi Kapil (and the rest) – you probably already found it, but:

the syntax is =Worksheetname!range

Worksheet name – Sheet2
range = A1:A6
range2 = C1:C10

syntax in source:

=Sheet2!A1:A6,C1:C10

December 8, 2010 at 4:07 pm
(9) Mary says:

Hi Kapil, I did not have that syntax so thank you very much. That helped the first part of my problem.

My 2nd question is: can my ‘list’ be formatted colored cells (no text)? I am doing a green-yello-red status. I formated each of the cells for red, yellow and green. but when I set up the validation, the list shows up as if the options should be there, but they are blank. Any idea of how I can have my list be cell colors?

December 9, 2010 at 3:26 pm
(10) gene says:

I need something a bit different. My problem…… I have 2 columns. Cells A1 through Axx contain the product description. Cells B1 through Bxx contain the product cost. I can easily create a drop down showing the descriptions, but when I select the description (col. A), I really want to use the corresponding cost (col. B) to calculate the total product cost.

Think of it as making a pizza…… I select the product from col. A (crust, sauce, toppings, etc.) but I want to use the corresponding cost from col. B ($2.00, .65, .35, etc.)

I’m sure it can be done, I just don’t know how.

I’m not a programmer.. Thanks.

February 4, 2011 at 2:53 am
(11) David says:

Hi Mary,
I am only a beginner, so my thoughts on your dilemma may be amateurish and crude, and I may be shot down in flames for it, but how I would address the colour issue is to have my data validation list contain the following values:
Red, Blue and Yellow. Then the cell in which I wish this displayed would have the following conditional format rules
if cell value is equal to red (then make it display red)
if cell value is equal to Blue (then make it display Blue)
if cell value is equal to Yellow (then make it display Yellow)

June 23, 2011 at 4:45 am
(12) Sudhi says:

Thanks, Great Help

June 27, 2011 at 4:29 pm
(13) Sarah says:

Mary,

Another thing you can try is conditional formatting. It’s under the Home tab in the style section. Select the range that you want to turn different colors, and then select the appropriate conditional formatting, and you can make it turn colors based on dates, values, equallity and several other things. It’s pretty easy to get the hang of :)

September 29, 2011 at 4:11 pm
(14) Amy says:

Using my named list and data validation and love it. My question is, how can I select something from the list without having to use the mouse to drop down and select and then tab on over to the next field?

October 2, 2011 at 7:48 am
(15) spreadsheets says:

Amy:

If you are asking how you can use the keyboard to open the drop down list you can use the key combination of Alt + the down arrow key

October 27, 2011 at 1:08 pm
(16) joe says:

When I type my data validation list on another sheet it works. but when i save and close it then reopen, only the data validation drop down is there if i hid the list on the same sheet. If the source was on another sheet it does not save. please help!

November 9, 2011 at 7:56 pm
(17) hacker says:

how can i create multiple drop downs?

November 10, 2011 at 2:53 pm
(18) Roy B says:

I haven’t been able to figure out how to accomplish this – Y have a “master page” worksheet that includes a list of account names which correspond to individual work sheets for each account. Is there a way to click on the cell on the master page that includes the account name and have it send me to the corresponding work sheet?

December 23, 2011 at 10:11 am
(19) Sherif says:

when a make a list consists of different rangs(i.e. B4:G15)
the below massage appeared:
“The List source must be a delimited list, or a reference to singl row or column”

Iam going crazy…. i realy need to make a list consists of different rangs or cells in the same worksheet or another sheet….. HOW CAN I DO THIS !!!!!!!!!!!!!!!!!!?????

January 3, 2012 at 11:46 am
(20) Marie says:

I can’t go to Data Validation. It’s grayed out. What to do??

January 12, 2012 at 9:58 pm
(21) Ron says:

I have created a list and now I need to add the different parts of the list for totals. for example the list has different excercises. R for running, b for biking, c for cycling, etc. I want to add up how many times I did each activity throughout a given week. Any ideas how to do that?

January 30, 2012 at 1:59 pm
(22) Elaine says:

I have an excel spreadsheet containing dropdown lists on another hidden sheet. Question is: I can change the font color in the dropdown lists as well as bold the font but when the dropdown list is displayed on the worksheet, the font color is not displayed and/or bolded. I tried the conditional formatting but this does not work as well.

March 8, 2012 at 11:40 am
(23) BBoyd says:

I am trying to make a drop down list that contains text. But once the text is selected I want the selection to display a number. Please help.

March 14, 2012 at 12:25 am
(24) AKS says:

Thank you Trena! That worked PERFECTLY!!!

March 24, 2012 at 2:24 pm
(25) Lee says:

I have a column with a pull-down list (tied to a named List). The column is only 4 characters wide, but the when I pull down the list, the window is much wider. Is there a way to control the width of the list?

April 8, 2012 at 2:18 pm
(26) Anu says:

Thanks a lot trena, your tips were highly simple and easy to understand. Solved my dilemma in a jiffy :)

April 11, 2012 at 1:11 am
(27) hemanth says:

Thanks.it is very usefully…..good job..

April 27, 2012 at 12:57 pm
(28) Troy says:

If i have 3 columns, 1 is data (Matthew, mark, luke , john) and the 2nd is (1,2,3,4) … and the 3rd is (First, Second, Third, Fourth)… can i create a dropdown for the 1st (Names), that when one name is selected, it fills the remaining associated cells of info in the other 2 columns beside it?

June 25, 2012 at 5:02 am
(29) Murad says:

Use validation list on the first column, then use VLOOKUP to select data for 2nd & 3rd rows.

June 28, 2012 at 3:09 am
(30) Jeni says:

thanks Trena

July 23, 2012 at 8:51 am
(31) Jay says:

Thanks Trena… It helped me !!!

July 24, 2012 at 1:14 pm
(32) Sharon says:

I’m not sure how to allow multiple selections when choosing items from the drop down box. I want to be able to select one of more choices from a list. Please explain how to a novice at creating drop down boxes.

July 24, 2012 at 7:33 pm
(33) Marc says:

I have added a drop down list to a spread sheet my security officers use on a lap top to make entries during there patrols. They make allot of different entries during there shifts. I made a drop down list with a handful of commonly used tasks. This just saves them typing time. Is there a was to allow them to type other entries on the spreadsheet instead of selecting whats in the pulldown? When I try to type something else then whats in the pull down, I get an error message. Thanks

August 31, 2012 at 3:39 pm
(34) Penny says:

Use the Error ALert tab and uncheck the Show error alert.

September 12, 2012 at 7:50 pm
(35) Jae says:

Same question as Sharon, how do I create a dropdown list and allow selection of multiple entries?

September 20, 2012 at 1:58 pm
(36) Solimar says:

I have created a drop down list in a hidden sheet that I constantly update. How do I set it so when they attempt to key data in they get a warning to select from the drop down list?

September 25, 2012 at 5:28 am
(37) nick says:

Mary I have the same problem. Did you find any solution ?

October 3, 2012 at 11:45 am
(38) london says:

how do i add a name to a drop down list but can’t find the orignal drop down list which is feeding this?

October 4, 2012 at 10:08 am
(39) jeff says:

I’ve created a drop down list. I need to include ten items in the list which are aspects of patient care. Each of the ten items are sentences that contain several words (i.e. Timeliness and appropriateness of diagnosis). After typing in the 4th or 5th aspect in the drop-down list ‘source’ box, it will not allow me to go any further. It’s as thought the capacity of the drop down list is limited.

Is there any way to increase the capacity of what can be entered in the drop down list?
Any other recommendations appreciated.

November 2, 2012 at 12:55 pm
(40) Gerard says:

How can I make the validated list be sorted A-Z and not presented in the same order as it is in the list – without sorting the actual list itself. Is this possible?

November 14, 2012 at 9:54 am
(41) Paul James says:

Amazing – at last someone who gives an undertsandable way of doing things and it works!

November 28, 2012 at 4:27 pm
(42) Ken says:

I want a drop down list that references a list I have in another workbook (whole other excel file). Is that possible? How?

December 11, 2012 at 6:43 am
(43) Vinay says:

Thanks terna

December 17, 2012 at 8:33 pm
(44) Jeremy says:

Worked great, thanks for letting me know how :)

December 27, 2012 at 4:31 pm
(45) Gina says:

Hello,

I know how to create a drop down list. My dilemma lies here; when I make a selection from my drop down list I would like to have that selection pull up different choices that is tied to that selection.

(Example) Drop Down List:
Red
Green
Blue

I chose the color Red. Now I would like a list of all Red Items like apple, cherry, car (all things tied to Red) to appear in the neighboring column.
I chose the color Green. Now I would like a list of all Green Items like grass, money, trees (all things tied to Green) to appear in his neighboring column.

Is this at all possible in Microsoft 2007? If so can someone please help me with step by step directions. Much appreciated! Thank you.

February 27, 2013 at 4:04 am
(46) Teena says:

Thanks alot Trena

April 17, 2013 at 9:39 am
(47) MikeLeon says:

Can you creat a list and when you click on a choice on the list it takes you to a different area of a spread sheet? I am working with a very large workbook and want to be able to quickly go to certain areas of it from the first page. Thanks.

May 18, 2013 at 5:35 am
(48) rajesh tiwari says:

thank you for giving good knowledeg

August 8, 2013 at 3:17 pm
(49) Joerg says:

When I do math on my filtered dataset (rows hiden), the results are still based on the entire dataset. How can I get results based just on the rows that show? Example: average of column D (revenues), based on just 2010-2013 data (in column C), excluding earlier years

September 27, 2013 at 12:53 am
(50) Gopal Sharma says:

in data validation menu, allow tab , there is nowhere ” SOURCE ” , i am using excel 2007

November 15, 2013 at 8:13 am
(51) Martina says:

Hi,
Does anyone know if you can create a multiple choice pick list whereby you can enter more than one choice in the same cell:

e.g. Pick List = smarties, rolo, buttons,maltesers. Enter:”rolo: smarties” in the same cell?

Thanks

January 15, 2014 at 7:32 am
(52) Kay says:

I am using excel 2010 and I have a drop down list over 10 pages from a key which is on the initial page and contains letters and a colour.

The data for the drop down lists is all correct and the letters are displayed however when I select from the dropdown list the colours of the cells from the key does not display.

How can I make the cells on all work sheets turn to the colour of the drop down list?

Many Thanks

Leave a Comment

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

©2014 About.com. All rights reserved.