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;
Step 3: Select the columns you want to remove duplicates from and hit OK;
Step 4: A message appears that shows the number of duplicate values found and removed, and the remaining number of unique values.
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;
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;
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;
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;
Step 7: Select the Table radio button, decide where to put the import data and hit OK;
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;
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.
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.