3 Ways to Remove Duplicates Values in Excel
3 Ways to Remove Duplicates Values in Excel. During data processing, you may struggle with finding and deleting duplicate values, which can be time-consuming. In this article, I will show you 3 quick ways to remove duplicate values in Excel, which can save you time when dealing with difficult and repetitive data. Let’s take a look!
I have an example of sales data for specific regions as shown below:
3 Ways to Remove Duplicates Values in Excel
Currently, there are duplicate values in the data range. Do you want to remove these duplicates? Here are 3 methods for your reference!
1. Remove duplicates using Remove Duplicates
3 Ways to Remove Duplicates Values in Excel
Before removing duplicate data, you should create a copy of the data by:
Select the data by clicking on it and pressing CTRL + A.
Press CTRL + C to copy the selected data to the clipboard, then click on a new location in the worksheet and press CTRL + V to paste the copied data into the new location.
3 Ways to Remove Duplicates Values in Excel
Next, to remove duplicate rows from the copied dataset, you will perform the following steps:
Step 1: Click on the copied data.
Step 2: Go to the Data tab => Select the Data Tools group => Select Remove Duplicates.
3 Ways to Remove Duplicates Values in Excel
Step 3: In the Remove Duplicates dialog box, make sure that all column headings are listed and the option with the label “My data has headers” is selected.
3 Ways to Remove Duplicates Values in Excel
Step 4: Press OK to complete.
3 Ways to Remove Duplicates Values in Excel
2. Remove duplicate values using the UNIQUE function
In this step, you will copy the header and paste it into the location where you want to display the data with duplicate values removed.
3 Ways to Remove Duplicates Values in Excel
Select the cell immediately below the “Sales Agent” header (in this case, cell F2) and enter the following formula:
=UNIQUE(A2:C20)
Note: If you have formatted this table as a proper Excel Table, you can use the table name instead of the cell range reference. This is easier to understand and write.
3 Ways to Remove Duplicates Values in Excel
Then press Enter to display the data range with duplicate values removed.
3 Ways to Remove Duplicates Values in Excel
3. Remove duplicate values using Power Query
3 Ways to Remove Duplicates Values in Excel
To use Power Query to remove duplicates in a table, follow these steps:
Step 1: Click on the data to be deduplicated.
Step 2: Go to the Data tab => in the Get & Transform group, select From Table/Range.
This will display the Power Query interface.
Step 3: Click on any column header and press CTRL + A to select all columns.
3 Ways to Remove Duplicates Values in Excel
Step 4: Go to the Home tab => in the Reduce Rows group, select Remove Rows and choose Remove Duplicates.
3 Ways to Remove Duplicates Values in Excel
Your data is now deduplicated.
3 Ways to Remove Duplicates Values in Excel
Step 5: Once you have finished, you will download the result to an Excel file by selecting Close & Load in the Home group and choosing Close & Load to….
3 Ways to Remove Duplicates Values in Excel
Step 6: In the Import Data dialog box, select the option for Table and Existing Worksheet. Next, select the cell where you want to display the data with duplicate values removed.
3 Ways to Remove Duplicates Values in Excel
Step 7: Press Ok to complete.
3 Ways to Remove Duplicates Values in Excel
Note: In this method, if your original data range is changed, the result in the new data range will also update automatically! Simply select any cell in the new data range, right-click and select Refresh.
So I have finished instructing 3 ways to remove duplicate values in Excel. I hope this article is helpful to you. If you find it useful, don’t forget to rate the article below! Best regards.
