Microsoft Office Tutorials and References
In Depth Information
Using Data Validation to Control Data Entry
Using Data Validation to Control Data Entry
Some worksheets are designed for repeated use, with specific cells designated for data
input and others used for formulas that transform those data points into meaningful
results. The danger of such a design, of course, is that you (or someone using a worksheet
you designed) might accidentally enter inappropriate data in an input cell. The result can
be summed up in four words: “Garbage in, garbage out.”
The cure is to assign a data-validation rule to one or more of those input cells so that they
accept only the proper type of data or values in a specific range. Using validation rules,
you can restrict entries to those that match a list of approved items (budget categories or
department names, for example) and even attach a drop-down list from which items are
picked. That strategy eliminates the possibility that someone will misspell an entry and
inadvertently mess up a report based on that data.
Each data-validation rule is made up of three parts that correspond neatly to the tabs of
the Data Validation dialog box. The Settings tab defines the criteria for valid input data; the
Input Message tab allows you to create an optional message that provides helpful
information when the cell is selected; and the Error Alert tab specifies what a user sees when she
tries to enter invalid data.
To get started, first select the cell or range for which you want to restrict data entry, click
Data Validation (in the Data Tools group of the Data tab), and define your rule using the
options on the Settings tab. Figure 14-6 shows this dialog box after we filled in the
validation criteria Excel will use when deciding whether to accept or reject specific input values.
Choose a value from the Allow list to restrict data entry to a specific data type and then use
additional criteria to define acceptable values.
Figure 14-6 The validation criteria defined here use formulas to restrict allowed input to dates
that are between the current date and 30 days ago. Any other input will be rejected.
Search JabSto ::




Custom Search