In the previous version of Excel, deleting duplicate cells or highlighting was a feature known only to a pro but with Excel 2007 from MS Office deleting duplicate cells or even highlighting is an easy task.
Let us see how to delete duplicates or highlight them one by one.
How to Remove or delete duplicate cells in Excel 2007?
Select the columns or rows where you want to find the duplicate cells.
Go to the Data tab and under data tools, click Remove duplicates. This would remove duplicate cells and their corresponding rows.
But most of the time, it deletes all the corresponding data in the rows and you may want to review the duplicate cells before deleting it. In this case highlighting the duplicate cells helps.
So how to highlight the duplicate cells in Excel 2007?
Again, select the cells that you want to highlight. Under the Home tab, under Styles, Click Conditional Formatting. Go to Highlight Cell Rules and Click Duplicate Values.
Here you can either highlight the duplicate cells or the unique cells. Choose your selection and format styles. Click Ok.
Now depending on your choices all the duplicate cells will be highlighted, you may choose to review and then delete duplicate cells based on the tips above.
I have been using this trick for quite some time
after generating automated reports that sometimes miss out on dups. Nevertheless this is why I like the Office 2007 Suite (I use Office 2007 Ultimate Edition Suite) since it packs in tons of useful features which other editions do not have.
@Keith, I have the enterprise edition which I bought from my University. It is more like the Ultimate edition and I love it.
A good tool indeed!
Let me give a suggestion for those who want to find duplicates within the combination of columns!
Create a sort of “Hash column” with the “=CONCATENATE()” function and apply the conditional formatting to it.
For instance, if you have a spreadsheet with 20 columns and you want to find the rows with the same information in, lets say, columns A, K and X.
Go into the next column available (21st) in the first line and put the function “=CONCATENATE(A1,K1,X1)”. Drag it down and excel will create the same formula for all the other rows.
Apply the formatting and voilà! Duplicated rows about those columns!