This time each year, many of us spend hours poring over customer lists updating contact names and address details in order to prepare the annual Christmas card mail merge.
We start by dumping a variety of information into a spread sheet – exports from our CRM system, our Outlook contacts and various lists we have produced in previous years – and inevitably this results in a degree of duplication.
So did you know that Excel offers a handy tool to help identify and remove duplicate rows of data at the click of a button? Here’s how it works:
[themecolor]Highlighting duplicates[/themecolor]
The work sheet below shows duplicate data that you may wish to identify when preparing your Christmas card list.
For example, Jones Brothers have two Directors; do you want to send a Christmas card to each of them or just one to your main contact? Also, Smith & Co has recently relocated from Guildford to Chester, so you probably wish to remove the old address from this list.
To identify duplicate values in the Company column highlight column A then select:
Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values
The following formatting will be applied enabling you to easily identify the duplicate values requiring your attention.
You can also sort the data by text colour, so in a longer list you might wish to bring all duplicate values to the top of the page, making it easier to manage all items together (see below).
[themecolor]Deleting duplicate values[/themecolor]
You may simply wish to delete any rows containing duplicate values without first highlighting these entries for review. To do so, highlight the entire dataset and click:
Data tab > Remove Duplicates
The following dialog box will be displayed. Deselect the column tick boxes as appropriate. Leaving them all selected means that only rows with duplicate values in every column will be deleted. Selecting just one of two columns means rows with duplicate values in those particular columns will be deleted.
Click OK to complete this action.
