This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using Data Validation in Microsoft Excel Part three of a three-part series of blogs
If you're constantly frustrated by other people entering the wrong values in your spreadsheets why not learn how to use data validation to prevent them? This blog series explains how you can control what other people can do to your spreadsheet. Validating Cells in Microsoft Excel Input Messages and Error Alerts Creating Drop Down Lists in Cells (this blog)

Using Data Validation in Microsoft Excel

Part three of a three-part series of blogs

If you're constantly frustrated by other people entering the wrong values in your spreadsheets why not learn how to use data validation to prevent them? This blog series explains how you can control what other people can do to your spreadsheet.

Validating Cells in Microsoft Excel
Input Messages and Error Alerts
Creating Drop Down Lists in Cells (this blog)

Posted by Andrew Gould on 28 February 2012

In this blog

Creating Simple Drop Lists
Using Your Drop Down List
A Note for Excel 2007 Users

Creating Drop Down Lists in Cells

One of the coolest things you can do with data validation in Excel is create in-cell drop down lists for your users to select values.

This simple list allows you to select a month name without having to type it in!

Creating Simple Drop Lists

You can create simple drop down lists using the Data Validation dialog box. To do this:

Select the cells in which you want to create drop down lists.
From the ribbon select: Data -> Data Validation.

Use this tab of the dialog box to create a drop list in a cell.

Select the Settings tab.
Choose List from this drop down list.
Make sure the In-cell dropdown checkbox is checked.
You can either type in a comma-separated list like this, or click the small red arrow and select the cells that contain the values you want to see in your list.
Click OK to create your drop down list.

Although you can type a list of entries into the dialog box, it makes much more sense to have those values stored in other cells. This will make it much easier to update the list in future, particularly if lots of cells will rely on the same list of choices.

Using Your Drop Down List

Using a drop down list is as simple as clicking on the cell you have validated, clicking the drop down arrow and clicking on an item in the list.

It's worth mentioning that the drop down arrow won't appear until you have selected the cell.

You can also press ALT + Down Arrow on the keyboard to open the drop down list. You can then use the up and down cursor keys to scroll through the list and the Enter key to select an option.

A Note for Excel 2007 Users

If you are using Excel 2007 (or earlier) then there is one thing to be aware of when creating drop down lists. If you have your list of values typed into cells on another worksheet you won't be able to use the mouse to select them. Instead you have to type the sheet name and cell references directly into the dialog box.

The text in the Source box has to be entered by hand in Excel 2007 and earlier.

One way to get around this irritation is to create range names to refer to your list of cells. You can then use the range name in the Data Validation dialog box instead of the cell references. For example, you could create a range name called ListOfMonths and then in the Source box you can enter =ListOfMonths to refer to those cells (the equals sign is important here).