BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
Posted by Andrew Gould on 28 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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).