Excel > Excel 2007 Advanced > Auditing Worksheets

Data Validation

Data Validation allows you to specify what data is valid for a range of cells, helping to prevent errors on data entry. Data Validation also enables the creation of messages to assist the user when entering data and warn them of incorrect enries.

Creating a list of options

Using Data Validation you can create lists of items for the user to select from. This will prevent spelling mistakes, typos and usually speed up data entry.

1. Select the range of cells you want to validate
2. Click the Data tab on the Ribbon
3. Click the Data Validation button from the Data Tools group
4. The Data Validation dialogue box appears.

Click the drop list arrow for the Allow field and select List



5. In the Source field, either type the entries for the list seperated by commas, or select the range of cells holding the required entries

Ensure the In-cell dropdown checkbox is checked so that the list appears as a drop down menu

6. Click Ok

Data Validation is applied to the selected cells.

Now when the user clicks on a cell to enter data, the list of entries will appear.

If the user types an entry into a validated cell that does not match an entry in the list, the Data Validation error message appears

Excel messages are not known for their user friendly, easy to understand explanations, and this message is no different. However it is possible to create your own messages.

Data Validation messages

You can create your own input message (message that appears when a user selects a cell for data entry), and error alert (message that appears when a user enters invalid data.

1. Select the range of cells with the Data Validation applied
2. Click the Data tab on the Ribbon
3. Click the Data Validation button from the Data Tools group
4. Click the Input Message tab in the Data Validation dialogue box

Enter a Title and Input Message

5. Click the Error Alert tab

Select a Style, Title and enter an Error message

Ensure the Show error alert after invalid data is entered check box is checked so that data entries are validated. This can be unchecked if you want the functionality of the list, but would like users to be able to enter items that are not in the list



6. Click Ok

Now when a cell is selected or invalid data entered, the Data Validation messages will appear providing assistance to the user. The image below shows both messages displayed.

Validating dates and number entries

Validation can be applied to cells to help prevent the entry of incorrect number and date values.

1. Select the range of cells you want to validate
2. Click the Data tab on the Ribbon
3. Click the Data Validation button from the Data Tools group
4. Click the drop list arrow for the Allow field. Options are provided for validating whole numbers, decimals and dates

Select the required option from the list

5. Select an operator from the Data list
6. Complete the necessary fields by entering the value or refering to a cell or range of cells that hold the validation criteria

The example below is validating entries to not accept a date in the past



7. Click Ok

Validate a worksheet

Guides