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.
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.
- Select the list that you want to search for duplicates
- Click the Data tab on the Ribbon, and then click the Remove Duplicates button from the Data Tools group
- 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
- Click Ok
- The duplicate records are removed and a message box appears informing you of how many were exterminated.
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.
- Select the cell range you want to check for duplicates
- Click the Home tab
- Click Conditional Formatting > Highlight Cell Rules > Duplicate Values
- Select a format you want to use for the records from the drop list. Click Custom Format for more formatting options
- Click Ok
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.