Hard-core table types know that every once in a while, despite the utmost caution, a duplicate value slips into a table. Fortunately, Excel has tools that let you find duplicates, wherever they're hiding, and remove them.
It's not too hard to fish out these duplicates—one option is to use sorting (described earlier) on the column where you suspect a duplicate exists. Then, if you spot two identical values, you can delete one of the table rows (Editing a Table). Of course, in order for this technique to work, you have to be ready to scroll through all the records and check each one. In a supremely long list, that job could take some time.
Fortunately, Excel has another solution—conditional formatting. You've already used it to highlight important data (in Chapter 6), but you can also use it to make repeating values stand out like sore thumbs. Here's how:
Select the table column (as described on Selecting Parts of a Table) that you want to check for duplicate values.
For example, you could select the Product ID column to look for products that have the same ID value.
You can highlight more than one column, but if you do, Excel highlights identical values that appear in more than one column. For example, if the same number appears in the Product ID column and in the Price column, Excel highlights it even though it isn't really a duplicate.
Choose Home→Styles→Conditional Formatting→Highlight Cells Rules→Duplicate Values.
When the Duplicate ...