What is the excel formula to remove duplicates from list

Answer

To remove duplicates from a list in Excel, the UNIQUE function is used.

Explanation

The UNIQUE function returns a list of unique values from a range or array. It automatically filters out repeated entries, leaving only distinct values.

Syntax  

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

• array: The range or array containing values to check.

• [by_col]: Optional. TRUE to compare columns, FALSE to compare rows. Default is FALSE.

• [exactly_once]: Optional. TRUE to return only values that appear exactly once, FALSE to return all unique values. Default is FALSE.

Example

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

To get a list without duplicates, use the formula: 

=UNIQUE(A1:A10)

The result will be:

Notes

1. The UNIQUE function works in Excel 365 and Excel 2021. It is not available in older versions like Excel 2016 or 2019.

2. For older Excel versions, you can remove duplicates using Data → Remove Duplicates from the ribbon, or by combining IF, MATCH, and COUNTIF formulas, though it is more complex.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report