Handling Duplicate Records in Excel 2007

One of the many improvements in Excel 2007 was its greater ability to deal with duplicate records. In prior versions the only tool available was the Advanced Filter, which would identify unique values and allow us to copy them elsewhere, thus eliminating duplicates.

This hub will explain a couple of new options now available when using Excel 2007.

Remove Duplicates

The Remove Duplicates feature does exactly what its name implies. No messing about, no copying, just plain and simple removes all duplicate records from a list.

It will assume that the first instance it comes across is the unique record; therefore you may need to sort your list first before running Remove Duplicates.

  1. Select the list that you want to search for duplicates
  2. Click the Data tab on the Ribbon, and then click the Remove Duplicates button from the Data Tools group
  3. In the Remove Duplicates dialogue box, check the boxes for the columns you want to look for duplicates in. By default, they are all checked to check for true duplicates
  4. Click Ok
  5. The duplicate records are removed and a message box appears informing you of how many were exterminated.

Remove Duplicates in Excel 2007
Remove Duplicates in Excel 2007

The Undo button will restore the duplicates if you have made a mistake, but you need to notice it immediately.

Using Conditional Formatting

Conditional Formatting in Excel now includes an in-built condition to highlight duplicate records. This can be seen as a softer approach to using Remove Duplicates, as it merely highlights the records, as opposed to eliminating them.

  1. Select the cell range you want to check for duplicates
  2. Click the Home tab
  3. Click Conditional Formatting > Highlight Cell Rules > Duplicate Values
  4. Select a format you want to use for the records from the drop list. Click Custom Format for more formatting options
  5. Click Ok

Use Conditional Formatting to highlight duplicate records
Use Conditional Formatting to highlight duplicate records

Useful Links

Apply Conditional Formatting to the Whole Row

I have been asked many times during my career if it is possible to apply conditional formatting to the whole row rather than an individual cell. Well the answer is yes, lets look at how this is done.

Software training videos

Online training videos for Excel, Word, PowerPoint, Photoshop, Dreamweaver, Flash, JavaScript and much much more..

More by this Author


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working