The Easiest Way to Remove Duplicate Data in Excel

Other Interesting Articles

Use Pivot Tables

The third way to remove duplicates in Excel is to use “Pivot Table”. Pivot Table is a feature that can be used to summarize, analyze, and present data as dynamic tables. By using Pivot Tables, you can easily filter data based on specific criteria, including removing duplicates. The following are the steps to use a Pivot Table:

  1. Select all the data you want to deduplicate. You can use the shortcut “Ctrl + A” to select all data in a worksheet, or click and drag the mouse to select a specific range of data.
  2. Click the “Insert” tab on the ribbon, then click the “PivotTable” button in the “Tables” group.
  3. In the “Create PivotTable” dialog box, select the location where you want to place the Pivot Table. You can select a location within the same worksheet, a new worksheet, or a new workbook.
  4. Click the “OK” button to create a Pivot Table.
  5. On the “PivotTable Fields” pane, select the column or row that you want to use as criteria for removing duplicates. You can select more than one column or row by pressing the “Ctrl” key when clicking the column or row name. Then, drag the column or row name to the “Rows” or “Columns” area of the “PivotTable Fields” pane.
  6. On the “PivotTable Fields” pane, select the column or other row you want to display in the Pivot Table. Then, drag the column or row name to the “Values” area of the “PivotTable Fields” pane.
  7. On the “PivotTable Fields” pane, select the other columns or rows that you want to filter to filter data based on specific values. Then, drag the column or row name to the “Filters” area of the “PivotTable Fields” pane.
  1. In the created Pivot Table table, you can view the filtered data based on the criteria you selected. Data that has the same or similar values in a criteria column or row is merged into a single row or column only, so there are no duplicates in the data.
  2. If you want to change the criteria or filters used to filter data, you can click the small arrow button next to the column or row name in the Pivot Table table, and then choose the option you want from the menu that appears.
  3. If you want to delete the Pivot Table and return to your original data, you can click the “Analyze” tab on the ribbon, then click the “Clear” button on the “Actions” group, and choose the “Clear All” option.

For example, suppose you have data as in the previous table:

If you want to remove duplicates based on Name and Age columns, then you can do as following steps:

  1. Select all the data, and then click the “PivotTable” button on the “Insert” tab.
  2. In the “Create PivotTable” dialog box, select the location where you want to place the Pivot Table. For example, select a new worksheet.
  3. Click the “OK” button to create a Pivot Table.
  4. In the “PivotTable Fields” pane, select the Name and Age columns, and then drag the column names to the “Rows” area of the “PivotTable Fields” pane.  
  5. In the “PivotTable Fields” pane, select the City column, and then drag the column name to the  “Values” area of the “PivotTable Fields” pane.
  6. In the created Pivot Table table, you can see the filtered data by Name and Age columns. Data that has the same or similar values in that column is combined into a single row, so there are no duplicates in the data.

Other Ways to Remove Duplicates in Excel

In addition to the three ways we have discussed above, there are several other ways you can use to remove duplicates in Excel, namely:

  • Use “Conditional Formatting” to highlight duplicates of a specific color, and then filter or delete the highlighted data.
  • Use “Advanced Filter” to filter unique data, and then copy or move that data to another location.
  • Use “VBA” (Visual Basic for Applications) to create a macro that can remove duplicates automatically by using specific code.

These methods may require some additional steps or special knowledge to do so but can provide results that are more flexible or fit your needs. You can find more information about these ways on the internet or in Excel guidebooks.

Thus the article we made about how to remove duplicates in Excel. We hope this article has helped you manage your data better and more efficiently. Thank you for reading this article.

Latest Articles

Related Articles