Now that the validation rules have been set, take some time to experiment with how they work. Add an entry for a new employee in row 46, and make some intentional mistakes while doing so to view the input and error alerts.
- To select cell A46,
Click in cell A46
- To add a 5-digit Employee ID, type:
34424 Enter key
As you type, you notice the input message informing you that the Employee ID must be four characters. When you press Enter, the error alert appears. You have the option to retry or cancel the entry.
- To retry the entry,
The active cell remains A46 and the entry can be edited.
- To add a 4-digit Employee ID, type:
The 4-digit ID is accepted and the active cell is now B46.
- To enter the employee's last name, in cell B46, type:
Hart Tab key
- To enter the employee information across row 46, type:
Wanda Tab key, 10/9/2010 Tab key, 57000 Tab key
NOTE: The currency format has already been applied to Column E, so the dollar sign and comma appear as you type.
When you reach cell F46, the input message appears warning you region must be North, South, East, or West. The region can be selected from the drop-down list or typed directly in to the cell.
- To enter the region, in cell F46,
Click, Click East, Press: Tab key
- To enter the remaining employee information, type:
15 Patoka Road, Murray, KY, 40211 Tab key
The new employee record is completely and correctly entered.
Flagging and correcting data
Data validation rules are only applied to data entered after the rule is put in place. However, pre-existing data can be checked to see if it meets the data validation criteria. You can see at a glance what entries might already be breaking the rules by having Excel flag invalid entries with red circles.
- To see the invalid data, in the Data Tools group,
Click , Click Circle Invalid Data
Circles appear around the invalid data including the cell containing a 5-digit employee ID and the cell containing "Central" as a region. Since our validation rules included the Column A and E headers, "Employee ID" and "Region," these are also circled.
At this point, you have the option whether to fix the invalid data or leave it as it is. For today's exercise, go ahead and fix the data.
- To correct the 5-digit employee ID number,
Click in cell A2, type: 1024, press: Enter key
The employee ID is now the correct number of digits.
- To correct the region labled "Central,"
Click in cell F2, use the drop-down list to choose a valid region.
- To remove any remaining circles, including the column headers, in the Data Tools group,
Click , Click Clear Validation Circles
Identifying cells with existing data validation
When looking at a worksheet, it is not immediately obvious if, or where, data validation rules have been set. You may want to find all of the cells on a worksheet that have data validation rules in place, especially if you did not create the worksheet yourself.
Note: The steps for accomplishing this task are very different for Windows and Mac. Choose the appropriate option below.
- To open the Find and Select tool, on the Home tab, in the Editing group,
- To find the cells that have data validation applied,
Click Data Validation
- To see the Find options, on the Menu bar,
Click Edit, Click Find
Several options for finding specific text and/or formatting are in this menu. The choice to search for Data Validation is under the Go To... option.
- To open the Go To dialog box,
Click Go To...
- To open the Special options,
The Go To Special dialog box opens and lists several options for searching this worksheet.
- To search for cells containing Data Validation,
Click the Data validation radio button, Click
All of the cells that have been set up for data validation are now selected.
Before moving on, it is important to deselect these cells.
- To deselect the cells,
Click any other cell
To save the workbook,
Control key + s