Using Data Validation to Make a Dropdown List in Excel

Microsoft Excel offers a tool, called Data Validation, which makes it simple for you to create unique drop down lists. Drop down lists are particularly helpful when you need to create an Excel form or database into which users enter information. When you make a drop down list using Data Validation, you control the data users can enter while making it easier for them to do so. You can also use Data Validation drop down lists to make it easier on yourself if you frequently need to enter one of a handful of items in a column.

The first step in creating a Data Validation drop down is to make a list of the items you want in that drop down. There are two ways to do this. The first is to enter the list into the worksheet itself, and the second is to go directly to the Data Validation tool. We will start with making a list on the spreadsheet.

You can enter the reference list on a different Excel worksheet than the one containing the drop down. However, in order for the Data Validation tool to work, users must have access to the sheet with the list and that worksheet must be open. I recommend that, until you are comfortable working with Data Validation, you enter the list in an out-of-the-way area of the same sheet. Enter each item of the list into a separate cell in the column.

click to enlarge

There are two ways to work with this list. The first way is probably the simplest. Click on the cell that you want to contain the drop down list. In Excel 2003, go to the Data menu and click on Validation. In Excel 2007, click the Data tab of the ribbon, click on Data Validation in the Data Tools section, and then choose Data Validation from the drop down menu. The Data Validation dialog box will open, and should default to the Settings tab.

click to enlarge

Under Validation Criteria, choose List in the Allow drop down menu. Click inside the Source box and then select the list you created on your worksheet using the mouse. You can move the Data Validation box out of your way by clicking the top of the box, holding down the mouse button, and dragging it away if you need to.

Once you have selected your cell range, click OK on the Data Validation box. The cell you selected to contain your Data Validation drop down list will have an arrow at the end of it for users – or for you – to click.

The second way to use a list you created on your Excel spreadsheet is to name it first. Select the list you created on your worksheet. In Excel 2003, go to the Insert menu, point to Name and click Define. In Excel 2007, go to the Formulas tab of the ribbon. Select Name Manager and click Define Name. Type a name for the list in the Names in Workbook box and click OK.

click to enlarge

Next, select the cell that you want to contain the drop down list. Following the same steps listed above, get to the Data Validation dialog. Choose List again, only this time, enter = and the name you gave your list, then click OK.

click to enlarge

The final way to make a drop down list in your Excel spreadsheet does not require a list on any worksheet. Just click on the cell where you want the drop down list. Once again, go to the Data Validation dialog box and choose List. Now type the options for your list directly into the Source box, separating each one with a comma. When you are finished, click OK.

The final way to make a drop down list in your Excel spreadsheet does not require a list on any worksheet. Just click on the cell where you want the drop down list. Once again, go to the Data Validation dialog box and choose List. Now type the options for your list directly into the Source box, separating each one with a comma. When you are finished, click OK.

click to enlarge

The last method allows you a bit more control. When you create a list on your spreadsheet for the Data Validation tool to refer to, users can type a word into the cell as long as it is in the list. When you create the list directly in the Data Validation box, users can still type in a word, rather than using the drop down button, but the list will be case sensitive.

One other point to remember is that users will not know there is a drop down list ahead of time. The drop down button won’t appear until they click on that cell.

For more tips and tricks, take a look at the other items in Bright Hub’s collection of Microsoft Excel user guides and tutorials. Find out how to use the various lookup and reference functions, learn about constructing charts and graphs, read up on saving Excel objects as images, and more. Additional items are being added on a regular basis, so be sure to check back often.