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

Safalta Published by: Ishika Kumar Updated Wed, 22 Jun 2022 01:04 AM IST

Highlights

if you wanna know how to remove duplicates in excel, then read more for complete details.

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. 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

 

Free Demo Classes

Register here for Free Demo Classes

Please fill the name
Please enter only 10 digit mobile number
Please select course
Please fill the email

What is the formula to remove duplicates in Excel?

➤ Go to Data > Remove Duplicates tool in Excel Toolbar under the section Data Tools. Step 2: ➤ Click on Remove Duplicates. ➤ Put a check on all names of the columns you want to remove duplicates from.

How do I remove both sets of duplicates in Excel?

Note: If you need to remove all duplicate rows, tick Select whole rows in the Select Duplicate & Unique cells dialogue box, and all duplicate rows will be selected immediately. Then go to Home > Delete > Delete Sheet Rows, and all duplicate rows will be eliminated.

How do I remove duplicates in sheets?

  1. Select the dataset from which you want to remove the duplicate records.
  2. Click the Data option in the menu.
  3. Click on the Remove Duplicates option.

How do I delete all duplicate rows in Excel?

Select the range from which duplicate rows should be removed. Simply hold down the Ctrl + A key to select the entire sheet and erase all duplicate rows. 2. Select Remove Duplicates from the Data Tools group on the Data tab.

Free E Books