Learn simple ways to find and remove duplicates in Excel: use standard functions, Conditional Formatting, or a special add-in. Remove duplicates in Excel: video transcript In this video we will look at the ways you can highlight and remove duplicates in Excel. We will use conditional formatting to see the duplicates, employ formulas to find and remove duplicate rows, and work with our special add-in to do all this and more. You have probably tried working with the 'Remove Duplicates' tool in Excel. If you use Excel a lot at home or in the office, you know that sometimes the Excel files can quickly become unwieldy due to the sheer number of records you are working with. Luckily, Excel has built-in functions to help you find and remove duplicate records. Let's have a quick look at what it does. Go to Data tab, click the icon, and you will get the list of all columns in your table. Here you can choose key columns you want to check: one column,say, to look for duplicate IDs, or all to find duplicate rows. If I want to find the rows that have the same name and book title, these are the columns I need to select. Once I click ok, it will simply remove all duplicates and leave the original values intact. Highlight duplicates in Excel If you prefer to take a look at the records before deleting them, you need to know the ways you can highlight duplicates in Excel. Your main assistant is going to be conditional formatting here. It is also the quickest way to keep checking your table for repeating values. Select your column or range, go to Home tab and find Conditional Formatting in the Styles group. Select Highlight cell rules, Duplicate values. Here you can pick one of the standard fill and font colors or choose a custom combination. Please note that Conditional Formatting compares each cell in the range to others, it won't compare columns or rows. Its biggest advantage is that it will keep comparing the cells when you add any new values to the range. Audacity for mac. However, it also includes the first occurrences as you can see. To exclude them, let's cancel our changes, select the records in column D that we want to compare, and create a new rule in Conditional formatting. Click to use a formula to determine which cells to format. You need to enter the formula in this box: =COUNTIF($D$2:$D2,$D2)>1 So if it finds more than one occurrence of a value, the rule will highlight duplicates the way you choose when you click Format. Select color, and Click OK to apply the rule. How to delete duplicates in Excel When you want to find and then remove duplicates in Excel, you can get the best of the COUNTIF and IF functions. We can use the same formula: =COUNTIF($D$1:$D2,$D2)>1 This way if the value from D2 is found more than once, it will be marked as TRUE. If you want to make more sense of the formula, you can enclose it in the IF condition: =IF(COUNTIF($D$1:$D2,$D2)>1,'Duplicate',') Copy the formula down to see the results. Standard formula in Excel to remove duplicate rows What about duplicate rows? What if you want to check the author's name in addition to the book id? In this case we need to modify the formula and use COUNTIFS instead of COUNTIF to deal with several conditions, then include one for each column we want to check. Here is how we shall change our formula: =IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1, 'Duplicate', ') This formula lets us check values in each of the first three columns, so if they are the same in the range A2 through C2 and A11 through C11, they are marked as duplicates. If the name or the ID is different, the row is marked as unique. Now that you've marked the records you want, you can simply filter your table by the helper column and remove them, or do whatever you need to. A special add-in for Excel: highlight duplicates, move or delete them, and more You can also avoid the formulas, helper columns, or colors.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |