Top Nav

How-To Add Drop-Down Lists And Data Validation To Excel 2010 Spreadsheets

Excel 2010 data validIn Excel 2010 and 2007, a handy feature I use all the time during 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!

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.

Step 1 – Select which cells to add validation to

Click a column to highlight the entire column.

select a cell collumn in excel

Step 2 – Data ribbon

Now that you have the desired cells selected, Click the Data tab in the ribbon and then Click the Data Validation tool.

data validation in excel 2010

Step 3 – Validation criteria

From the Settings Tab, Click the Allow drop down list and Click List.

excel 2010 validation criteria

Step 4 – Dropdown list

In the Source box there are two different options.

  1. 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.
  2. Manually enter the data for dropdown menu by Typing them into the box separating them with a comma.

Click OK to save.

set dropmenu contents in excel 2010

Step 5 – 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.

dropmenu excel 2010 pop-up alerts

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..).

data validation in excel 2010

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 so stay tuned for that How-To tutorial in a few days.

, ,

46 Responses to How-To Add Drop-Down Lists And Data Validation To Excel 2010 Spreadsheets

  1. 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)

  2. 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.

  3. 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!

  4. 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.

  5. 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.

  6. dinda May 12, 2011 at 11:48 pm #

    thanks …
    i now know about this

  7. 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.

  8. 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.

  9. 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 :)

  10. 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

  11. 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?

  12. Hasan Ghajar March 16, 2012 at 12:19 am #

    thnx man u r good

  13. 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.

  14. 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….

  15. 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?

  16. 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?

  17. 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”.

  18. 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?

  19. 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.

  20. 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!

  21. 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?

  22. 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.

  23. Kat October 3, 2012 at 12:38 pm #

    Thanks so much…most concise description I’ve found that works!! Awesome

  24. 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.

  25. 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…. :)

  26. 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.

  27. eric anderson February 28, 2013 at 7:45 am #

    Info

  28. 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.

  29. 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?

  30. 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.

  31. 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.

  32. 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!?

  33. Nancy Young September 12, 2013 at 11:09 am #

    Thanks! Very easy directions!

  34. 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.

  35. 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?

  36. 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?

  37. 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?

  38. 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?

Leave a Reply

 

×

Get Early Black Friday Deals Now - Amazon.com Check Deals