Microsoft Office Tutorials and References
In Depth Information
Validating Excel Worksheet Content
Only City Is Checked, to Remove Duplicate
Entries in the City Column Only
Marking New Invalid Data
If you have data that’s already entered prior
to setting up validation rules and error
messages, or if you’ve set your Error Alerts
to a Warning or Information (which doesn’t
prevent invalid data from being entered),
you can quickly mark all the cells containing
invalid data. From the Data tab, choose Data
Validation, and then choose Circle Invalid
Data from the drop-down menu. All cells
with invalid data will be marked, and you
can edit them as desired. You can also choose
Clear Invalidation Circles if you no longer
want to see them.
Don’t repeat yourself—unless you want to. Let Excel
look for and remove redundancies in your data.
Looking for Duplicate Entries
Got duplicates? Not sure? They can turn up
frequently, especially when you have multiple
people working on a worksheet, contributing
content. You can make Excel look for and
document them in a handy dialog box, giving you the
opportunity to decide if they should stay or go.
To use this handy feature, follow these steps:
4. Remove the checks next to any columns you
don’t want included in the removal of
duplicates. If, for example, you want to allow
them in all but the City column in a list of
cities that includes State, Population, and
other data about each city, remove the check
next to the columns that contain duplicates
that you want to allow.
1. Select the range of cells in which you want
Excel to check for duplicates.
2. Click the Remove Duplicates button in the
Data Tools group of the Data tab.
3. The Remove Duplicates dialog box opens, as
shown in Figure 14-29, listing each column
in the range of cells you selected.
Is Excel not seeing your column headings?
Check the My Data Has Headers
checkbox and Excel will assume the top row
of your range contains column headings
and will replace the generic Column A
entries with whatever text appears at the
top of each column.