Thursday, October 6, 2011

Duplicate Data

Duplicate Data can be an annoying issue when working in Excel.  Although it is desirable to use the tools of Excel to simply delete all duplicate data, there are times when it is more advisable to clearly identify these values prior to eliminating them.

For instance, let's say you are working with blended human resources data that lists employees with their home addresses.  If the information in the blended data includes duplicates of employees who have had updates , and shows different addresses for these duplicates, it would be good to have a simple way of doing identifying them before taking action.

Solution?  Conditional Formatting to the Rescue! 

Here is How to do this:

1.  Select the range (e.g. A1:A65).
2.  Choose Format / Conditional Formatting to display the CF dialog box
3.  Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$A$65,A1)>1 in the second box
4.  Click the Format button to bring up the Format Cells dialog box.
5.  Select the Patterns tab, and choose a background color
6.  Click OK twice to return to your worksheet
Alacazam!  If the range contains any duplicate entries, they will be highlighted with the background color you chose previously, providing you with Easy Identification of any Duplicate Data. 
How Cool is that!
So, what are you waiting for?  Give it a try!

No comments: