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.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

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)
By KrisB
Mar 6th 2018 21:11

I got this to work except for if a column contains nothing, then it doesn't count that as a match. I pasted rows that match up on the 5 columns I indicated in my COUNTIFS formula, but I have to remove one of those columns as it typically doesn't have any data entered into it (it's header is "special features," and most entries don't have that.)

If I compare the 4 columns with entries instead of the 5 columns (incl. the one with absence of data), it works.

Here's my formula:

I want to include column "E", but then the conditional formatting rule fails to identify duplicates.

Why doesn't the conditional formatting rule understand that the absence of an entry is still a value (value of "null"?), and compare?

Thanks (0)
By priya01
Mar 24th 2018 05:36

Great information, Thanks :)

Thanks (0)