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.
Now that you have the desired cells selected click the Data tab in the ribbon and then click the Data Validation tool.
From the Settings Tab, Click the Allow drop-down list and click 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.
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..).
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.