557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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!
You can create simple drop down lists using the Data Validation dialog box. To do this:
Use this tab of the dialog box to create a drop list in a cell.
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 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.
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).
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street