Skip to content

How to remove duplicates in Excel?

With their ability to manipulate entered formulae, numbers and keywords, Spreadsheets are some of the most simplistic yet powerful tools available to any business organisation.

Data entry — a traditional use of Excel — often utilises data duplication; but in some cases, this can also be the cause for an unorganised and unprofessional-looking spreadsheet.

In this article, we list the methods users can use to locate and remove duplicate information in an Excel Workbook.

You can find and remove duplicates in Excel using the following guide in Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and Excel Starter 2010.

Also read: How to lock cells in Excel?

How to find duplicates in Excel Worksheet?

As mentioned earlier, data duplication has importance in a spreadsheet. Hence, the removal of essential duplicate data can cause harm to concerned parties. Thus, knowing where such information exists is required.

With Conditional Formatting function, users can find the exact location of duplicate data.

Launch Microsoft Excel on your computer, open the required workbook and follow the steps listed below to find duplicates.

Step 1: Select entered data.

How to remove duplicates in Excel? | Candid.Technology

Step 2: Click on the Home section.

How to remove duplicates in Excel? | Candid.Technology

Step 3: Locate and click on Conditional Formatting in the Styles subsection.

How to remove duplicates in Excel? | Candid.Technology

Step 4: Click on Highlight Cell Rules and then select Duplicate Cells.

How to remove duplicates in Excel? | Candid.Technology

Step 5: Select Duplicate Cells in the drop-down box, choose the colour with which duplicate cells will be highlighted and click OK.

How to remove duplicates in Excel? | Candid.Technology

Also read: How to insert a footnote in Microsoft Word?

How to remove duplicates in Excel Worksheet?

The Remove Duplicates function takes into regard all the cells in a column and removes duplicate cells within that column alongwith their corresponding rows of data.

Launch Microsoft Excel on your computer, open the required workbook and follow the steps listed below:

Before removing duplicates, we recommend copying the selected data and pasting it into a new worksheet; it will allow you to recover information if you run into an error.

Step 1: Select entered data.

How to remove duplicates in Excel? | Candid.Technology

Step 2: Click on the Data section.

How to remove duplicates in Excel? | Candid.Technology

Step 3: Locate and click on Remove Duplicates in the Data Tools subsection.

How to remove duplicates in Excel? | Candid.Technology

Step 4: Choose the columns from which duplicate data needs to be removed and click OK.

Selecting the My data has headers option — marked in green — will exclude the first row of information from checks for duplication.

How to remove duplicates in Excel? | Candid.Technology

Also read: How to add Grammarly to Microsoft Word and Outlook?

Hello There!

If you like what you read, please support our publication by sharing it with your friends, family and colleagues. We're an ad-supported publication. So, if you're running an Adblocker, we humbly request you to whitelist us.







>