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.
More by this Author
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
Although Excel’s greatest strength is its ability to analyse huge amounts of data, it is also great for creating forms. We can create formulas, apply validation rules and use techniques to make our forms user...
This hub will explain how to create the scrolling text credits from the Star Wars movies in your PowerPoint presentation. To create the Star Wars credits we will use different animation effects simultaneously. ...
No comments yet.