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

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

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

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

4. Select Data validation.
5. To select cells with the same validation settings as the one you have selected choose Same.
6. 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.

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

3. Select the Input Message tab and make sure the Show input message checkbox is checked.
4. Enter a title for the message here.
5. Enter the text of the message here.
6. 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 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.

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

3. Select the Error Alert tab and make sure the Show error alert checkbox is checked.
4. Choose a style of error message (more on this below).
5. Enter a title for the error message here.
6. Enter the text for the message here (you can type in rude words but we've managed to resist doing that here).
7. 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:

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.

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.

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.

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:

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.

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.

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

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

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, or have a look at the classroom Excel courses that we offer.