How to remove duplicate rows in excel

There are multiple ways to identify the duplicate in the excel and removing the duplicate is a one-click trick.

First, we will learn how to identify the duplicate in the excel. Initially, you need to find a unique data column or relative unique data column in the excel.

Example. The serial number, Ticket numbers, System generated ID's or any identical column it depends on the data

Identify the duplicate in the excel Use condition formatting

1. Click on New Rule
2. Select Format only unique or duplicate value
3. Select duplicate value
4. Select duplicate value

Identify the duplicate in the excel Use a countif function

COUNTIF Function to find Duplicate

1. Select the range you want to remove duplicate rows. If you want to delete all duplicate rows in the worksheet, just hold down Ctrl + A key to select the entire sheet.
2. Select on Data ribbon, click Remove Duplicates in the Data Tools group.
3. In the Remove Duplicates dialogue box, leave all the checkboxes checked under Columns list box, and if your data contains headers, please check My data has headers option
4. Then click OK, a prompt box will pop out to tell you the removing result, and all identical rows are removed except for the first identical row

The COUNTIF Function is a really simple and fast way to find the duplicate in an excel sheet.

As shown in the figure. The Job Name column is relatively unique. Hence, we can count the number of a name which has the same value using =COUNTIF(A2: A8, A2)

Countif column, if you get any value greater 1 its a duplicate. Similarly here "Williams" is appearing twice. hence we are getting 2 in the count if column. we have to remove the entire row.

Steps to remove the Duplicate in given data points

Note: With this function, you can also remove rows with the same values in certain columns. To do this, you just need to check the columns that you want to remove the duplicate values in the Remove Duplicates dialogue box in step 3