Microsoft Office Tutorials and References
In Depth Information
Working with Range Names
Understanding Data Validation
Your worksheet works for you only
if the data entered is valuable. Typos and
carelessness can ruin your data. Imagine that you
were paid solely on commission and the payroll
manager made a mistake when she entered your
largest sale of the year. Your wallet would be
directly affected by a data entry error.
Accepting Blank Cells
In addition to these validation options, you
can also decide whether to accept blank
cells in your data. Click to deselect the Ignore
Blank option when you are creating your
validation to prevent your spreadsheet users
from leaving blanks in the data.
Excel provides a Data Validation feature that can
help protect the data in your worksheet. Data
validation helps restrict the kind of data that is
entered into a specific cell, or range of cells.
Data can be restricted in the following ways:
Applying Data Validation
Follow these steps to apply data validation to a
range of cells:
Values: You can specify that whole
numbers or decimals be used, and you can
choose minimum and maximum values.
For instance, a realtor’s commission may
be restricted to a maximum of .07, or 7%,
of the sales price.
1. Select the cells you want Excel to validate.
2. Choose Data > Data Tools > Data Validation
to open the Data Validation dialog box as
shown in Figure 1-28.
Dates and Times: You can require a
specific date, or specify that the dates fall
within a certain range. For example, only
dates within the current year are valid.
Text: You can specify that the data in
these fields is a specific length. For
example, telephone numbers are 10
digits long if you include the area code
and do not include the dashes.
Lists: You can create a list in another area
of the worksheet and then require that all
entries in the validated cell be one of the
items in that list.
The Data Validation dialog box has three tabs.