564 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 two 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.
Input messages and error alerts give your users more information about what values are allowed to be entered into your validated cells. Before you can add either of these things you'll need to select the cells you have previously validated.
There's a neat trick you can use to select all of the validated cells in a worksheet, or all of the cells that have the same validation settings as the one you have selected. To do this:
The Go To dialog box allows you to jump to specific cells in the spreadsheet. Here we need to click the Special... button.
This dialog box allows you to select individual components of cells. The numbered steps are described below.
Now that we have the validated cells selected we can edit the validation settings.
You can create an input message using the Data Validation dialog box. To do this:
Use this tab of the dialog box to create an input message.
You'll be able to see your input message whenever you select one of your validated cells.
Input messages appear near to the selected cell.
Even when you've provided an input message, people will still get things wrong. When they do it's handy to have a more descriptive error message than the default one. To add an error alert to validated cells:
Use this tab of the dialog box to add an error alert.
You'll be able to see your message when you next enter an invalid value in a cell:
If your users are still getting it wrong after seeing this it might be time to hire some new data input staff...
There are three different styles of error alert to choose from. They are:
You can either click Retry to try again with a new value or Cancel to reset the cell to its original value.
You can click Yes to continue and enter the value in the cell.
With this style of message simply click OK to continue with the value entered.
When you add validation to a range of cells it's perfectly possible that some of those cells already contained invalid data. Because validation is only triggered when you actually change the value in a cell it's easy to miss these invalid values. Fortunately, you can ask Excel to highlight invalid data:
From the Data tab of the ribbon select the option shown here.
Any invalid data on the current sheet will immediately be highlighted with red circles.
Without the red circles this type of input error can be tricky to spot.
The circles will disappear when you enter a valid value into the circled cell, or you can clear the circles manually:
Choose this option on the Data tab of the ribbon to remove the red circles.
If you decide that you need to remove validation from cells you can do so using the Data Validation dialog box. To do this:
You can clear validation settings using the same dialog box you used to apply them.
Annoyingly, it's very easy to accidentally remove validation settings from a cell. If you copy a non-validated cell and paste onto one that has been validated you will inadvertently remove any validation settings that you had applied.
The final technique that we'll look at with data validation is how to create a cell drop down list to allow users to select a value rather than having to type it in. Read the final part of this series to find out how to do this, or have a look at the classroom Excel courses that we offer.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street