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. 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. 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? :) Thanks. Dropdown box is A1 and Quantity is A2 and calculation is A3 =IF(A1=”Brown”, A2100; IF(A1=”Blue”, A2150, IF(A1=”Black”, A2*200;” “))) Something like that, good luck! Thanks 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. 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? we in dropdown we can add 17 but i want to extend this upto 30 Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
