UNIQUE function. Remove duplicates in range of cells.(Microsoft Excel)


The UNIQUE function is a related function of Spill added in 2019 that removes duplicates in a cell range.It is also possible to retrieve only duplicate data.

How it works

arrayRequired argument.
Cell range from which to remove duplicates.
by_colFALSEDirection of duplicate deletion.
TRUE: Column direction.
FALSE: Row direction.
exactly_onceFALSEData to be obtained
TRUE: Unique data only
FALSE: Delete duplicate data


Argument 1: array

Enter a formula and the result will be displayed in the cell range specified in Argument 1.
The result is displayed with duplicates removed in the row direction.
The formula is automatically expanded to cells other than the cell where the formula was entered, but these cells are ghost cells and cannot be edited.

Argument 2: by_col

Specifying TRUE for this argument changes the direction of duplicate removal from vertical to horizontal.
If this argument is omitted, the direction is vertical.

Argument 3: exactly_once

If TRUE is specified for this argument, only non-duplicated data will be displayed.
If all are duplicates, a #CALC will result

If this argument is omitted or FALSE is specified, duplicates are removed and retrieved.

Multiple cell ranges.

Multiple cell ranges can be targeted with the VSTACK and HSTACK functions.



Microsoft Excel Functions Lookup & Reference

Examples of Use.

Calculating the median in a Pivot table. Its alternative method.