Excel - Find & Highlight Duplicate Rows - 3 Methods | Conditional Formatting

Microsoft Excel can find duplicates easily with Conditional Formatting. The issue is Conditional Formatting finds duplicates based on the cell value, but I want to find duplicate rows. I use three methods in the video to find duplicate rows. I start off with CONCATENATE, then use the TEXTJOIN function, and finally COUNTIFS with no helper column. To highlight the rows I use Conditional Formatting using a formula.

Chapters:
00:00 Introduction
00:14 Duplicate cells
00:58 Conditional formatting works on cells
01:49 Method 1 - Concatenate
02:39 Method 2 - TEXTJOIN
03:47 Conditional formatting entire row
05:23 Method 3 - COUNTIFS with no helper column
06:14 Closing remarks

CONCATENTATE in Excel
Use CONCATENATE, one of the text functions, to join two or more text strings into one string. You can use an ampersand also. Example: cell A2 has Chris and cell B2 contains Menard. =a2&" "&B2 will return Chris Menard in cells C2. Same for row 3. In cell C3, could use =CONCATENTATE(A3," ",B3) to get Chris Menard

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[talhayamin]

Thanks Chris, you're a lifesaver.

[VenomQuill]

This was incredibly helpful, simple, and to the point! Thank you so much!

[hcajeffrobinson]

I've been looking for a way to highlight rows and not just cells...THANK YOU!

[avareinstein]

Was super helpful!!!! Had a spreadsheet with 200+ data elements. Developer said it was good go. Checked in Excel for duplicates and came back with 500+. He actually said "send me a duplicate." I color coded them :).

[TheAlexanderReid]

Thank you. I am a real estate agent pulling data and this helped save so much time

[CatsofAmsterdam]

you're a genius! thanks for this incredible tutorial. you just saved hours and hours of tedious work

[aymeetolander]

I enjoyed your video a lot, it was direct to the matter, your explanation was clear, and you presented really good ideas. Thank you!

[prakharshukla]

Thank you so much for this, I was looking for this everywhere!!!

[tutsecret]

Thank you Sir, bless your heart. I like the method join. I saw other videos methods out there and I got confused.

[karnkraftverk]

Many thanks, method 1 worked fine for me

[vedantamohapatra]

Thank you so much. I was looking for this.

[SimonRoos-rg]

Great video. I agree with Jacob's comment. Using method 2, the TEXTJOIN has a limit of 255 characters which is disappointing as I have 49 columns in my dataset which exceeds the 255 limit

[muhammadkamran]

Your textjoin formula, give me new idea, new helper column with the help of Vlookup formula solved my problem to some extent..great. No doubt some tricks give new idea..like it, Regards

[tutsecret]

What if in column has the word transferred and other texts. So bow it would highlight entire rows. Should I do ="transferred" instead of >1 it's not about to highlight duplicates, it's about to highlight all the rows and columns. When the column Comments contains the word "transferred nurse Mary to Lisa" because the rows with transferred will be appended or copy and pasted to a database. I love your videos. This one is one of my favorite.

[dittprashant]

absolutely loved it, cant thank enough!!!

[hockeyjss]

Hi Chris, how do we highlight values which duplicate but not highlighting one value among the duplicates considering one to be unique. For example Consider A1:A10 have same values and I only want A2:A10 to be highlighted, thereby making A1 a unique value

[ayushkhanna]

This helps!
How do i delete the duplicate rows and keep one instance of that row.
I have 1000 such cases in my dataset.

Please help.

[jithuj]

very good video..can you please do a video on how to highlight duplicates in each row and not across entire row? Thanks

[tutsecret]

It's cool. I noticed that when I delete the duplicates and start putting duplicates for other names, the formula does not work. I have to go to the formula and adjust the number rows/range. So to avoid this, do I have to convert range rows into table format to prevent this?

[RaviRaj-zvbh]

Hi sir, your video helped me :) i have 1 question, please help.

I highlighted duplicate content, now

1. i want to sort rows which are not highlighted.
2. i want to match highlighted content from column A to B

Thank you