Microsoft Office Tutorials and References
In Depth Information
Validating Excel Worksheet Content
Setting Up Rules, Messages,
You can set up rules for data entry at any time in
the life of your workbook. In fact, most people
don’t realize they need these helpful features
until there’s a problem—usually through errors
caused by someone not familiar with the data
being responsible for entering it. Of course,
optimally, the creation of rules for various
ranges within a workbook is best done at the
time that the worksheets are first set up, but if
you’re looking to promote more accuracy in an
existing workbook, it’s not too late. To invoke
Excel’s validation tools, follow these steps:
4. The Data Validation dialog box opens, as
shown in Figure 14-23.
You can set up many levels and features for your
validation rules, beginning with what kind of data
will be allowed in the selected range of cells.
1. In the worksheet requiring validation, select
the range of cells to which the validation rules
should apply. This can be a single cell, a
contiguous range, or multiple ranges (selected
using the Ctrl key to add cells and ranges to
5. On the Settings tab, click the Allow
dropdown menu and choose what kind of data
will be allowed in the range of cells you
selected in Step 1. Your choices range from
Any Value to Custom, which requires that
you type a value or select a cell that already
contains the desired value that will be allowed.
2. Click the Data tab and choose Data Validation
from the Data Tools group of the Ribbon.
3. From the drop-down menu shown in Figure
14-22, select Data Validation.
6. Based on your choice in Step 5, using the
Allow list, the next set of fields will vary, but
typically includes a Data field, where you
choose from options such as between, equal
to, or greater than. Make a choice here, and
move to the next field.
Enter the acceptable values, based on your
choice in Step 6. As shown in Figure 14-24,
when a Decimal is required, it must be
between two numbers, so a minimum and
a maximum must be entered.
Choose Data Validation to begin setting
up your data entry rules.