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.

Step 2: Click on the Home section.

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

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

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

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.

Step 2: Click on the Data section.

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

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.

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