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.
Validating Cells in Microsoft Excel
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.
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.
The Example We'll Use
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:
- Select the cells you want to apply validation settings to.
Here we're selecting the three rows of cells that contain costs.
- From the ribbon select: Data -> Data Validation
You can either click the top half of the button, or click the drop down arrow and choose Data Validation…
- Complete the dialog box as shown below:
Use this dialog box to configure your validation settings. The numbered steps are described below.
- Select the Settings tab.
- Choose the type of data that you want to allow in the selected cells. Here we've selected Whole number, although we could also have selected Decimal for this example. The option you choose here affects the subsequent options on this dialog box.
- Choose how to restrict the numbers you want to be entered into these cells. Here we're saying that the numbers entered into these cells must be less than or equal to the value we enter in the following text box.
- Enter values to control the range of numbers allowed. Here the Maximum value allowed in the selected cells will be 0.
- Click OK to validate the selected cells.
Testing that Validation Works
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.