A Step-by-Step Guide on How to Remove Duplicates in Excel

Microsoft Excel is a frequently used program, but when it comes to deleting and reducing duplicate data, it can be a little complicated. When working with large datasets, removing duplicates in Excel is a common chore. You can wind up with duplicate entries in your spreadsheet if you merge multiple tables or if numerous users have access to the same document. As a result, the data becomes obsolete. The greater the dataset, the more likely duplicate records will be found.

Source: safalta

It can be a concern if they aren't discovered and dealt with properly.
   

1. Remove duplicate values in excel-

Excel offers a built-in function that can help you remove duplicate entries from your spreadsheet. Let's have a look at the procedures for removing duplicates in Excel.
Step 1: To remove duplicates from a dataset, first click on any cell or a specified region within it. Excel automatically determines the range for you if you click on a single cell in the next step.
 
Step 2: Next, look for and pick the 'Remove Duplicates' option.
Remove Duplicates from the DATA tab's Data Tools section
 
 Step 3: As illustrated below, a dialogue window is displayed. You can check for duplicate data by selecting the columns you want to compare.
Select the 'My data has headers' option if your data contains column headings, and then click OK.
While the header option is selected, the first row is not taken into account when deleting duplicate data.

Step 4: Excel will now display a dialogue box and eliminate the duplicate rows. The dialogue box displays a summary of the number of duplicate values identified and deleted, as well as the total number of unique values.

Step 5: The duplicate records have been eliminated, as you can see.
 

2. Using the Advanced Filter Option-

Excel's Advanced Filter feature allows you to filter duplicate values and copy unique values to a new spot. Take a look at the steps below to see how the Advanced Filter option works.

• To begin, select a cell or region in the dataset from which duplicates should be removed. When you click on Advanced Filter on a single cell, Excel automatically decides the range.
• Select Advanced Filter from the drop-down menu.

Click on Advanced in the Sort & Filter section of the DATA tab.

• A dialogue window will appear. It consists of a collection of sophisticated filtering options.
• To copy the unique values to a different location, use the 'Copy to another location' option.
• In the 'List Range' field, double-check that the range of your records matches the range you entered.
• Enter the range where the generated unique values must be copied in the 'Copy to:' field.
• Select 'Unique records only' from the drop-down menu. This is the most important phase.
• Select OK.
• Cell G1 will be replicated with the unique values.

These are the built-in Excel features that assist us in deleting duplicates. Now let's go on to learning how to make our own function that does the same thing.
 

3. How to Use Formulas to Remove Duplicates in Excel?-

To demonstrate this strategy, we'll use a basic example with the columns kind of sport, athlete name, and medal earned.
 

 

• This method entails utilizing an Excel formula to combine the columns and calculate the count. The duplicate values will then be filtered out (ones that have a count greater than 1).

Let's use the concatenation operator "&" to join the columns A, B, and C together. As a result, the Excel formula would be as follows:
=A2&B2&C2
This formula is typed into cell D2 and then copied down the rows.
 

 

• To locate the duplicates in Column D, we'll need a new column called 'Count.' As a result, on cell E2, we employ the COUNTIF function. =COUNTIF($D$2:D2, D2) will be the formula.

This formula counts the number of times each value in column D appears.
If Count is "1," that means it has only appeared once and is unique. It's deemed a duplicate value if the value is "2" or higher.

• Select the Filter option to apply a filter to the Count column.

It's under the DATA tab, in the Sort & Filter section, Filter.
At the top of Column E, select the filter. Select "1" to maintain only unique values and discard duplicates.

• The duplicate values will be removed from the table when you click OK. You can copy and paste the resulting unique records wherever you want.

READ MORE:
How to Use Yoast SEO on WordPress?
Excel Vs. Google Sheets: What You Need to Know-
Top Excel Formulas You Should Know
Exploring Google’s New multisearch Feature

 

How do I quickly remove duplicates in Excel?

• Choose the range of cells with duplicate values that you wish to get rid of. Before attempting to remove duplicates, remove any outlines or subtotals from your data.
• Select Data > Remove Duplicates, and then check or uncheck the columns where duplicates should be removed under Columns.
• Click the OK button.

• Whatsapp Channel