Data Validation
Data Validation allows you to set limits to
what data in a certain cell or range of cells.
Criteria can be set to double check cell entries, display a message when
a cell is selected or specify an error message when the entry does not meet the
criteria.
Setting Data Validation
The example above restricts the number of
shares to a maximum value of 200. Data
Validation has been set to cells B2:B5.
1.
Select one
or more cells to validate.
2.
On the
“Data” tab, in the “Data Tools” group, click “Data Validation”.
3.
Click the
“Settings” tab.
4.
Change the
“Allow” option to the constraints required.
In this example they will be “whole numbers” and not decimals.
5.
In the
“Data” box select the comparative operator.
6.
In the boxes
below enter the maximum or minimum value.
7.
Click on the
“Ok” tab.
8.
Click on the
“Input Message” tab. This options
displays an input message reminding the user of the constraints that have been
set to the cells.
9.
Type on the
“Title” and “Input Message” box.
10. Click on the “Ok” tab.
11. Click on the “Error Alert” tab. An error message is displayed warning that
invalid data has been inserted.
12. The “Style” of error message can be changed.
a. Stop - will prevent any invalid data being
entered in the cell(s).
b. Warning
- displays a warning message but invalid data can be entered.
c. Information - displays an information message
allowing invalid data to be entered.
13. Type on the “Title” and “Error Message” box.
14. Click on the “Ok” tab.
Checking for Invalid Data
When you audit
a worksheet for incorrect entries Excel can identify all cells that contain
values that are outside the limits set by Data validation. These include values that are where typed in
the cells, values that become incorrect because of calculations in formulas and
values placed in cells by macros.
1. Select one or more cells to validate.
2. Click on the “Data” ribbon and select the “Data Validation”
drop-down arrow.
3. Click on the “Circle Invalid Data”.
4. Cells not matching certain validations are circled in red.
Clear
for Validation Circles
5. Click on the “Data” ribbon and select the “Data Validation”
drop-down arrow.
6. Click on Clear Validation Circles.
