Microsoft Office Tutorials and References
In Depth Information
Data Validation: Improving Your Entrée to Data Entry
Figure 6–48. Results reported
OK—“1 duplicate values” isn’t grammatically correct (let’s not be too harsh—Bill Gates did drop out
of Harvard, after all), but you get the idea. The second Ed Jones has been removed—but had Ed Jones
Number 2 been say, 37 years old, he’d have remained in the table, because his three fields would no
longer completely duplicate those of Ed Jones the First. And Grace Jones has been completely ignored by
our duplicate removal, as she should be—because only her last name duplicates the other Joneses, and
again, we required a match on all three fields. Thus if you really wanted the table to display just one
record containing Jones, you’d click First Name and Age off in the Remove Duplicates dialog box, thus
confining your search to duplicates in Last Name only —a less restrictive criterion.
Finally, to turn off the table’s status as a table, should you want to do such a thing, click anywhere in
the table and then click Table Tools Convert to Range on the Tools button group. A prompt appears,
asking: “Do you want to convert the table to a normal range?” Clicking Yes turns off the AutoFilter, but
leaves your current table formatting in place, along with the Total row and any SUBTOTAL calculations
you’ve added (though it does subtly rewrite the SUBTOTAL formulas), by substituting the range being
subtotaled (i.e., $I$10:$I$19) for the name of the field (i.e., Average) whose data are subject to the
subtotal.
Data Validation: Improving Your Entrée to Data Entry
Whether you’re an Excel guru or an Excel tyro, your worksheets are only as good as the quality of the
data you post to them. The most elegant formulas and beauteous charts in all of Exceldom won’t work if
the data they crunch are erroneous, and Excel provides you with a collection of ways for preempting—
though not completely preventing—miscues in data entry.
One such collection is warehoused in the Data Validation option, which you can access via the
Data Data Tools ribbon group. Data Validation offers you an assortment of what are, for the most
part, pretty simple ways to restrict the kinds of data you can enter in any range (no table required), and
thus minimize the likelihood of entering the wrong numbers, or even the wrong text.
Using Data Validation
Let’s demonstrate how Data Validation works with a simple introductory example. Suppose you need to
enter state names as per their post office designations—that is, their two-lettered abbreviations.
1.
Select any range and then click the Data Validation button. Its dialog box
appears
2.
Click the drop-down arrow by Allow . You’ll see (Figure 6–48):
 
Search JabSto ::




Custom Search