560 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 one 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.
Data validation is all about controlling what values can be entered into cells on a worksheet. Validation is particularly useful when you give workbooks to other people for data entry and you want to restrict the values they can enter into a sheet.
Obviously data validation is just one of the many topics that we cover on our courses in Excel.
This blog explains how to apply validation settings to cells, how to provide users with instructions, how to create useful error messages and even how to create fancy drop down lists in cells. You can also watch a video version of this tutorial, and download the file used to demonstrate these techniques.
To demonstrate data validation we're going to use a simple business model for a chocolate shop. Part of the model is shown in the image below:
This is the model we'll use to demonstrate the techniques.
In this model we have an Inputs sheet with a large number of input cells (the blue shaded cells in the example above). Our job is to control what values can be entered into those cells, starting with making sure that we can only have negative numbers in the cells that contain costs.
To validate cells:
Here we're selecting the three rows of cells that contain costs.
You can either click the top half of the button, or click the drop down arrow and choose Data Validationï¿½
Use this dialog box to configure your validation settings. The numbered steps are described below.
When you've applied validation settings to cells it won't be obvious that you've done so until you try to enter an invalid value.
Here we're entering a positive number into a cell that is only allowed to have negative numbers. When we press Enter we see the message shown below.
We can either click Retry to try entering another value, or Cancel to set the cell back to its original value.
It isn't a particularly descriptive error message and nor was it obvious that the cells had been validated when we attempted to enter a value. We can give our users much more help by adding input messages and customised error alerts to our validated cells. The next part of this blog series explains how to do just that.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street