What is the formula to automatically remove duplicates in excel

Answer

To automatically remove duplicates in Excel, you can use the UNIQUE function.

The UNIQUE function extracts distinct values from a range or array.

Syntax   

UNIQUE(array, [by_col], [exactly_once])

• array: The range of cells to check for duplicates.

• by_col: Optional. Set TRUE to compare columns, FALSE (default) to compare rows.

• exactly_once: Optional. Set TRUE to return values that appear only once.

Example

Suppose you have a list of names in cells A1:A10 with some duplicates:

You can remove duplicates by using the formula: 

=UNIQUE(A1:A10)

The result will be:

Remove values that appear only once

If you want values that appear only once (excluding all duplicates), use: 

=UNIQUE(A1:A10, , TRUE)

This will return only Peter, Bob, and Sam from the previous example.

Notes

1. The UNIQUE function is available in Excel 365 and Excel 2021.

2. On older Excel versions, use Remove Duplicates in the Data tab manually or use a combination of IF, COUNTIF, and FILTER formulas.

Do you want me to show the formula method for older Excel versions that do not support UNIQUE?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report