How to Find Duplicates in Excel & Highlight Duplicates If You Need To

Struggling with duplicate data in Excel? Discover four innovative methods to identify and eliminate duplicates, whether in columns, rows, or across your entire dataset.

What You'll Learn:
▪️ Column Comparison: Easily spot overlapping data between two columns.
▪️ Row-Wise Duplication Check: Identify entire rows that are duplicates.
▪️ Formula-Based Duplication Detection: Utilize Excel formulas for dynamic duplicate flagging.
▪️ FILTER Function for Duplicates: Extract only duplicate rows for a clearer view of your data.

In this video I'll show you 4 ways on how to find duplicates in your Microsoft Excel data. We'll find solutions regardless if your data is in the same column, in separate columns and even if you need to find duplicates for a combination of cells. You'll learn how to use conditional formatting to color duplicate values, how to look inside an entire row and compare one row with another and flag it is a duplicate. As a bonus you'll learn how to use a dynamic formula to flag duplicate entries with an icon or returns just the duplicate entries.

00:00 Find and Remove Duplicates in Excel
00:33 Find Duplicates in Columns
01:43 Find Duplicate Rows
03:09 Excel Formulas to Find Duplicates
06:37 Use Formulas to Return Duplicate Values
08:07 Wrap Up

🎬 LINKS to related videos:

🚩Let’s connect on social:

This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#Excel

[thinkingitthrough]

She is hands down the best person for excel problems. It's the way she explains it. Let's face it, when you come to her videos, you usually have a problem, and you're usually on a deadline.
She gets right to it.

[johnarnold]

Great video.... be careful when combining columns when looking for duplicates. I recommend putting a delimiter between the columns to help eliminate false duplicates. If you have the values AA BB two columns of data in the first row, and A ABB as the second row, they will end up giving you a false duplicate of AABB. Use a "-" as a delimiter to help remove this possibility. AA-BB won't get confused with A-ABB.

[chh]

'Thursday's with Leila' just doesn't disappoint ... even when not immediately useful, the videos still plant ideas for future harvest ... 🌱🌿🌿🌱🌱

At the 8:20 mark " ... I hope you enjoyed this video ... don't forget to hit that 'thumbs up ... " .
There should be another option; 'Two Thumbs Up' ... 👍👍

Thank you ... thank you ... thank you ... 😍😍😍

[drew]

I know this is a little off topic, but for anyone removing duplicates in power query for excel or power bi, there is a trap. Yes, just like in an excel spreadsheet the first unique row found is retained while others are removed. However, the way that first row is defined is the trap. It is not defined as the first row as the data is currently, but the first row when the data was first loaded. So you might do a sort to ensure the rows you want retained are at the top then remove duplicates but this will not give you the correct result. The solution is to include Table.Buffer in the remove duplicate query to reset the state of the data to the current sort order. Then the power query remove duplicates operation will work the same as if you removed duplicates from an excel spreadsheet.

[ukaszlachmider]

It never came to my mind to add emojis in cells! That's really cool and opens up many possibilities to make the sheet more appealing 😊

[JasonW]

This showed on my feed at the perfect time. I'm going to be applying your last example to a data set with around ~10, 000 rows today. This will make it so much easier that what I normally do. Thanks!

[republikadugave]

Good thing with excel is that you never run out of things to do and make videos about

[gospelmoto]

Thanks Leila! exactly what Im looking for. Very timely. 3thumbs up!!!

[TheMasterIsaka]

That ConCat formula is a game changer. THANK YOU

[WilsonC]

This video is great, thanks for making it. You have shown us that "there is no need to invest in costly analytic tools" through your use of Excel for data analysis. That's fantastic, you have my undying loyalty.

[Casualbystander]

fantastic video, straight to the point. thank you very much
Highlight column(s)/cell(s), click Home at the top > Conditional Formatting > Highlight Rules > Duplicate Values

[piggybankaccountant]

I love to use conditional formatting. Thats what I teach beginners at work

[GMod]

Love how you casually added the emoji, but you just created a monster. I'm going to use an emoji in every formula from now on, just to make my day a little more enjoyable. Chances are, I'll be using the middle finger quite often.

[katarzynak]

First ever video of Leila when I finally did not learn anything new :D I am not disappointed though- I was already wondering if there will be ever video when I will already be 'know it all' lady :D :D :D

[ReikiTraditionSchoolofReiki]

superb video easy to understand and follow thank you

[IceHot]

In the last exercise to pull out just the duplicates, an alternative is to use UNIQUE instead of SORT in case you don't want to see the same record multiple times over.

[cycleSCUBA]

Excellent guidance and this will help me so much in my work as I've to clean up ~21k rows of data. Thanks Leila.

[git]

Searching for it yesterday and it's up there😀 Thanks 🤘

[paramveerssachdeva]

Just GORGEOUS. The lady and the formulas, Thanks for sharing once again. :)

[josdilna]

Great video. This helped me after watching all other videos. A life saver!