O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Dealing with Duplicate Rows

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.

Highlighting Duplicates

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:

  1. 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.

    Note

    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.

  2. Choose Home→Styles→Conditional Formatting→Highlight Cells Rules→Duplicate Values.

    When the Duplicate ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required