Author: Oscar Cronquist Article last updated on February 09, 2019

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog post using an array formula.

If you are looking for filtering unique distinct values sorted from A to Z, see this blog post: Extract a unique distinct list sorted from A-Z from range in excel

Array formula in cell B8:

=TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$2:$E$5, $B$2:$E$5)=1)+COUNTIF(B7:$B$7, $B$2:$E$5))=1, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, $B$2:$E$5, ""))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell B8

Step 1 - Identify unique values

The COUNTIF function counts values based on a condition or criteria, if number is 1 then value must be unique.

COUNTIF($B$2:$E$5, $B$2:$E$5)=1

becomes

COUNTIF({"Banana", "Orange", "Pineapple", "Lemon";"Grapefruit", "Watermelon", "Apple", "Orange";"Pear", "Blueberry", "Grapefruit", "Blackberry";"Pineapple", "Blueberry", "Cranberry", "Banana"}, {"Banana", "Orange", "Pineapple", "Lemon";"Grapefruit", "Watermelon", "Apple", "Orange";"Pear", "Blueberry", "Grapefruit", "Blackberry";"Pineapple", "Blueberry", "Cranberry", "Banana"})=1

becomes

{2,2,2,1;2,1,1,2;1,2,2,1;2,2,1,2}=1

and returns

{FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}.