This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Validating Cells in Microsoft Excel (this blog) Input Messages and Error Alerts Creating Drop Down Lists in Cells

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.

Validating Cells in Microsoft Excel (this blog)
Input Messages and Error Alerts
Creating Drop Down Lists in Cells

Posted by Andrew Gould on 28 February 2012

In this blog

The Example We'll Use
Validating Cells
Testing that Validation Works

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.

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.

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.

Validating Cells

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.

Parts of this blog
Validating Cells in Microsoft Excel (this blog) Input Messages and Error Alerts Creating Drop Down Lists in Cells

Some other pages relevant to the above blogs include:

Excel classes

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Validating Cells in Microsoft Excel

The Example We'll Use

Validating Cells

Testing that Validation Works

Head office

London

Manchester