Site Loader

MS Excel is a powerful tool for managing and analyzing data, but duplicate entries can hinder your progress and accuracy. Fortunately, Excel provides efficient methods to identify and remove duplicates, allowing us to streamline your data and work with confidence.

In this article, I will guide you through the step-by-step process of removing duplicates in Excel, making sure that our data is clean and accurate for our projects.

There are two ways to remove duplicates in Excel:

1. Using the Remove Duplicates feature

  1. Select the range of cells that contains the duplicate values you want to remove.
  2. On the Data tab, click Remove Duplicates.
  3. In the Remove Duplicates dialog box, uncheck any columns where you don’t want to remove duplicate values.
  4. Click OK.

2. Using the Advanced Filter feature

  1. Select a cell in your dataset and go to Data > Advanced filter to the far right.
  2. Choose to “Filter the list, in-place” or “Copy to another location”.
  3. Tick the “Unique records only” box to keep the unique values, and then “OK” to remove all duplicates.

Shortcut to remove duplicates in Excel

We can also use the keyboard shortcut Shift + Alt + F5 to remove duplicates in Excel. This will open the Remove Duplicates dialog box, where you can uncheck any columns where you don’t want to remove duplicate values.

Tips for removing duplicates in Excel

  • If you want to keep one copy of each duplicate value, you can use the Advanced Filter feature and select the “Unique records only” option.
  • You can also use the Conditional Formatting feature to highlight duplicate values before you remove them.
  • If you have a large dataset, you may want to use the Remove Duplicates feature on a smaller range of cells first, to see how many duplicates are removed.

Post Author: Kapil Mohan Sharma (Business Consultant & Coach)

Kapil Mohan Sharma has experience in BPO-ITES, OTA, Banking & Finance and Manufacturing Industry and exposure to best practices being used in these Industries. He is an MBA, Business Excellence Master Black Belt, Lean Six Sigma Master Black Belt, Lean Expert, Project Management (Prince 2 Practitioner), Robotics Process Automation (UiPath), Business Analytics.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog Stats

  • 53,152 hits