Sometimes, a spreadsheet has duplicates that you want to remove. If you’re using an older version of Excel this can be a really tedious process. In this article, I will explain how to remove duplicates from a list in Excel 2013 or above. This is very useful if you have a database of emails and you need to send the same email to several of those contacts.

Are you trying to remove duplicates in Excel but have no clue how? It’s easy! I created this tutorial because there are quite a few people out there who want to remove duplicates in Excel, but don’t know how. To make matters worse, there are also many tutorials explaining how to do it, but they all do it wrong. This tutorial explains the right way to remove duplicates in Excel by using INDIRECT.

Find and remove duplicates

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 More…

Sometimes duplicate data is useful, sometimes it just makes it harder to understand your data. Use conditional formatting to find and highlight duplicate data. That way you can review the duplicates and decide if you want to remove them.

1. Select the cells you want to check for duplicates.Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.
2. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.

Remove duplicate values

When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

1. Select the range of cells that has duplicate values you want to remove.Tip:  Remove any outlines or subtotals from your data before trying to remove duplicates.
2. Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.For example, in this worksheet, the January column has price information I want to keep.So, I unchecked January in the Remove Duplicates box.
3. Click OK.

Other method

Using the Advanced Filter Option

The Advanced Filter option in Excel helps you filter duplicate values and copy the unique values to a different location. Look at the following steps to find out how the Advanced Filter option works.

• First, click on a cell or range in the dataset from which you want to remove duplicates. If you click on a single cell, Excel automatically determines the range when you click on Advanced Filter.

• Locate the Advanced Filter option.

DATA tab  → Sort & Filter section → Advanced and click on it.

•  You will see a dialog box. It consists of a list of options for advanced filtering.

• Select the ‘Copy to another location’ option to copy the unique values to a different location.
• Check the range of your records in the ‘List Range’ field and make sure it is the range that you have specified.
• In the ‘Copy to:’ field, enter the range where the resultant unique values must be copied.
• Check the ‘Unique records only’ box. This step is the most crucial.
• Click on OK.

•  The unique values will be copied to cell G1.

These were the in-built functionalities in Excel that help us in removing duplicates. Now, let’s move forward and learn how we can create our own function to do the same. 

Free Course: Business Analysis Basics

Master the Fundamentals of Business AnalysisENROLL NOW

How to Use Formulas to Remove Duplicates in Excel?

We will be using 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 the columns A, B, and C by using the concatenation operator “&“. So, the Excel formula would be:

=A2&B2&C2

This formula is entered into the 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 from the table. You can copy these resultant unique records and paste them elsewhere.

Let’s proceed by understanding the final approach to delete duplicates: Using Power Query. 

How to Use the Power Query Tool to Remove Duplicates in Excel?

Power Query in Excel lets you import data from various sources, clean and transform your data. This tool makes it effortless to remove duplicates in Excel.

• Select a cell or range, locate Data Tab  →  Get & Transform Data section  →  From Table/Range and click on it.

• On clicking, you will see a dialog box to create a power query table. Make sure that the range of values is correctly specified. Click on OK.

• The following Power Query editor window appears.

Business Analyst Master’s Program

Gain expertise in Business analytics toolsEXPLORE PROGRAM

Now you have two options. You can remove duplicates based on:

• One or more columns
• Entire table

To remove duplicates based on one or more columns, right-click on the specific column header. You can select more than one column using the CTRL button and remove the duplicates accordingly. 

To remove the duplicate records based on the entire table, click on the button present on the top left corner of the data preview. And then select the ‘Remove Duplicates’ option. 

This way, the data will be free from duplicate values.

On clicking the ‘Close & Load’ option, the data will be loaded onto your spreadsheet.

Get prepared for the role of a Business Analyst with the Post Graduate Program in Business Analysis. Enroll now!

Conclusions

How to Remove Duplicates in Excel Duplicates are very common in Excel. Whether you are importing data from different sources or entering the details of the same record multiple times, its likely you will end up with duplicates. This is because Excel cannot tell if two rows are duplicate until you have looked at it carefully. On an average users import or paste 20-30 rows for every 100 rows they look at in the spreadsheet. It’s pretty evident that they won’t manually auto-check each and every single one of them. So eventually, some duplicates are bound to sneak in the workbook.