Learn How to Highlight Rows in Excel Based on Duplicates

Spreadsheets and graphs on a desk

As you may be aware, Excel’s Conditional Formatting feature allows you to identify duplicates in a column. This technique requires some tech savvy in certain versions of Excel, but only a couple of mouse clicks in Excel 2007 and later. A reader named Chris asked me if it’s possible to extend this functionality to not only identify duplicates in a single column, but further highlight the adjacent cells in the same row. In this article I’ll first cover show how to identify duplicate values in a single column, and then show how to extend the functionality in the fashion Chris is seeking. I’ll do so first for Excel 2007 and later, but will then provide alternate instructions for Excel 2003 and Excel 2011 for Mac.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.


Please login or register to join the discussion.

Dec 7th 2016 08:47

Hi David,

Thanks a lot for the above examples.

Could you please help me out with similar issue I am facing.

I have 63000 entries on Sheet 1 and I need to find 148 entries from those 63000 entries. Is it possible to highlight the cells?

Sorry I forgot to add 63000 entries in Sheet1 and 148 entires in Sheet2.


Thanks (2)
By mwlee
Sep 5th 2017 17:00

Hello. Normally, I have a simple sheet of 2 columns with various values and names in which I need to find duplicates. The easiest way was to use conditional formatting to find the duplicates, which I had done for years. Unfortunately, it no longer works. Either it highlights nothing or randomly highlights cells that I know are not duplicates. I am currently using Excel 2013. Do you know how to get it to work again? Thank you.

Thanks (0)