Microsoft

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.


49 Comments

49 Comments

  1. cannon_fodder  

    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  

      I usually blame Apple for everything. Android – works on android =) (assuming you are talking about Google Docs version of this)

  2. andrea  

    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  

      Just put “;” between the desired options.

  3. Kim  

    It worked! Much, much easier than the help file in excel on this same question. Thank you!

  4. Heloise  

    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  

    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  

      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  

      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!

  6. dinda  

    thanks …
    i now know about this

  7. Koen  

    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  

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

    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  

    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  

    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  

    thnx man u r good

  13. pragati  

    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  

    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  

    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  

    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  

    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  

    I want to know how to post my excel to the web but retain my drop down menus. Any tricks for that?

  19. Neil  

    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  

    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  

    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  

    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  

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

  24. Tammy  

    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  

    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  

      oops, nevermind, I found the checkbox! Monday mornings…. 🙂

  26. jjzmgailey  

    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  

      Did you get a reply? I have the same situation and would appreciate it if you could help me.

  27. eric anderson  

    Info

  28. Arvind  

    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  

    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  

    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  

    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  

      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  

    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  

    Thanks! Very easy directions!

  34. Mikkel  

    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  

    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  

    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  

    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  

    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?

  39. Joshua  

    I was able to practice the lesson successfully.
    Thank You!

  40. Anil Karne  

    Its very useful article as well define very simple and understandable language.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top