How to Find Duplicates in Excel & Highlight Duplicates If You Need To
ฝัง
- เผยแพร่เมื่อ 20 มิ.ย. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Struggling with duplicate data in Excel? Discover four innovative methods to identify and eliminate duplicates, whether in columns, rows, or across your entire dataset.
⬇️ Download the file I use in the video from here: pages.xelplus.com/find-duplic...
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:
3 EASY Ways to Find and Remove Duplicates in Excel: • Three EASY Ways to Fin...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
![[FULL EP.69] #เอิร์นจิรวรรณ เปิดบ้านหมื่นล้านทุกมุม ครั้งแรก! l เคหสถานบานปลาย l One Playground](http://i.ytimg.com/vi/_J4_nw8c64o/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/find-duplicates-file
how about if you need to compare from two different excel files?
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!
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.
Thanks Leila! exactly what Im looking for. Very timely. 3thumbs up!!!
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 😊
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.
Thanks for the suggestion, John
Super helpful! Thank you!
Excellent guidance and this will help me so much in my work as I've to clean up ~21k rows of data. Thanks Leila.
Just GORGEOUS. The lady and the formulas, Thanks for sharing once again. :)
Thank you Leila! This is very helpful for me 🙏🏾
This video came in exactly when I needed. Thank you
Leila your videos are really good! Congrats! Thanks for sharing with us!
Thanks Laila. This video was extremely helpful.
You just answered my question from the short. Thank you!!!!
Thank you Leila for this video on the most common need, find duplcates and the four cool ways to get the job done! I have watched your videos over a period, and you have come a long way in terms of presentation, quailty and utility of the videos, that's getting superior by the day! Good luck to you!
Thank you Vijay. Very much appreciate that. Specially for your support during all the this time 🙏
You explain very well and ur videos are informative. You covers maximum possibilities. Thank u.
'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 ... 😍😍😍
ahhh thank you for the two thumbs up. Appreciate it.
We can simply use 'Countifs' instead of 'Countif' + Concatenate to integrate multiple columns while finding duplicates..Great video as always 👍
Great video. This helped me after watching all other videos. A life saver!
Very much helpful to get improved the XL hacks. Thanks Leila. Always watching you. Your presentation of Videos is super marvelous. I got much knowledge from you. Thanks again Leila.
I will try this later. Thank you!
That ConCat formula is a game changer. THANK YOU
Your videos are a big inspiration! just started out my own youtube (from my experience as a data analyst) All the best!
Really nice video and thoroughly enjoyed and learned from it.
Thanks
OMG you are ALWAYS the best!!! Thank you!!!
As always, you made my live much easier! Thank you Leila ❤
Glad it was helpful!
Just pure genius. Thanks.
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.
Glad you enjoyed it!
😭😭 Amazing as always. Thank you so much.
Thank you Leila. You are great.
Very good animation and amazing explanation. Thanks for uploading such a useful content. Keep it up!
Thank you so much, your videos are so informative.
I learned again...thanks
very good playlist, nice work, thanks a lot
Excellent 😊..Thank you 😊
Thanks Leila for the video and for always keeping us abreast with excel advancements. Wondering if there's a formula for highlighting figures that 'knock off each other' in a column. Let's say 12 and -12 or -1,000 and 1,000? Thanks
This is amazing, this what I need right now, thank you sooo much😍
Great! Thank you
Thanks Leila for those tips!
You're so welcome!
Thank you Leila!!!!
Wow! Thank you. It's very convenient and useful
Glad it was helpful!
Thanks for the free knowledge
Very useful..learnt something new... 👍
Leila has saved my life 1000000000000000x thank you!
Great video, very recommended!
Fantastic. As always.
Finally! I’ve been looking for a video like this for so long! I’ve had the idea but I didn’t know how to start, thanks!!
Glad I could help!
😊Very Good 👌 Thank You for sharing
Great video!
Thank you so much. Amazing 👏
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
Thank you for your useful conclusion~
same, I'd like to share another quick way to find duplicates with you, and hope it can be helpful too!
you just need to open your file in WPS Office> select the range of your values>click “highlight duplicates”> click “set”
then all the dupplicates will appear!
Great and really helpful and did my work
thanks for video
You are great!, I love your class!!
Amezing mam.. superb
Good video! Your tips are a great way to keep me updated with excel. Thanks for sharing.
My pleasure, Audrey!
Good thing with excel is that you never run out of things to do and make videos about
That's true!
Super dear,thank's too much❤
Really helpful
I needed this
Very Nice and practical.😀
This trick is very helpful in my work.
I love to use conditional formatting. Thats what I teach beginners at work
sincere thanks
Really excellent
This is very helpful 👌👌
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.
Thanks for this ☺️
I did not know this. thank you for sharing this info.
Excel also has a "remove duplicates" feature on the Data tab. No formulas needed except creating unique keys when needed.
@@MrSupernova111 You wouldn't use that if you were using power query though.
@@drew3030 . I use both depending on the complexity of the project. I don't like to limit my options.
You are always awesome ma'm. Great tutorial 👍
Thank you, Leila
You Rock Leila!
Thank you Leila
Excellent
❤
Thanks!
Excellent and very helpful video. Can you recommend a method that allows a comparison of values, let’s say staff names, that are in 3 or more columns?
Thank you 🙏
Thank you very much! This is really helpful! It's amazing to see how many things we can do in excel 😃
Glad you like it!
YOU ARE PERFECT, AND THAT'S MORE THAN 1!
Valuable video, very useful.
How to retain original value without colouring?
Thank you
Thanks 🙏🏽
Ossum!!👌
thanks a lot, u saved my life (Y)
thank you and God bless :)
Thank you Leila for this great video. Another way that I have to find duplicates is Pivot Table
Great tip Nelson! Now we have 5 ways.
Thank you for sharing
My pleasure
It's always a treat to watch and learn from your videos. Just want to know how to create dummy data and query the data to apply formulas in different ways. If you can make a tutorial on same that would be great and I assume maximum viewers have similar concern while practicing ... thanks
If you want create dummy data for numberic =randbetween(100,200)
Wow!!!!!! Brilliant
These videos really help me better manage the random requests my leadership comes up with. Thanks for sharing!
Glad to help!
Great, many many thanks
My pleasure!
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.
Perfect 👍
You are geniuses
Thanks 🥳
Wow brilliant
very very useful👍👍👍👍
Thank you Madam
Thank you Leila for this nice video 📹 👍
You're welcome 😊
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
🙌
Nice video
Searching for it yesterday and it's up there😀 Thanks 🤘
Its a Discovery.. Thank You for the Valuable Information. I Have Been Using CountIf with start cell as Absolete and 2nd Cell as Relative in a Range.
A1:A100 Has Unique and Duplicate Values
Formula For B2 =CountIF($A$2:$A2,$A2)
Formula For B3 =CountIF($A$2:$A3,$A3)
Formula For B4 =CountIF($A$2:$A4,$A4)