Prevent Data Input Errors in Excel Using Data Validation Feature
Have you ever needed to control what type of data is entered into an Excel Spreadsheet?
Excel’s Data Validation tool can help you do just that.
This article describes how data validation tool works and demonstrates different data validation techniques you can use to prevent data input errors.
Data Validation is an Excel feature that allows you to restrict the type of data or the values that users enter in a cell. This amazing feature ensures data integrity by enforcing users to enter valid data. You can also write your own message that appears before entering data that informs users with valid inputs and error messages that appear in case of invalid data input.
There are different techniques available under the Data Validation dialog box. In this article, we will go through a few examples to learn more about them.
Example 1 – Drop-Down List
You may have found yourself in a situation where you wished you could have simply picked a value for a cell from a drop-down list rather than having to enter it manually multiple times.
In this example, we have a table full of employees and the department they belong to. In the department type column, we would like to have a drop-down list from which we can simply select an appropriate option (type of the department).
1. First, we will create a list of options for our drop-down list. The easiest way to create and maintain list of options, is to keep them on a worksheet. You can do this on the sheet that contains the drop-down list, or on a different sheet. For this example, the list of options will be stored on another tab.
2. Next, we will name the range for the list of options we just created. This named range will be completely dynamic. It will adjust automatically if options are added to or deleted from the list.
To name the range, select all the options and click in the name box, which is located on the left side of the formula bar.
Type a one word name for the list without space, e.g.“DepartmentOptions” and press Enter key to complete the naming process.
3. Now that we have created a named range, we can use it to create the drop-down list we wanted.
Go to our main tab where the employee table is located.
Select the cells in which you want the drop-down list to show up (in this example, B2:B10).
From the data tab, click Data Validation.
Now, from the Allow drop-down list under settings tab, select List.
Click in the Source box, and type an equal sign, and the list name (DepartmentOptions).
OR
Press F3 key, to see the list of names.
Click OK to close the Data Validation dialog box.
Now, click on one of the cells and click on the drop-down arrow to make a selection.
Example 2 – Whole Numbers within Limits (Restricting Data)
Data Validation can also be used to restrict the entry to a whole number and assign a limit to the numbers that can be entered.
In this example, we will create a restriction for student marks. Marks must be between 0 and 100. Here, we will create an Input Message for user when the cell is selected and Error Alert if an invalid data is entered.
Here is a sample input message we can create to guide our users when a cell is selected.
Now, if we try to enter 102 marks for David, it will show us an error alert (sample which I have created for this example).
In Excel, you can choose from three types of error alerts to warn the users:
(Image source: support.office.com)
You can customize the text that users see in an error alert message. If you choose not to do so, users will see a default message from Excel.
Example 3 – Text of a Specific Length
Text Length rule is fairly straightforward. You can limit how many characters can be entered into the cell. For example, here we have a column for phone numbers, which must be exact 10 characters long. We can use Text Length rule under data validation to control the data inputs by users.
Now that we have set the rule, we can test it. Enter 10 digit phone number in a cell. If you try to enter less than or more than 10 characters, it will warn the user to correct the entry.
Other Data Validation rules available are as follows:
Any Value to remove any previous restrictions that allow the user to enter anything he desires into the cell
Decimal to restrict the entry to a decimal number that falls within a certain range
Date to restrict the entry to a date that falls within a certain range
Time to restrict the entry to a time that falls within a certain range
Custom to restrict the entry to the limits specified by a particular formula entered in another cell of the worksheet
