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.

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

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.

Input Messages and Error Alerts

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.

Selecting Validated Cells

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:

  1. Select any cell that contains the validation settings you want to change.
  2. On the keyboard, press CTRL + G
GOTO dialog

The Go To dialog box allows you to jump to specific cells in the spreadsheet. Here we need to click the Special... button.

 
  1. Click Special... and complete the dialog box as shown below:
Special dialog

This dialog box allows you to select individual components of cells.  The numbered steps are described below.

 
  1. Select Data validation.
  2. To select cells with the same validation settings as the one you have selected choose Same.
  3. Click OK to select the validated cells.

Now that we have the validated cells selected we can edit the validation settings.

Creating an Input Message

You can create an input message using the Data Validation dialog box.  To do this:

  1. Select the cells to which you want to add an input message.
  2. From the ribbon select: Data -> Data Validation.
Input message

Use this tab of the dialog box to create an input message.

 
  1. Select the Input Message tab and make sure the Show input message checkbox is checked.
  2. Enter a title for the message here.
  3. Enter the text of the message here.
  4. Click OK to create your input message.

You'll be able to see your input message whenever you select one of your validated cells.

Input message

Input messages appear near to the selected cell.

 

Customising the Error Message

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:

  1. Select the cells to which you want to add the error alert.
  2. From the ribbon select: Data -> Data Validation.
Error alert

Use this tab of the dialog box to add an error alert.

 
  1. Select the Error Alert tab and make sure the Show error alert checkbox is checked.
  2. Choose a style of error message (more on this below).
  3. Enter a title for the error message here.
  4. Enter the text for the message here (you can type in rude words but we've managed to resist doing that here).
  5. Click OK to create your error alert.

You'll be able to see your message when you next enter an invalid value in a cell:

Custom error

If your users are still getting it wrong after seeing this it might be time to hire some new data input staff...

 

Styles of Error Alert

There are three different styles of error alert to choose from.  They are:

  • Stop - This style prevents you from continuing with the invalid data entry.
Custom error

You can either click Retry to try again with a new value or Cancel to reset the cell to its original value.

  • Warning - Use this style of message to warn users of values that are outside of the normal range but not beyond the realms of possibility.
Warning message

You can click Yes to continue and enter the value in the cell.

 
  • Information - Use this style of message to inform users of the effects of entering a particular range of values.
Information

With this style of message simply click OK to continue with the value entered.

 

Highlighting Invalid Data

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:

Circle invalid

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.

Circled data

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:

Clear circles

Choose this option on the Data tab of the ribbon to remove the red circles.

 

Removing Data Validation

If you decide that you need to remove validation from cells you can do so using the Data Validation dialog box.  To do this:

  1. Select the cells you want to remove validation from.
  2. From the ribbon select: Data -> Data Validation.
Clear validation

You can clear validation settings using the same dialog box you used to apply them.

 
  1. On any tab of the dialog box click the Clear All button to remove all validation settings from the selected cells.

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.

What's Next?

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.

 

  1. Validating Cells in Microsoft Excel
  2. Input Messages and Error Alerts (this blog)
  3. Creating Drop Down Lists in Cells
This blog has 0 threads Add post