How to Highlight or Remove Duplicates in Excel 2007?
If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..
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.
Update: How to remove duplicates in Excel 2003?
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.
[...] How to Highlight, Remove or Delete Duplicate Cells in Excel 2007? using Conditional Formatting to remove duplicate cell values (tags: duplicates Excel) [...]
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!
[...] and receive free daily updates on Tips, Tricks and Tweaks.. Long back I wrote an article on how to remove or highlight duplicates in Excel 2007? Though this option is much visible in Excel 2007, it is also possible in Excel 2003 with simple [...]
Good one Filippo … Thanks for sharing this tip. I’m sure many of us will find it useful.
Thanks… It helped me a lot….
Great tips, since long time I was finding any solution to find duplicate records in ms excel.
Does anyone know how to highlight duplicate rows?
I can delete duplicate rows and highlight duplicate cells, but I want to highlight the rows that are exact duplicates of other rows.
thankks
Scott Boelscher, check Filippo Gadotti’s answer above
i would like to isolate the duplicate records from one to another. Is it possible to do so in excel 2003?
thx mate.. though now that i feel it is sooooooo simple, i had hard time FINDING this “simple” solution..
thx agen
Thanks alot. this help me alot
can some 1 please explain me, how to get Vlookup in excel 2003?
THANK U SOOOOOO MUCH!
Thank you for these clear instructions!
The info. was useful..thanks for inst.