When working in Excel, it is important that data is entered accurately and consistently. One way to ensure error-free data is to create validation rules.
Validation rules make certain that the data complies with specific conditions as it is entered. These rules can be about having data that conform to an approved list of entries, a set length of the entry, time or date range, and more.
It is important to note that validation rules:
work best when users type data directly into cells
do not affect pre-existing data
are applied to the cells, not the content in the cells
can be overwritten by copying and pasting data into the cells or using the autofill handle to enter data
Navigate to the folder where the exercise files were unzipped.
To open the exercise file named Employees.xlsx
Double-ClickEmployees.xlsx
The Employees.xlsx workbook contains a single worksheet named Employee_Information. This is a list of employees including ID numbers, last and first names, hire dates, current salaries, regions, and addresses.
Creating a list validation rule
Let's create a list validation rule. List validation allows users to choose data from a drop-down list. In this case, we want to create a list validation rule that will make sure each entry in the Region column is one of four valid entries: North, South, East, or West.
Validation rules are applied to the selected cells. Therefore, we will begin by selecting the Region column.
To select the Region column, in the header row at the top of the worksheet,
Click
To move to the Data tab, on the Ribbon,
Click the Data tab
To open the Data Validation dialog box,
Click , Click Data Validation...
NOTE: The Data Validation button may look different if the Excel window is a different size. When the window is small, the button may look like this: or .
The Data Validation dialog box opens:
The dialog box is currently showing the Settings tab. On this tab, we can specify what kind of data is allowed in the selected cells. Currently, any value is allowed.
There is also a tab for the Input Message. This is the message users will see when they begin to enter data.
The third tab is the Error Alert tab. This is the alert users will see if they enter invalid data.
To set the allowed criteria to "List", in the Data Validation dialog box,
Click , Click List
The dialog box changes slightly and now includes a Source field. If our list already appears in Excel, we could use cell references as the source. For today's exercise, we will type our list in the Source field.
To select the Source field, in the Data Validation dialog box,
Click the Source field
To enter the list, type:
North, South, East, West
NOTE: List entries are case sensitive and should be separated with commas.
With the list in place, let's create an input message. This message will tell the users what values are valid for the Region column.
To move to the Input Message tab, in the Data Validation dialog box,
Click the Input Message tab
To add a title,
Click in the Title, type: Region
To move to the Input message field, on the keyboard, press:
Tab key
To add the input message, type:
Region must be North, South, East, or West
Now that the input message has been created, let's add the error alert.
There are three options for error alerts: Stop, Warning, and Information. The Stop alert will prevent users from overriding the alert. The Warning alert will ask users if they want to proceed anyway. And the Information alert will simply tell users that they are entering the wrong data but won't prevent it.
To move to the Error Alert tab, in the Data Validation dialog box,
Click the Error Alert tab
To see the Style options,
Click
To choose the Stop alert, if necessary,
Click Stop
To move to the Title field, if necessary, on the keyboard, press:
Tab key
To add the title and move to the Error message field, type:
Region Tab key
To enter the Error message, type:
Region must be North, South, East, or West
To apply the validation rule,
Click
The dialog box closes and the data validation rule is now applied to the Region column. Because the column is selected, we see the input message:
Let's deselect the column and save the workbook.
To deselect the column,
Click in a blank area of the worksheet
Testing a data validation rule
Now that the validation rule has been set, let's experiment with how it works. We'll add an entry for a new employee in row 47.
When cell F47 becomes active, we will see the input message warning us of the constraints of the Region field. Let's test this and see what happens when we attempt to enter invalid data.
To enter invalid data in cell F47, type:
Central Tab key
When we attempt to move from cell F47, the error alert appears:
Let's retry the data entry. This time we'll use the drop-down list.
To retry the data entry, in the error alert dialog box,
Click
To remove the current contents from cell F47, on the keyboard, press:
Delete key
To change the region to North,
Click, Click North
To move to the next column, on the keyboard, press:
Tab key
To enter the address, in cell G47, type:
9066 Highland Road, Lake Villa, IL, 60046
The new employee information is complete.
Finding invalid data
Data validation rules are only applied to data entered after the rule was put in place. There may be previously entered data that does not meet the requirements. The workbook can be checked and the invalid data can be flagged. This data can then be manually changed.
To circle the invalid data, on the Data tab,
Click , Click Circle Invalid Data
Circles appear around the invalid data including the cells containing "Central" as a region. The heading of the Region column is also circled. Because the validation rule was applied to the entire column, the header is considered to be invalid.
At this time, we have the option to fix the invalid data or ignore it. Let's go ahead and fix the data.
To correct the first occurrence of Central,
Click in cell F2, type: East
To correct the next occurrence of Central,
Click in cell F23, type: North
To correct the final occurrence of Central,
Click in cell F36, type: North
To remove the circle, on the Data tab,
Click , Click Clear Validation Circles
Identifying cells with existing data validation
When looking at a worksheet, it is not immediately obvious where, or if, data validation rules have been applied. This is especially true if you did not create the worksheet.
To move to the Home tab, on the Ribbon,
Click the Home tab
To see the Find and Select options, on the Home tab,
Click
To find the cells that have data validation applied,
Click Data Validation
The cells that have validation rules applied are selected. Before moving on, it is important to deselect these cells.
, 
or 
.
, 
, 