The Easiest Way to Remove Duplicate Data in Excel

Using the COUNTIF or COUNTIFS Formula

The second way to remove duplicates in Excel is to use the formula “COUNTIF” or “COUNTIFS”. This formula can be used to count the number of times a value appears in one or more columns or rows. Using this formula, you can mark duplicates by providing specific values, such as 1 for unique data and 0 for duplicate data. Then, you can filter or remove the data marked as duplicate according to your need. The following are the steps to use the “COUNTIF” or “COUNTIFS” formula:

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. Select an empty cell to the right or bottom of your data, depending on whether you want to mark duplicates based on columns or rows.
3. Type the formula “COUNTIF” or “COUNTIFS” according to the criteria you want. “COUNTIF” formulas are used to mark duplicates based on a single column or row only, while “COUNTIFS” formulas are used to mark duplicates based on more than one column or row. The general format of this formula is as follows:

=COUNTIF(range,criteria)
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,...)

Where:

3. range is the range of cells that you want to count the number of times a value appears in it.
4. criteria is the value you want to find in that range of cells. You can use direct values, cell references, or logical expressions to define criteria. For example, “Budi”, A2, or “>25”.
5. criteria_range1, criteria_range2,… is the range of cells that you want to use as criteria to mark duplicates. You can use more than one range of cells by separating them with commas.
6. criteria1, criteria2,… is the value you want to search in that range of criteria cells. You can use direct values, cell references, or logical expressions to define criteria.

4. Press the “Enter” button to display the formula result. If the result is more than 1, there are duplicates in your data. If the result is 1, the record is unique. If the result is 0, the data doesn’t exist in the range of cells you specified.
5. Repeat steps 3 and 4 for all records that you want to mark duplicates. You can use the “Fill (Ctrl + R)” or “(Ctrl + D” ) feature to fill formulas to other cells automatically.
6. After all data is marked with formulas, you can filter or delete the data that has a value of 0 or more than 1 according to your need. You can use the “Filter” feature on the “Data” tab to filter data by a specific value or use the “Sort” feature on the “Home” tab to sort data by a specific value. Then, you can delete the unwanted data by pressing the “Delete” button.

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

If you want to mark duplicates based on the Name column, then you can do as following steps:

1. Select the entire data, and then select the blank cell to the right of your data.
2. Type the formula =COUNTIF($A$2:$A$7,A2) in the blank cell. This formula counts the number of times the value in cell A2 appears in the range A2:A7. If the value appears more than once, then it is a duplicate. If the value appears only once, then it is unique data.
3. Press the “Enter” button to display the formula result. Here, the result is 2, because there are two “Budi” in the Name column.
4. Repeat steps 2 and 3 for all records that you want to mark duplicates. You can use the “Fill” feature to fill formulas to other cells automatically.
5. After all, data is marked with formulas, you can filter or delete the data that has values over 1 according to your needs. For example, if you want to filter unique data, then you can use the “Filter” feature to select only 1 value in the formula field.

As a result, your data will look like this: