Easy Steps On How to Remove Duplicates in Excel

Wondering how to remove duplicates in Excel in easy ways? Let us find out all the ways in which it can be done.

Duplicates are no new thing in Excel. Whether you are working solo or with a colleague or partner, duplicates can happen easily but on a sheet that is very lengthy and detailed, finding duplicates can be very tricky and can cause major trouble and error to your sheets.

There are several ways with the help of which you can remove duplicates. Let us find out what are the many ways to do so.

We will illustrate how to remove duplicates in Excel using a Sports dataset. This dataset contains information about Olympic medalists for the year 2012. 

How to Remove Duplicate Values in Excel

Let’s see the steps to be followed to remove duplicates in Excel.

Step 1: Click on any cell or a specific range in the dataset from which you want to remove duplicates. If you click on a single cell, Excel automatically determines the range for you in the next step.

Step 2: Locate the ‘Remove Duplicates’ option and select it. DATA tab → Data Tools section → Remove Duplicates

Step 3: A dialog box appears, as shown below. Select the columns you want to compare and check for duplicate data. 

In case your data consists of column headers, select the ‘My data has headers’ option, and then click on OK. 

Step 4: Excel will now delete the duplicate rows and display a dialog box.

Step 5:  As you can notice, the duplicate records are removed.

Let us now understand how to remove duplicates in Excel using the Advanced Filter option.

Find and Remove Duplicates

Here is how you can find and remove the duplicates in simple ways. 

1. Select the range of cells containing duplicate values that you want to remove. Note: The best way to remove duplicates is to remove any outlines or subtotals from your data.
2. By selecting Data > Remove Duplicates and then checking or unchecking the columns you wish to purge, you can remove duplicate records.
3. Then click OK.

Filter for Unique Values

Take the following steps to filter for unique values:

Step 1: Select the range of cells. Ensure that the active cell is in a table.

Step 2: Locate and select the Advanced filter option in the Sort & Filter group. 

Data tab → Sort & Filter section → Advanced.

Sort & Filter Option

Step 3: The Advanced Filter popup box will appear on your screen. Take one of the following actions:

• If you have to filter the range of cells/ tables in place, click Filter the list in place.
• If you have to copy the results of the filter to a different location, then take the following measures:
  • Click the “Copy to another location” option to copy the values. 
  • In the “Copy to” box, enter a cell reference where the resultant values must be copied.
  • You can temporarily click the “Collapse Dialog” option ( ) to hide the popup window. Then, once you have selected a cell on the worksheet, click the “Expand” option ().
  • Check the ‘Unique records only’ option box and then click OK.

Advanced Filter Popup Box

How to Use Formulas to Remove Duplicates in Excel?

Let us now learn to use formulas to remove duplicates in excel with the help of a simple example containing the columns: type of sport, athlete name, and medal won to demonstrate this approach.

This method involves combining the columns using an Excel formula and finding out the count. We will then filter out the duplicate values (ones that have a count greater than 1). 

• Let’s combine columns A, B, and C by using the concatenation operator “&“. So, the Excel formula would be:

=A2&B2&C2

This formula is entered into cell D2 and then copied down to all the rows. 

• Now, we will need another column named ‘Count’ to find out the duplicates in Column D. Hence, we use the COUNTIF function on cell E2. The formula will be:

=COUNTIF($D$2:D2,D2)

This formula helps count the number of occurrences of each value in column D.

If the value of Count is “1”, then it has only appeared once and is unique. If the value is “2” or more, then it is considered a duplicate value.

• Now add a filter to the Count column by selecting the Filter option.

You will find it in the DATA tab → Sort & Filter section → Filter

Click on the filter at the top of Column E. Select “1 ” to keep only the unique values and remove the duplicates.  

• On clicking OK, the duplicate values will be removed. Copy these resultant unique records and paste them elsewhere.

How Lio can Help You

Lio is a great platform that can help entrepreneurs, homemakers, students, businessmen, managers, shop owners and many others. This mobile application helps to organize business data and present them in an eye-catching manner. 

Lio is a great platform for small business owners and can track a wholesome record of employee information for better employee management, customer data, etc. You can handle those data with ease.

If you want to be a professional, then you must save your time, you need to learn to arrange all the business strategies in one place. In that case, Lio can be your partner.

Entrepreneurs can also allow multiple authorized users of their office to access the information from various locations within minutes.

Lio is definitely for the win and using it for your business is only going to make your journey smooth and easy to track.

Step 1: Select the Language you want to work on. Lio on Android

Step 2: Create your account using your Phone Number or Email Id.

Verify the OTP and you are good to go.

Step 3: Select a template in which you want to add your data.

Add your Data with our Free Cloud Storage.

Step 4: All Done? Share and Collaborate with your contacts.