Targeting and Trapping Errors in Excel with Excel’s Data Validation Features

In this article

In my younger years, I had a number of data entry related jobs.  The constant repetition of entering numbers and values were always prone to some mistakes.  Thank goodness for programming that targeted and trapped those mistakes during the data entry process.

Excel was created to handle a large amount of data.  Beginning with Excel 2007, it has 1,048,576 rows and 16,384 columns.  Data entry of large worksheets will be prone to mistakes along the way.  Good news, Excel has a means of targeting and trapping those mistakes.  This post will showcase the data validation features found in Excel 2010.

The entry of correct data can be ensured in a worksheet through data validation.  Data validation is the process of creating a set of rules that determine what users can enter in a specific cell or range.  Each validation rule defines the criteria for the entered data within the cells or ranges.  You can specify the type of data allowed (for example, whole numbers, decimals, dates, time, text, and so forth) as well as a list or range of acceptable values (for example, integers between 1 and 100).

Data validation can also interact with the user through visual messages.  Error Alert Messages can be created that will appear if a user tries to enter a value in the cell that does not meet the data validation rule.  Excel also has a feature that will give a user a clue to what is acceptable prior to entering values.  An Input Message can automatically appear upon entering a cell and will specify the type of data the user should enter into that cell.

Validating Data Entry

The office manager of a regional office hired a temp worker to enter the weekly expense report for the sales department of 75 employees.  The manager wants to ensure that the correct values will be entered into the designated cells of the report.  The table below shows the data validation rules for maximum expenditures for Total Expenses, Meals and Entertainment.

Maximum Allowable Values

Weekly Expenses	Sales Rep A	Sales Rep B	Sales Rep C	Manager
Total Expenses:	$500.00	$750.00	$1,000.00	$2,000.00
Meals:	$30.00	$50.00	$75.00	$100.00
Entertainment:	$100.00	$150.00	$200.00	$300.00

 

Each of the validation rules specifies maximum values allowed and the validation criteria (job title) used when entering a value in a cell.   The above table will be the foundation of the data validation rules that the office manager will create in an Excel worksheet.  The validation rules will target the three kinds of weekly expenses for the four sales department positions.  The maximum allowable values will be trapped upon data entry into the appropriate cells in the worksheet.

Create a Data Validation Rule

The temp worker will be using the following worksheet.   Only a portion of the 75 employees is displayed below.  At the top of the worksheet is a re-creation of the data validation table.

 

Prior to turning the worksheet over to the temp worker, office manager has gone through the steps of creating the data validation rules in Excel.  Let us follow the manager’s steps to create the data validation rule for the weekly expense of Meals for the Sales Rep A employees.

 

1. Select the range C9:C12, the Sales Rep A Meals values
2. Click, Data –>Data Tools–> Data Validation
3. Click the Settings tab
4. Choose the Decimal from the Allow: drop-down list.
5. Click the Data: drop-down list and Select, less than or equal to
6. Enter 30.00 in Maximum:
7. Click OK

 

Related: Excel Can Do Queries – A Step by Step Guide

 Test the Validation Rule

David Kelly reported $50.00 for Meals.  When the temp worker entered fifty dollars a dialog box was presented.  The box stated; “The value you entered is not valid.” with the option to retry, cancel or obtain help.  The data validation upholds the rule that the office manager had created.  Sales Rep A maximum expense for Meals is $30.00.

The user has the options to Retry, Cancel or obtain Help.  The error dialog box that was presented is Excel’s standard error dialog box.

Using the above process the office manager will apply the specified rules to the entire worksheet.  When all the rules have been applied, there will be twelve data validation rules.  The rules will target and trap potential data entry mistakes that the temp worker might make.

User Friendly Messages

The standard error message; “The value you entered is not valid”, is not “user friendly”.  A user friendly message would inform the temp worker of valid expense amounts.  In the case of Meals for the Sales Rep A employees, the message could state that the maximum amount is $30.00.

Data validation in Excel allows you to create a customized error message to replace the standard message.  The office manager needs to create an error message that would be activated when the temp worker has entered an invalid value.

1)    Select cells C9:C12

2)    Click:  Data à Data Tools à Data Validation

4)    Type:  Invalid Meals into the Title box3)    Click the Error Alert tab

5)    Click in the Error message and type:
Sales Rep A meals no larger than $30.00

6)    Click OK

 

Now when the temp worker tries to enter $50.00 for David Kelly’s Meals expense, the error message will look like this:

Did you notice in the “Error Alert” tab of the Data Validation, that the standard “Style” was “Stop?”  The stop style shows a big “X” with a red background.  There are other styles that you can use when creating an error message; “Warning and Information”.

The three Error Alert Messages have two options in common; Cancel and Help.  The Cancel option will erase the value that was entered and leaves the cell active for you to enter a new value.  The Help option will open the large Excel Help window.  The Help is the same as clicking the blue background question mark in the upper right of the Excel menu or clicking F1.

The three Error Alert Messages each have additional options specific to their messages.

Message	Options	Meanings
Stop	Retry	Highlights the entered value, ready for you to type over and make any corrections
Warning	Yes, No	Yes; the entered value is allowed     /      No; highlights the entered value, ready for you to type over and make any corrections
Information	Ok	the entered value is allowed

The Yes and Ok options are the same and the Retry and No options are also the same.

Use an Input Message as a Clue

An Input Message will appear when the user activates or moves into a cell.  The message should give the user a clue to valid values.  Unlike the Error Alert Message which appears after a value has been entered, the Input Message appears before a value is entered.

Back to our temp worker; remember that expense data for 75 employees must be entered.  As the worker enters data, the top portion of the worksheet with the data entry rules will not be visible.  The office manager does not expect the temp to memorize the set of rules.

The solution to memorizing the rules is to use an Input Message as the temp worker moves into a cell that has a rule.  The office manager will follow these steps to create an Input Message for Entertainment expense for Sales Rep A.

 

1)    Select cells D9:D12
2)    Click:  Data –> Data Tools –> Data Validation
3)    Click the Input Message tab
4)    Type:  Entertainment Maximum into the Title box
5)    Click in the Input Message: and type:
$100.00 maximum for Entertainment expense
6)    Click OK

When the temp worker activates an Entertainment cell, the Input Message will look like this:

Using Excel’s Data Validation features will help minimize or completely eliminate data entry errors.  Targeting and trapping potential errors and giving a user some entry clues will make your worksheet easier to use.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.