How to remove duplicates from Excel Columns? COUNTIF Example

To remove duplicates from an Excel column using the COUNTIF function, you can follow these steps:

Assuming your data is in column A, insert a new column next to it (e.g., column B) to store the unique values.

In cell B1, enter the following formula to check if the value in cell A1 is the first occurrence in the column:

excel

=IF(COUNTIF($A$1:A1, A1) = 1, A1, "")

This formula uses the COUNTIF function to count the occurrences of the value in cell A1 from A1 to the current row. If the count is 1, it means it's the first occurrence, so the value is displayed; otherwise, an empty string is returned.

Drag the formula from B1 down to the last cell in column B to apply the formula to the entire column.

Select the entire column B, copy it (Ctrl+C), and then paste it (Ctrl+V) as values back into column A (paste values only).

Now, column A will contain only the unique values from the original column. You can safely remove column B if you no longer need it.

This approach using the COUNTIF function effectively filters out duplicate values and retains only the unique ones in the column.