By Colin Wilcox

The previous column explained how to create lists on worksheets, which is one type of data validation. This column explains how to use other types of validation and create messages to help others enter the data you require. Part two of two.

The previous column explained how to create drop-down lists. Although lists are handy and easy to use, they're just one form of data validation. Put briefly, in addition to creating drop-down lists, you can validate numbers, dates, times, and text. To try the steps in this column, you can use the worksheet that you created for the previous column, or you can use an existing worksheet.

Validate numbers, dates, times, and text

In case you didn't read the previous column, you use the Data Validation dialog box (Data menu, Validation command) to create validation rules and control what users enter on your worksheets. That dialog box also provides a common set of options for validating whole numbers, decimals, dates and times, and text. The following figure illustrates the key point: If you select Whole number, Decimal, Date, Time, or Text length from the Allow list, the Data list provides the options shown here.

For example, you can require a whole number between 1 and 25, a date between 2000 and 2002, or a text string no more than 200 characters long. And don't worry about setting an incorrect rule. Excel won't let you.

 Note   If you select Any value, List, or Custom from the Allow list, the Data list becomes unavailable. I'll explain how to use Any value and Custom later on. See the previous column if you want to know how to create lists.

The following steps explain one way to validate whole numbers. Keep in mind that if you require a whole number, that's what users have to enter. They can't enter a decimal, a date, or text. If you require a date, users can only enter a date, and so on.

To validate whole numbers

1. On your practice worksheet, select a blank cell, and then, on the Data menu, click Validation.
2. Click the Settings tab, and from the Allow list, select Whole number.
3. From the Data list, select between.
4. In the Minimum box, type 1. In the Maximum box, type 30, and then click OK.
5. Try to enter a number larger than 30 in the text box. Excel prevents you from entering the invalid value. You have to either enter a valid number or leave the cell empty.

To validate dates, times, and text

• Follow the steps listed above, but select Date, Time, or Text length from the Allow list. Depending on your selection, the lists, text boxes, and other controls on the Settings tab will change. Use those options to create the validation rules you want. Take a little time to experiment with the choices. It's painless!

Create custom input and error messages

The previous steps reveal a potential problem: Even if you know that a cell contains a validation rule, how do you know which values to enter? One answer is to place instructions in an adjacent cell, but you don't always have room to do that. In that case, you can create custom input and error messages. The input messages appear when the user selects a cell that contains a data validation rule, and the error messages appear when the user enters an invalid value.

 Note   Excel provides a default error message that works well (in my humble opinion), but you really should place instructions in adjacent cells or create input messages. They'll make your worksheets much easier to use and help ensure accurate data.

To create input and error messages

1. Click the cell that contains the validation rule you created in the previous section, and then open the Data Validation dialog box.
2. Click the Input Message tab, and then, in the Title box, type this text: Limited Values.
3. In the Input message box, type this text: Enter whole numbers between 1 and 30.
4. Click the Error Alert tab, and in the Title box, type Incorrect Value.
5. In the Error message box, type this text: You entered an invalid number. Enter a whole number between 1 and 30.
6. Click OK, and then select the cell. The input message appears whenever you select the cell.
7. Try to enter a value greater than 30 in the cell. The error message allows you to either type a valid number or leave the cell blank.

Ensure that your error messages prevent users from making mistakes

When you create an error message, you can select one of the following message styles.

The default style is Stop. If you select any of the other styles, users can enter invalid data. The other styles display either the warning icon or the information icon . Use the warning message style to help your users avoid mistakes, such as entering the wrong kind of data. Use the information message style when you think your users can benefit from some additional instructions. For example, you may want to remind them to enter dates in the mm/dd/yyyy format.

 Note   Regardless of your error message, data validation isn't a completely foolproof way of ensuring that users enter correct values. Users can overcome your validation rules by copying entries and pasting them into your restricted cells. If you need to find invalid entries, see Check worksheets for invalid data, later in this column.

Copy validation rules to other cells

You use the Paste Special dialog box to copy validation rules to other cells.

To copy validation rules

1. Select the cell that you want to copy.
2. Press CTRL+C, or on the Edit menu, click Copy.
3. Select the cell into which you want to paste the rules.
4. On the Edit menu, click Paste Special. The Paste Special dialog box appears.
5. Click Validation, and then click OK.

Check worksheets for invalid data

Because users can defeat your validation rules with a simple copy-and-paste operation, it's a good idea to check the worksheets that others use for invalid data. Follow these steps:

1. On the Tools menu, point to Formula Auditing, and then click Show Formula Auditing Toolbar.
2. On the toolbar, click the Circle Invalid Data button .

Excel circles any entries on the active worksheet that violate your validation rules, like so.

When you find errors, you can change them yourself or send the workbook back to the person who entered the invalid data and ask them to correct it.

Remove validation rules

1. Select the cell or range of cells that contains the data validation settings that you want to remove.
2. On the Data menu, click Validation.
3. Click Clear All, or from the Allow list, select Any value.
4. Click OK.

 Note   If the cell has an input message applied, you must also disable or delete the message. If you don't, it appears whenever users select that cell. Your users then have to take the time to close the message. I'm reasonably sure they'll also become irritated with you in the process. To disable the message, click the Input Message tab, clear the Show input message when cell is selected check box, and then click OK. If you're feeling a bit more ambitious, you can delete the message title and text instead.

About the Custom criteria and using formulas

You use the Custom validation criteria when you want to use data-validation formulas. You follow the same process discussed earlier in this column:

• Open the Data Validation dialog box, select Custom from the Allow list, and then enter your formula in the Source box.

Excel does not provide functions specifically for data validation. Instead, you use the standard functions to control data entry. For example, to ensure that a range of cells in column B contain only text, you would enter this formula in the Data Validation dialog box:

=ISTEXT(B1:B10)

A number of Web sites and good books provide data-validation formulas. Rather than reinvent the wheel, I'll just point you to some of them:

• Excel Data Validation Sample (a sample worksheet from Microsoft, complete with instructions)
• John Walkenbach's Excel user tips and Excel 2002 Formulas
• Custom validation examples at Contextures.com

About the author

• Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, he writes articles and tutorials for Microsoft Data Analyzer.