How to Add Drop-Down Lists a Data Validation to Excel 2010 Spreadsheets
Data Validation constrains the values of a cell to a pre-set list or specific range. Here’s how to use it in Excel 2007 or 2010.
In Excel 2010 and 2007, a handy feature I use all the time during the budget season is the Data Validation feature which allows you to create an in-cell Drop-Down list for populating fields. Since several of us work on the budget together, by using the drop-down lists, I can keep the data normalized, saving me and my coworkers a lot of time during and at the end of the process. This is just one example, but I’m going to guess you get the point. If not, open Excel 2010 or 2007 and follow along to see the magic of Excel Drop Down lists!
Update: This article is for Excel 2007 & 2010. For instructions for modern versions of Excel, read our article: How to Create Drop-Down Lists Using Data Validation in Microsoft Excel.
Data Validation Excel
Although I normally add Data Validation drop-down lists to entire columns, you can also add the drop-down to individual cells. In the example below, I’m going to build a drop-down to an entire column to help me build out the Genre category of my music collection.
Select which cells to add validation to
Click a column to highlight the entire column.
Data Ribbon
Now that you have the desired cells selected click the Data tab in the ribbon and then click the Data Validation tool.
Validation criteria
From the Settings Tab, Click the Allow drop-down list and click List.
Dropdown List
In the Source box, there are two different options.
- If you already have a few rows with your List data, you can just select those fields. This is why I normally do IE: use a hidden column somewhere in my spreadsheet; however, to keep things simple, I suggest #2 for this exercise.
- Manually enter the data for the drop-down menu by typing them into the box separating them with a comma.
Click OK to save.
Optional: Input Message + Error Alert
There are two additional Data Validation tabs that you can use if you like. The first one is Input Message; this tab will allow you to assign a small pop-up message that appears whenever someone selects a cell with this data validation assigned to it. The second is the Error Alert, this will let you set up a message when someone attempts to input information in the cell that does not match what you put in the source.
Done!
Now all of the cells you initially chose will have a dropdown menu function with a list you can choose from for the cell. These cells will now also be moderated/normalized by data validation, so you will only be allowed to enter one of the source options into the cell. If the user does the drop-down and selects, no problem. If they type it, no problem. If they type the wrong data, they will get the error pop as configured above in Step 5 (if you configured an error message, etc..).
Conclusion
As I mentioned in the opening, I use drop-down lists all the time at work and at home when coordinating articles here at groovyPost. Google Docs also supports the feature.
49 Comments
Leave a Reply
Leave a Reply
cannon_fodder
October 3, 2010 at 9:26 am
Who is to blame as to why this doesn’t work on an iTouch running iOS 4.0? Apple or Google? And by that I mean that I can not access a dropdown list when using Safari or even Opera.
Animis
October 3, 2010 at 1:33 pm
I usually blame Apple for everything. Android – works on android =) (assuming you are talking about Google Docs version of this)
andrea
February 8, 2011 at 11:29 am
Can you expand the instructions to include how to create a drop down list where you can select multiple options for the same cell? Thanks
Ramsey
July 27, 2012 at 5:28 am
Just put “;” between the desired options.
Kim
March 1, 2011 at 9:41 pm
It worked! Much, much easier than the help file in excel on this same question. Thank you!
MrGroove
March 1, 2011 at 10:47 pm
Great feedback! Thanks Kim. Glad we could help!
Heloise
April 14, 2011 at 1:48 am
Hi, it works great just as in 2007. But when I send it to colleagues with 2007 they cannot access the drop down menu. And it does not want to safe in 2003 format at all. It gives me the “significant loss of functionality” error. Any advise. I have tried to download a patch but without success. Thanks.
Stace
April 29, 2011 at 8:01 pm
I am using Excel 2010 and need one dropdown box to multiply someone who orders x-number of shoes by x-colour.
eg:
Number of Shoes required (drop down box 1)
Show Tyoes (drop down box 2)
brown
blue
black
Costs (caluation field)
$100 for brown shoes
$150 for blue shoes
$200 for black shoes
How do I now create this forumla please? :)
Lauren
April 13, 2012 at 8:16 am
DId you ever find out how to do this? I want to do something similair and I can’t figure out how.
I’d appreciate any input.
Thanks.
Stephen
January 11, 2016 at 2:03 am
You need to nest your =IF commands.
Dropdown box is A1 and Quantity is A2 and calculation is A3
=IF(A1=”Brown”, A2*100; IF(A1=”Blue”, A2*150, IF(A1=”Black”, A2*200;” “)))
Something like that, good luck!
dinda
May 12, 2011 at 11:48 pm
thanks …
i now know about this
Koen
November 14, 2011 at 7:23 am
For excel 2010 you have to enter your values separated by ; instead of comma, otherwise it won’t give you a list of separate items
Pam
April 30, 2012 at 8:15 am
I have 2010 and it worked fine with a comma. However, I have another problem. I forgot to put a comma between 2 of the items (of a total of 11) and now I cannot edit it. The Data Validation window/Settings will allow me to change it, then I click OK, click Save, but it doesn’t save it. I access the dropdown list again and it reverts back to the original entry.
headphones for running
December 21, 2011 at 12:11 am
Great post. I was checking constantly this weblog and I’m inspired! Very useful information specifically the last phase :) I take care of such information a lot. I was looking for this particular information for a very long time. Thanks and good luck.
Tara
February 9, 2012 at 3:29 am
How easy was that!!! I had tried excel help and just do not understand their use of the Queen’s lingo – many thanks groovy post :)
Tina
February 15, 2012 at 4:10 pm
I have created a drop down list but when you select the list the font is so small you cannot read. How do I correct so you can actually read the selections.
Thanks
Ed
February 27, 2012 at 11:13 am
The help here was awesome…the only thing I noticed is that you cannot copy and paste the “Validation” into other sheets within the workbook…it just shows as blank…is there a way to do this?
Hasan Ghajar
March 16, 2012 at 12:19 am
thnx man u r good
pragati
April 4, 2012 at 4:02 am
hi I want to genearte report. In which there will be one button on that button click it will save everything. And disply in report but its not taking values from dropdownlist why please help me.
Vikram
April 8, 2012 at 10:40 am
Hi…the list box from the Data Validation options works.. But i have a list of 4000 data to choose from… Is there any way to type the first few letters and the list to show only data starting with the typed letters….
Ross
April 9, 2012 at 1:38 am
This worked great, except that the font in the drop down list is too small to read. Everything I tried to adjust it did not get results. How does one format the text in dropdown boxes?
Stephanie
April 10, 2012 at 7:45 am
I had an older version of excel in which I used validation lists also but used teh worksheet protect feature – now we have upgraded to 2010 and I still have my lists but where my worksheet is protected the validations can not be picked without unprotecting the sheet. Is there away to keep my sheet protected for the areas I want protected and still have a validation table? if so how do i do it?
Colleen
May 18, 2012 at 5:04 pm
I would be interested in seeing the response to Andrea’s question. Also, is there a way to have conditional drop-down lists? For example, if the answer in cell A1 = “Dog”, then the answer in cell B1 must equal “Labrador”, “Cocker Spaniel”, or “Schnauzer”.
Neil
May 19, 2012 at 1:29 pm
I want to know how to post my excel to the web but retain my drop down menus. Any tricks for that?
Neil
May 19, 2012 at 1:30 pm
I want to know how to post my excel to the web but retain my drop down menus. Any tricks for that? Answer this one as it emails me.
Charlie
June 13, 2012 at 8:13 am
Everytime I get stuck with Excel and know that there should be a formula or easier way of doing this, I google it and it always brings me to this site. Which in return helps me simplify and solve my problem – in my mind – “I knew there is a way to do it here in excel!” From them on, I bookmarked this site. Thanks Austin for all the help!
Saint Moin
June 28, 2012 at 2:03 pm
Hi can I create a drop down list to select Type of Value. For example everything in “Centimeter” column will change to Inch accordingly if I select “Inch” from drop down list. Suppose 2.54 in Centimeter and when I select Inch it converts to 1 automatically. Is that possible?
Mark
July 27, 2012 at 2:02 pm
How do I keep the tab showing in a drop down box to let me know that the cell has a drop down box attached when I move to another cell.
Kat
October 3, 2012 at 12:38 pm
Thanks so much…most concise description I’ve found that works!! Awesome
Tammy
October 5, 2012 at 5:28 am
We have a spreadsheet with two drop down menus. Based upon the selections in the drop down menus we would like to have an answer appear in another cell. For example
Drop Down box 1 = lawnmower
Drop Down Box 2 = Generic
Cell = service fee for work on a generic lawnmower.
Thank you.
vandera12
October 8, 2012 at 6:18 am
I’ve created a spreadsheet using the data validation sets, and have filled in numerous cells with the data. Worked like a charm. However, I’ve had to update the data set in the drop-down, and now need to update all of those cells. Is there a way to update the cells that have already been filled in? Meaning, if my data set was “black, blue, red” and I have 400 cells filled in, but I’ve updated the data set to be “black, purple, red”, and need to change the ‘blue’ cells to ‘purple’, is there a way to automate this? or do I have to select each cell individually to update? Thanks!
Angela
October 8, 2012 at 6:30 am
oops, nevermind, I found the checkbox! Monday mornings…. :)
jjzmgailey
November 29, 2012 at 12:36 pm
I have an excel spreadsheet with a “categories” column with a drop-down menu in each cell and the next column over is “sub-categories”. Is there a way to create a rule to have the sub categories drop-down list depend on what is selected in the categories column because each category has its own set of sub-categories?
Bob
March 7, 2013 at 7:33 am
Did you get a reply? I have the same situation and would appreciate it if you could help me.
eric anderson
February 28, 2013 at 7:45 am
Info
Arvind
March 18, 2013 at 2:11 am
Hi,
I want to create an excel for the change of shifts monthly as well as to weekly basis, i am not able to understand how that can be done, I have given names in row and shift timings in columns, using if function i am not able to follow and please let me know marcos can be runned on this if i click on the button next to name all the column values should changed, please suggest me how that can be done.
Hugh
May 15, 2013 at 4:01 pm
Hi,
I have data validation applied to column C in an excel 2010 worksheet.
When I copy from another cell (e.g. F10) into C10, the data validation rules are lost in cell C10.
What can be done to avoid this?
Robert
May 22, 2013 at 10:28 am
Hello, This has been very helpful. I still have a question though. Say if I want to have a drop down box with “pass, fail & not supported” but when “pass” is selected the background would be green and the lettering is black, “fail” would be a background of red and with the lettering being black and “not supported” would be a blue background with black lettering. Thanks for you assistance.
sandeep
July 12, 2013 at 11:10 am
Can we increase number of selections in dropdown..
we in dropdown we can add 17 but i want to extend this upto 30
Tony M
August 14, 2013 at 6:26 am
Assuming your list resides in a group of cells somewhere in your workbook, go to the list and select 13 cells (avoid the last cell) right click and select “Insert”. Select “move cells down” and that should give you a total of 30, enter your new selections and sort to your liking. Now go back to any cell with the corresponding drop-down and make sure it worked.
Jeff
September 11, 2013 at 5:21 pm
Are the drop downs compatible between Excel 2007 and 2010? I use 2010 and no one who uses 2007 can see them!?
Nancy Young
September 12, 2013 at 11:09 am
Thanks! Very easy directions!
Mikkel
November 28, 2013 at 8:50 am
Hi. nice worktroug.
An aditional question.
Is there a way to include the colors of the source cell?
I have colored the source cells with the fill bucket, but the drop down menu is stil white.
Elizabeth
February 14, 2014 at 9:09 am
I am using Excel 2010 and would like to construct a drop-down list where I can choose from multiple items, without choosing all. I tried using the semi-colon, as suggested above, but when I did that it brought up every single item in the list, which is wrong. I need to be able to select my choice of items out of the list, not being limited to one or all. How do I do this?
JANE
March 20, 2014 at 2:47 pm
Now if only the drop-down list can be actually seen while I am entering data in the spreadsheet — that would be swell. Why is the font so small? Really — why?
Elizabeth Estabrooks
March 21, 2014 at 7:39 am
That’s easy enough, but the bigger question is how you set it up so that multiple choices can be made within that list. For instance, if the Variable is Types of Convictions, how do I set up my list so that all the possible convictions can be listed AND the choices can include more than one of them?
Sally
June 13, 2014 at 1:28 pm
Within a data validation list (using dates), how can you get the selection go to the last date selected? (The list shows a week at a time.) For example, you want to choose 6-8-14. The last date used/selected from the drop down list was 6-1-14 (a week prior). How do you get the list to populate with the 6-1-14 date (last used) without having to have the list start back at the first week of 2014 each time?
Joshua
December 9, 2015 at 8:03 am
I was able to practice the lesson successfully.
Thank You!
Anil Karne
April 26, 2016 at 1:53 am
Its very useful article as well define very simple and understandable language.