Validating Data in Excel

When entering specific types of data in Excel, it is useful to have the program monitor the input so that illegal values are rejected.

For example, suppose you were typing in a table of values representing scores from a survey and only values between zero and ten were acceptable. You could protect against illegal entries as follows:

• Select the range of cells into which the data entries will be made.
• Choose menu option “Data – Validation…”
• In the dialog box that appears, select “Whole Number” from the “Allow” field and then fill out the remainder of the form as shown.
• Click ‘OK' to finish and test this by entering some illegal values (i.e. more than ten or less than zero)

Messages and Alerts
You can warn your user of the requirements by means of an input message which appears as soon as the cursor is placed in one of the data entry cells. To do this, select the middle tab “Input Message” in the dialog box and provide both a title and a warning message.

When an error is typed, you can explain what the error was by means of the right hand “Error Alert” tab. For example, you can select the “Stop” style, provide a title such as “Data Error” and a message such as “Values must be between zero and ten”.

Try it!

Providing Drop-down Selections
Instead of providing a range of allowable values or items as described above, you could provide a drop-down menu with allowed entries. For example, suppose a cell in your worksheet should receive the name of a state. Since you don't want these spelt in different ways, you could allow a list to appear so that the user can select one from it.

To do this, enter the list in some out-of-the-way range in your worksheet. Then choose “Data – Validation…” and in the “Settings” tab choose the item “List”. In the Source field, enter the address of the range containing the list you entered. Alternatively, you can click the red arrow at the right edge of this field to enable you to select this range with the mouse, and then click this arrow again to return to the dialog box. You can enter input messages and error messages as before.

Experiment with other options in this useful Excel feature.