How to Remove Duplicates in Excel

There are a number of reasons why you might aim to remove duplicates in Excel. Duplicates can cause inaccuracies in data analysis and calculations. For example, if you have duplicate values in a sales report, you may end up overstating your sales numbers. When working with large amounts of data, removing duplicates can make it easier to navigate and analyze the data.

Removing duplicates can make your Excel spreadsheet look cleaner and more professional. Also, by removing duplicates, you can avoid errors while using functions like VLOOKUP or COUNTIF, which may count the same value twice if duplicates are present. These are just a few reasons to remove duplicates in Excel. In this tutorial, we will explore two ways to remove duplicates. Let’s get started!

Excel’s “Remove Duplicates” Function

To remove duplicates in Excel, you can use the “Remove Duplicates” feature. Here’s how:

1. Select the range of cells or the entire column from where you want to remove duplicates.
2. Go to the “Data” tab in the ribbon and click on the “Remove Duplicates” button.
3. In the “Remove Duplicates” dialog box, select the columns that you want to remove duplicates from, or leave the default selection of all columns.
4. Click on the “OK” button to remove the duplicates.

Alternatively, you can use the Advanced Filter feature to remove duplicates:

1. Select the range of cells or the entire column from where you want to remove duplicates.
2. Go to the “Data” tab in the ribbon and click on the “Advanced” button in the Sort & Filter group.
3. In the “Advanced Filter” dialog box, select “Unique records only” and click on the “OK” button.

It will remove all duplicate rows and leave only unique rows.

Note: The “Remove Duplicates” feature can only remove duplicates on a single worksheet, if you have multiple worksheets and want to remove duplicates you can use a VBA script or a third-party add-on.

How to Find and Remove Duplicate Values Using a Pivot Table

Pivot tables are great for analyzing data but today we will use them to remove duplicates. Here is how to use a pivot table to remove duplicates.

1. Create a pivot table based on your data.
2. Select a given range of data on your worksheet.
3. Go to the Insert tab and select the Pivot table.
4. Click ok in the create pivot table dialog box. A blank pivot table will appear.
5. Add the required fields in the rows of the pivot table.
6. Go to the design tab and select “report layout” to change the design of the pivot table to a tabular format.
7. Remove subtotals by navigating to the design tab and selecting sub-totals. Click on “Do Not Show Sub-totals.”
8. Since pivot tables only list unique values for a given set of data, all the duplicates will automatically be removed.

Duplicate sets of values can be annoying as they can become an obstacle to having a clean and accurate dataset. Fortunately, you now have some ways of dealing with them in your data-wrangling toolbelt.