Excel Data Cleanup: Remove Duplicates - Episode 2188
2,245 views · Published 10 April 2018 · 2:45 · Indexed 29 October 2026
Channel: MrExcel.com · 2018 · Education
Microsoft Excel Tutorial: Summarizing Data in Excel Using Remove Duplicates Welcome to another episode of the MrExcel Podcast, where we bring you the best tips and tricks for mastering Excel. In today's episode, we will be discussing how to summarize data using the Remove Duplicates function. This tip was suggested by one of our viewers, Adam from Road 2, during a seminar in Dallas. So let's dive in and see how it works. In yesterday's episode, we used the Subtotal command to summarize data down to one row per customer. However, Adam pointed out that we can achieve the same result using the Remove Duplicates function. So let's start by selecting the data we want to summarize, and then copying it to a new location. Next, we will go to the Data tab and click on Remove Duplicates. In the dialog box, we will choose "Unselect All" and then select the column that contains our customer names. Click OK, and voila! We now have a list of unique customers. But wait, what about those numbers next to the customer names? Don't worry, we can easily get rid of them by selecting the numbers, pressing Ctrl+Shift+Down to select the entire column, and then pressing Ctrl+Shift+Right to select the numbers. Next, we will enter a SUMIFS formula to calculate the total for each customer. This may seem like a lot of steps, but trust me, it's worth it. Plus, this method is discussed in detail in my latest book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time. Now, let's compare this method to the one we used in yesterday's episode. While this method may require more keystrokes, it is definitely faster and more efficient. But don't just take my word for it, try it out for yourself and see which method works best for you. And if you're looking for more ways to create a summary table, be sure to tune in for the rest of this week as we explore 5 different methods, including Subtotals, Advanced Filter, Consolidate, and Pivot Tables. So there you have it, a quick and easy way to summarize data using the Remove Duplicates function. I want to give a special thanks to Adam for suggesting this tip and to all of you for tuning in. Don't forget to check out our other episodes for more Excel tips and tricks. And if you want to learn even more, be sure to grab a copy of my book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time. Thanks for watching and we'll see you next time for another netcast from MrExcel. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #evergreen #excelremoveduplicates This video answers these common search terms: remove duplicated from a column in excel how remove duplicate rows in excel how to remove duplicates in column excel how to remove duplicates from multiple columns in excel how to remove duplicate values in excel in column how to remove duplicate cells excel how to remove duplicate values in excel how to remove duplicates excel how to remove duplicates in an excel coluln how to remove duplicates in an excel sheet how to remove duplicates row in excel how to remove excel duplicate rows how to remove of duplicates in excel how does remove duplicates work excel where is remove duplicated on excel how does the remove duplicates work excel which duplicate record does excel remove? how to find duplicates for two column in excel how to find duplicate entries excel how to find duplicates in excel how to delete duplicate lines in excel how to eliminate duplicate lines in excel how can i delete duplicate entries in excel how to delete duplicate values on excel how to delete duplicates in a column excel how to duplicate excell easy how to erase the duplicates in excel how to extract duplicates in excel how to take out duplicates excel removing duplictes on excel when removing duplicates on excel will "delete duplicates" delete entire rows in excel Table of Contents: (00:00) Using Remove Duplicates to summarize data (00:12) Suggestion to use Remove Duplicates from seminar attendee (00:22) Using Remove Duplicates to get unique customers (00:43) Removing meaningless numbers and using SUMIFS formula (01:00) Comparison of methods (02:01) 5 different ways to create a summary table (02:13) Clicking Like really helps the algorithm This is the first of a five-part series on Summarizing Data This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables How to summarize with Remove Duplicates 1. Copy the data to a new location 2. Data, Remove Duplicates. Click UnSelect All. Click Customer. OK. OK. 3. Click in the first number. Ctrl+Shift+Down+Right. Enter a SUMIFS and Ctrl+Enter. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154017/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920