change language:

You can filter out unique values ​​from a column of data in Excel, for example with array formulas. Suppose a column in Excel contains the data below:

Now to display in column B the unique values ​​of the data of column A, the following array formula can be used in cell B2:

display unique values 1

=IFERROR(INDEX(A$2:A$15,MATCH(FALSE,ISNUMBER(MATCH(A$2:A$15,$B$1:B1,0)),0)),"")

This is an array formula and must therefore be closed with CTRL+SHIFT+ENTER when entering. After that, curly braces automatically appear around the formula, indicating that it is an array formula. The formula can then be copied down. Then the following overview is created:

display unique values 2

It can be seen that in column B only the unique values ​​from column A are displayed.

Short explanation

Finally, a brief explanation of the formula used. It is a nested matrix function whose operation is difficult to explain in words. Basically, the last MATCH function looks for values ​​from the source column in the parent rows. The first MATCH function looks for the first position, where the last MATCH function cannot find anything and so ISNUMBER becomes false. Then the INDEX function displays the value of this position. If the INDEX function returns an error because all values ​​have already been found, an empty cell is displayed.

Excel Office 365

In Excel for Office 365, array formulas do not normally have to be closed with CTRL+SHIFT+ENTER, but it is allowed. In addition, the UNIQUE function may be available in this version. This depends on the type of subscription. If this function is available, you can use the following formula instead of the above reasonably complex array formula:

=UNIQUE(A2:A15)

Display unique values with pivot table

A PivotTable can also be used to display unique values. To do this, select the data in A2:A15 and choose insert PivotTable. Then at PivotTable Fields, drag the field to ROWS. Then the unique values ​​are displayed alphabetically with the possibility to filter this. The display of the Grand Total in de PivotTable can be removed with a right click. A disadvantage of the PivotTable is that it is not automatically adjusted when the values ​​in A2: A15 change. This can be taken care of with a macro, but that will not be considered here.

Questions / suggestions

Hopefully this article helped you to display unique values in Excel. If you have any questions about this topic or suggestions for improvement, please post a comment below.

arrow_up