Highlight Duplicate Rows with Excel Conditional Formatting

www.contextures... Visit this page for the sample file and written instructions.
With Excel's conditional formatting, you can highlight cells based on specific rules. In this example, we'll highlight the duplicate rows in a table.
The first step is to use the CONCATENATE function to combine all the data into one cell in each row.
Then, a conditional formatting rule is set, to color the rows that are duplicate records.
Watch this video to see the steps.

[@tomcarroll7670]

You have no idea how helpful this is, I have data all the way until column AK, and this has saved hours worth of work!! Thank you!!

[@contextures]

Thanks, Tom, and I'm glad it helped you!

[@PrashantParihar05]

This teacher sent by god

[@contextures]

Aww, thank you Prashant - I appreciate it!

[@brundhakamalasekaran9529]

Video detailing is awesome!! It helped me a lot!! Good work to save time!!!

[@contextures]

Thank you, Brundha! I'm glad that the video helped you.

[@Pankaj-Verma-]

No words to explain how magnificent this channel is. Narration, speed, Tricks everything is incredible.

[@AbdulRasheed-yb2sc]

really sir, Its Awesom

[@contextures]

Thank you!

[@teflondun5510]

Hi is another function I can use instead of dragging my mouse down to copy the =countif? I have thousands of lines to check for duplicates

[@contextures]

If you format your list as a named Excel table, the formulas will fill down automatically. My video shows the steps: kzbin.info/www/bejne/mabNmaSmm7eYjck

[@kjaan]

I have debits and credits in one column with more than 1000 entries, how do I match the debit and credit in excel 2010. The debits are positive number and credits are negative number.

[@waqasmaqbool3356]

thanks, practically done this

[@contextures]

You're welcome, Waqas, and thanks for your comment!

[@Travel_brat]

this trick does not work in the big data set where you have many columns

[@anuraghuzefa]

Thank you..very helpful! :)

[@contextures]

You're welcome, Anurag, and thanks for your comment!

[@EkaterineI]

This works only if the number of characters in ALLDATA cells are less then 250... Do you have suggestions if otherwise?

[@Antoneer]

Awesome !!! Thanks

[@contextures]

You're welcome, Antoneer, and thanks for your comment!

[@abdussalamfakir9067]

Alhamdulillah nice

[@xceleration-theexcellearni6074]

I'm sorry but your concatenation can create false duplicates. For instance 71 and 29 becoming 7129 and 7 and 129 becoming 7129 and being flagged as duplicate even though the 2 sets are different. It would work if you were to add a rare special character between each value.

[@Travel_brat]

yes this method is not reliable at all in such conditions, there must be another way to highlight duplicate rows

[@contextures]

@Eka, for longer strings, you could use this formula: =COUNT(FIND($G5,$G$5:$G$11))>1

[@zarablue546]

You're a lifesaver, thank you! ✨

[@contextures]

You're welcome, Zara, and thanks for your comment!

[@contextures]

@krn14242 Thanks,that's a great tip!

[@contextures]

Aw, thanks! Now get back to work. ;-)

[@albrecht3770]

Agree with previous comment. The number of times your voice has drifted me away from work...

[@contextures]

Thanks! I enjoy creating the content.

[@Anthony-si8gg]

thanks!, just saved me hours of work. thanks

[@krn14242]

If you click on the colon and hit F4 it will lock both addresses at the same time.

[@1001TechFacts]

Hello Need help with Excel. I have a column with 20,000 names. For example Smith Jones Williams Etc. How to duplicate each row separately? So I would have Smith Smith Jones Jones Williams Williams Tell me, please, how to do this. Thanks in advance.

[@contextures]

In the next column, add numbers -- 1 to 20000. Then, copy the names and the numbers, and paste in the first blank row below the list of name. Next, select any cell in the number column, and sort A-Z. When you're finished, delete the column with numbers

[@1001TechFacts]

Thanks for replying

[@Anim8orAsylum]

Thanx for sharing Knowledge :)

[@azarnoipatsan]

thank you !!!

[@maxdindar9751]

LEGENDARY....... Thank you

[@contextures]

Thank you! I'm glad you liked the video.

[@crazcomchronicles]

I think you are doing it the hard way. Use conditional format then highlight dups.

[@crazcomchronicles]

nevermind I just viewed the whole video.

[@contextures]

That would highlight any cells that are duplicated in the selected range. For example, lamps, tables, and Big Inc would be highlighted, because they're repeated. This technique highlights the entire row, if the value in column G (the combined data) is duplicated.

[@mattt2657]

Nice job. Thank you.

[@contextures]

Thank you! I'm glad you liked the video.

[@peppazfletcher]

Amazing!! THANKS

[@contextures]

You're welcome! Thanks for your comment!

[@togetherweexcel1525]

Nice. Do check mine and help me improve.

[@rizadil]

Apart from the content I always come here to hear that voice