SwifDoo PDF

Efficient PDF Toolkit for Windows

  1. OCR PDFs to make scans searchable and editable;
  2. Unleash PDF creativity on iOS and Android effortlessly;
  3. Translate PDF accurately to break down language barriers

Table of content

Share this article

Home / Blog / 4 Effective Ways to Remove Duplicates in Excel

4 Effective Ways to Remove Duplicates in Excel

By Demi | Follow twitter | Updated on September 19, 2023
Introduction:
Excel is one of the most commonly used tools for processing data. When collaborating on an Excel document online, you may come across duplicate values, which can be annoying sometimes. Do you know how to remove duplicates in Excel? This article by  SwifDoo PDF will teach you how to do that in four effective ways.
summary

Remove Duplicates in Excel with the Remove Duplicates Function

Before removing duplicates in Excel, we suggest you create a copy of the original data for later comparison. And it’s necessary to understand that the way Excel defines duplicates is based on the appearance rather than the underlying value of data. For instance, “10/8/2023” and “August 10, 2023” will be regarded as two unique values though they indicate the same meaning.

Let’s see how to remove duplicates in Excel with the built-in Remove Duplicates function.

Step 1: Open your Excel document and select the columns that contain duplicates;

Step 2: Navigate to the Data tab and hit Remove Duplicates in the Data Tools group;

Remove duplicates in Excel 1

Step 3: Select the columns you want to remove duplicates from and hit OK;

Remove duplicates in Excel 2

Step 4: A message appears that shows the number of duplicate values found and removed, and the remaining number of unique values.

Remove duplicates in Excel 3

When you remove duplicates in Excel, remember to mark the ‘My data has headers’ checkbox if your data consists of column headers.

Remove Duplicates in Excel Using the Unique Formula

Unique is a function used to find unique values in a range or array. Using the Unique formula is another effective way to remove duplicates in Excel. Let’s take a look at the details to do the duplicates removal work.

Step 1: Input =UNIQUE in an empty cell and select the area containing duplicate values;

Remove duplicates in Excel using the Unique formula

Step 2: Press the Enter key to run the formula, and then a list of unique values will appear. That’s how you can remove duplicate in Excel.

Notably, values filtered by the Unique formula are dynamic. That is to say, if you add or delete a value in the initial column, the result will update automatically.

Remove Duplicates in Excel with Power Query

With Power Query, you can import data from various sources. This tool also makes it effortless to remove duplicates in Excel.

Step 1: Select the cells you want to get rid of duplicates from;

Step 2: Hit From Sheet on the Data tab to bring up the power query editor window;

Remove duplicates in Excel with Power Query step 2

Step 3: Select the columns with duplicate data. You can hold the Ctrl key to select multiple columns;

Step 4: Navigate to the Home tab, andtap Remove Rows > Remove Duplicates to remove duplicates in Excel;

Remove duplicates in Excel with Power Query step 4

Step 5: Then, all the duplicate items in the selected columns will be removed;

Step 6: Tap Close & Load To… from the drop-down options;

Remove duplicates in Excel with Power Query step 6

Step 7: Select the Table radio button, decide where to put the import data and hit OK;

Remove duplicates in Excel with Power Query step 7

Step 8: Then, your data with duplicates removed will be present in the area you select on the same worksheet.

Remove Duplicates in Excel Using Advanced Filter

Another way to remove duplicates in Excel is to filter unique values and copy them to another location on the active sheet. You can do that using the advanced filter.

Step 1: Select the cells you want to remove duplicates from in Excel;

Step 2: Go to the Data tab, and click the Advanced button in the Sort & Filter group;

Step 3: Select Copy to another location radio button in the dialog box that helps remove duplicates in Excel, and verify is the List range covers all the cells you’ve selected in Step 1;

Remove duplicates in Excel using Advanced Filter

Step 4: Enter the range where to copy the unique values in the Copy to field, mark the Unique records only and hit OK;

Step 5: Then, all the unique values will be copied to the new location you’ve selected.

Share Excel Documents in PDF

After removing duplicates in Excel, if you want to share the Excel document with others, we suggest you convert it to PDF first. By doing so, you can make sure its content and layout remain the same regardless of the operating system. You are able to convert Excel to PDF with the SwifDoo PDF PDF converter. This software also lets you make conversions between PDF and Word, PPT, images, and other files and edit your PDF.

Remove duplicates in Excel SwifDoo PDF

Final Words

It is not a difficult to remove duplicates in Excel as long as you master these easy methods. You can do that easily with Excel’s built-in Remove Duplicates function. Another effective method to is to separate unique values from extra duplicates. With the help of the Unique formula, the Power Query function, and the Advanced Filter tool, you can do that in a breeze.

Demi is curious about the technology field and is always passionate to explore new things. She is now working as a copywriter for this website. Demi will introduce many useful tips to improve your workflow.

Related Articles