Update: 30 Aug, 2017h

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. Not only an exceptionally small regular formula, if you want to use that, but also awesome built-in features in Excel that makes your work so much easier.

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference so you can find the information you are looking for on this web page.

The picture above shows a list of values in column B, note value AA has a duplicate. Unique distinct values are all cell values but duplicate values are merged into one distinct value.

To filter duplicates, read this post: Extract a list of duplicates from a column

Column F contains unique values, meaning values that exist only once in column B. Value AA is not in column F because it has a duplicate, in other words, AA is not unique in column B.

What is the easiest way to filter unique or unique distinct values for my scenario?

I would choose the advanced filter if you are not looking for a formula. It lets you quickly filter a unique distinct list.

If you know that you will be extracting unique distinct values from time to time, like in a dashboard or an interactive worksheet, I recommend using a formula and an excel defined table. You won't need to repeat the same steps over and over compared to the advanced filter and that will save you time and repetitive work.

However working with a large data set may slow down the formula calculations considerably depending on your computer hardware, so perhaps the User Defined Function [UDF] is a better choice or even better a pivot table, if you have huge amounts of data to work with.

Conditional Formatting allows you to format cells determined by a built-in rule or a formula you construct. In this post, you will find a Conditional Formatting formula that highlights unique and unique distinct values.

The Excel Pivot table is lightning fast even with huge data tables but it does have a little learning curve and it requires a few steps to set it up but in my opinion, it is totally worth learning how to use pivot tables. You will be surprised how easy it is to start working with Excel Pivot tables.

There is also a useful array formula in this article that extracts a case-sensitive unique distinct list.

I have made an add-in that lets you extract unique, unique distinct and duplicate values and records from multiple worksheets. This allows you to easily bring together data from multiple sources in your workbook.

Table of Contents

  1. Working with unique distinct values
    1. How to extract unique distinct values from a column [Formula]
      1. Video 
      2. How to copy formula values
      3. Explaining formula
      4. Download example workbook
    2. Extract unique distinct values (case sensitive) [Formula]
      1. Video 
      2. Explaining formula
      3. Download example workbook
    3. Filter unique distinct values [Advanced Filter]
      1. Video
    4. Highlight unique distinct values [Conditional Formatting]
      1. Video
      2. Explaining CF formula
    5. Hide duplicate values [Conditional Formatting]
    6. Extract unique distinct sorted values from a cell range [UDF]
      1. Video
      2. Download workbook
    7. Extract unique distinct values [Macro]
    8. Build a list of unique distinct values [Pivot Table]
    9. Filter unique distinct values and records from multiple sheets [Add-In]
      1. Video
    10. How to extract unique distinct values from a column [Array Formula]
      1. How to create an array formula
      2. Explaining formula
      3. Download example workbook
  2. Working with unique values
    1. How to filter unique values from a list [Formula]
      1. Explaining formula
      2. Download workbook
    2. Highlight unique values [Conditional Formatting]
      1. Video
  3. Tips and tricks
    1. Useful tips
    2. The formula is too slow, can I make it faster?
    3. Named ranges
    4. Excel defined tables
    5. Remove errors, Excel version 2007 and later
    6. Remove errors, Excel version 2003 and earlier
    7. Blank cells in range
      1. Video
      2. Download workbook
    8. Useful links

Create a list of unique distinct values [Formula]

Column B contains names, some cells have duplicate values.  A formula in column D extracts a unique distinct list from column B.

Update: 2017-08-15!

This formula is even smaller than the array formula and you are not required to enter this as an array formula.

The formula in cell D3:

=LOOKUP(2,1/(COUNTIF($D$2:D2,$B$3:$B$21)=0),$B$3:$B$21)

Back to top


The following video shows you how to implement and use the formula above:

The formula above extracts a list in no particular order. The following link demonstrates how to filter a unique distinct list, sorted alphabetically:

Create a unique distinct alphabetically sorted list extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Back to top

The following post demonstrates how to extract a unique distinct list sorted from A to Z and removing blank cells:

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

This article shows you how to filter unique distinct values based on a condition:

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Lean how to filter values with a condition and return alphabetically sorted unique distinct values:

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Filter unique distinct values from two different columns:

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Filter unique distinct values from three columns:

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

How to extract unique distinct records:

Filter unique distinct row records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Copy unique distinct values

To copy unique distinct values to another location you must make sure you copy the values and not the formula:

  1. Select list
  2. Copy list, shortcut keys: CTRL + C or press this button:
  3. Right click on destination cell and click on the black arrow next to "Paste Special..."
  4. Then click on "Paste Values" button

Back to top

Explaining formula in cell D3

Step 1 - Count previous values above current cell

The COUNTIF function allows you to count values using a condition. With the help from an expanding cell reference, the formula knows which of the values that have been extracted.

In cell D3 no values have been extracted so it compares the value in the cell above current cell, this happens to be the Header value. Make sure you don't have a value in the list that matches the header value, it won't be extracted.

COUNTIF($D$2:D2,$B$3:$B$21) is entered in column F, displayed in the picture below.

The value in cell D2 is not found in any instance in cell range B3:B21, all values in the array are 0 (zero). Note that the array has the same size as the list in column B, 19 values.

Read more about the COUNTIF function:

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

Step 2 - Compare array with 0 (zero)

To identify values that have not been shown the formula compares the array with 0 (zero) and the result are a boolean values (TRUE or FALSE) for each value in the array.

COUNTIF($D$2:D2,$B$3:$B$21) = 0

The array contains 19 boolean values, all TRUE.

Step 3 - Divide 1 with array

The boolean value TRUE is equal to 1 and FALSE is equal to 0. If a value in the array is TRUE the result will be 1 because 1/TRUE equals 1.

If a value in the array is FALSE the result will be #DIV0! because 1/FALSE is 1/0 and you can't divide a number with zero. Excel returns an error.

The good thing about the LOOKUP function is that it ignores errors, see next step.

Step 4 - LOOKUP value

The LOOKUP function is designed to work with sorted cell ranges or arrays, you get weird results if they are not sorted. Be careful using the LOOKUP function.

However, in this case, the values in the array are either 1 or #DIV0!. Surprisingly it ignores errors, the only thing it can find then is a value that is 1.

The first argument in the LOOKUP function is 2 so the function finds the last largest value that is equal to 2 or smaller.

LOOKUP(2,1/(COUNTIF($D$2:D2,$B$3:$B$21)=0),$B$3:$B$21)

becomes

LOOKUP(2,{1;1;1;1;1;1;1;1;1; 1;1;1;1;1;1;1;1;1;1},$B$3:$B$21) and matches the last value in the array. LOOKUP function then returns the corresponding value in cell range $B$3:$B$21 which is Almagro, Nicolas

Read more about the lookup function:

LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

Back to top

Download excel *.xlsx file

Extract-a-unique-distinct-list-in-excelv4.xlsx

Back to top

Excel 2007 users (and later versions) can remove errors using IFERROR() function

When the formula runs out of values it returns #N/A errors (Not Available), you can use the IFERROR function to remove the error and return blank in those cell.

Unfortunately, it comes with a big disadvantage, it also removes other formula errors as well. So use this with great caution. If your source table has errors you won't detect it because the IFERROR function returns a blank cell instead.

Array formula in cell B2:

=IFERROR(LOOKUP(2,1/(COUNTIF($D$2:D2,$B$3:$B$21)=0),$B$3:$B$21),"")

and copy it down as far as necessary.

Recommended reading:

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Back to top

Excel 2003 users can remove errors using isna() function:

=IF(ISNA(INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))), "", INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)))

and copy it down as far as needed.

This formula is an array formula, how to enter an array formula.

Back to top

How to ignore blank cells in a range

Harlan Grove created a formula to count unique distinct values from a list with blanks. I used the same technique here to filter unique distinct values in column D.

If you want a header name you can use the slightly larger formula, displayed in column F below.

Update 2017-09-01, smaller regular formula in cell D3:

LOOKUP(2, 1/(COUNTIF($D$2:D2, $B$3:$B$21&"")=0), $B$3:$B$21)

Formula in cell F3 is slightly larger than formula above:

=LOOKUP(2, 1/((COUNTIF($F$2:F2, $B$3:$B$21)+($B$3:$B$21=""))=0), $B$3:$B$21)

Watch a video where I explain how these two formulas work

 

This article shows you how to fill blank cells with values or formulas

How to automatically fill all blanks with missing data or formula

Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]

Learn how to extract non-blank cells in a list using a formula:

Remove blank cells

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

Back to top

Download excel *.xlsx file

Extract-a-unique-distinct-list-in-excelv4.xlsx

Back to top

Extract/filter unique distinct values [Advanced Filter]

First a little reminder, unique distinct values are all cell values but duplicate values are merged into one distinct value.

The following video shows you how to filter unique distinct values using Advanced Filter:

Back to top

Instructions - Copy unique distinct values to another location

  1. Go to tab "Data" on the ribbon
  2. Click "Advanced Filter" button on the ribbon

     
  3. Click "Copy to another location"

     
  4. Click "List range:" and select range to filter unique distinct values
  5. Click "Copy to: and select a range
  6. Click "Unique records only"
  7. Click "OK"!

Back to top

Instructions - Filter unique distinct values, in place

If you choose to filter unique distinct values, click on the first option button in the dialog box.

You can then select unique distinct values and paste to another location.

The picture below shows you the selected distinct values after I cleared the Advanced Filter.

Learn more about other powerful excel features

An Advanced Filter is not the only powerful built-in feature in Excel, I highly recommend that you learn pivot tables. Perhaps the most powerful tool but also the least known:

Discover Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

The Excel defined table is also extremely useful, it allows you to quickly sort, filter and manipulate data. Learn that and much more:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Back to top

Highlight unique distinct values [Conditional Formatting]

The following image shows you unique distinct values highlighted using Conditional Formatting.

This video demonstrates how to highlight unique distinct values:

Instructions

  1. Select cell range B3:B21
  2. Go to tab "Home" on the ribbon
  3. Click on "Conditional Formatting" button
  4. Click on "New Rule..."
  5. Click on "Use a formula to determine which cells to format:"
  6. Type this formula: =COUNTIF($B$3:B3,B3)=1
  7. Click on "Format..." button
  8. Pick a color
  9. Click OK button
  10. Click OK button again

Back to top

Explaining Conditional Formatting formula

A CF formula works somewhat differently than a regular formula, it is possible that you can't even see if a cell range has CF applied to it or not, unless you select the cell range and click on the CF button and then click on "Manage Rules...".

The COUNTIF function has two arguments, the first argument is the cell range you want to count a specific value in. The second argument is the value you want to count.

The first argument uses both relative and absolute cell references, $B$3:B3. The absolute part has dollar signs $B$3 meaning it does not change when the Conditional Formatting formula is applied to the next cell.

The relative part B3 does change when the Conditional Formatting formula is applied to the next cell.

In cell B3 the function is COUNTIF($B$3:B3,B3) and in cell B4: COUNTIF($B$3:B4,B4) and so on. This technique lets you highlight the first instance of a value but not duplicate values.

How do we know if the value is a unique distinct value? Compare COUNTIF($B$3:B3,B3) to 1 and it will return TRUE or FALSE, like this: COUNTIF($B$3:B3,B3)=1

Tip! Right-click on a highlighted cell, click on Sort and then on "Put Selected Cell Color On top" to arrange unique distinct values at the very top of your list.

The picture below shows you all unique distinct values sorted together.

Back to top

Extract a unique distinct list (case sensitive)

The following array formula lists unique distinct values from a list and it is case sensitive. Aa is not equal to AA.

Array formula in cell D3:

=INDEX($B$3:$B$15, MATCH(0, FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($D$2:D2)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))), 0))

This video demonstrates how to build a formula that extracts a case-sensitive unique distinct list:

This post shows you how to extract a case sensitive unique list from a column:

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

How to enter an array formula

Back to top

Explaining the array formula in cell C3

Step 1 - Transpose previous values

TRANSPOSE($D$2:D2)

becomes

TRANSPOSE({"Unique distinct list (case sensitive)";"Aa"})

and returns

{"Unique distinct list (case sensitive)","Aa"}

Note that the ; (semicolon) changes to a , (comma)

Recommended reading:

TRANSPOSE function

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Step 2 - Check if two text strings are exactly the same, also case sensitive 

EXACT($B$3:$B$15, TRANSPOSE($D$2:D2))

becomes

EXACT($B$3:$B$15, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT($B$3:$B$15, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"}, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

and returns

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

Step 3 - Return relative position in array if TRUE

IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, {1;2;3;4;5;6;7;8;9})

and returns

{FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE}

Recommended article:

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

Step 4 - Calculate how often values exist in an array

FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},MATCH(ROW($B$3:$B$15),ROW($B$3:$B$15)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},{1;2;3;4;5;6;7;8;9})

and returns

{1;0;0;0;0;0;0;1;0;0} Aa is found in position 1 and 8 in cell range $B$3:$B$15

How to use FREQUENCY function

Returns how many times values exists in a given range. Note, this function returns an array of values.

Step 5 - Find first empty value (0) in array

MATCH(0, FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))), 0)

becomes

MATCH(0, {1;0;0;0;0;0;0;1;0;0}, 0)

and returns 2.

How to use Excel’s MATCH function

Identify the position of a value in an array.

Step 6 - Return value from position 2

INDEX($B$3:$B$15, MATCH(0, FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))), 0))

becomes

INDEX($B$3:$B$15, 2)

and returns "CC" in cell C3.

INDEX function explained

Fetch a value in a data set based on coordinates.

Back to top

Download excel *.xlsx file
Extract-a-unique-distinct-list-in-excel4.xlsx

Back to top

Articles with topic: Case sensitive

Learn to do case sensitive lookups and return multiple values:

Case sensitive lookup and return multiple values

Array formula in E5: =INDEX($B$3:$B$9, SMALL(IF(EXACT($B$3:$B$9, $F$2),MATCH(ROW($B$3:$B$9), ROW($B$3:$B$9)), ""), ROWS($A$1:A1))) Array formula in F5: =INDEX($C$3:$C$9, SMALL(IF(EXACT($B$3:$B$9, $F$2), MATCH(ROW($B$3:$B$9), ROW($B$3:$B$9)), ""), […]

The following article demonstrates a User Defined Function that filters unique distinct values based on case sensivity:

Excel udf: Filter unique distinct values (case sensitive)

How to use udf Select cell range D3:D10 Type =CSUnique(B3:B10) in formula bar. Press and hold CTRL + SHIFT Press […]

Excel udf: Filter unique distinct records (case sensitive)

How to use udf (array formula) Select cell range F3:G6 Type =UniqueRecords((C3:D8) in formula bar. Press and hold CTRL + SHIFT […]

Learn to construct a formula that counts case sensitive unique distinct values:

Count unique distinct values

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

This article shows you how to count multiple text strings in a cell range (case-sensitive)

Count multiple text strings in a cell range

The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]

Back to top

Extract unique distinct sorted values from a cell range [UDF]

This UDF lets you create and sort a unique distinct list. First you need to copy the VBA code to your workbook, instructions below. Second, select a cell range. Third, type FilterUniqueSort(cell_ref) in the formula bar. Last, enter formula as an array formula, instructions below.

There is also a workbook for you to download.

Array formula in cell B2:B8212:

=FilterUniqueSort($A$2:$A$8212)

This video explains how to implement and use the User Defined Function

How to create an array formula

  1. Type B2:B8212 in name box
  2. Type above array formula in formula bar
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

Recommended reading

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Back to top

VBA code

I am using the selection sort function to sort values. You can read more about the function here:

Using a Visual Basic Macro to Sort Arrays in Microsoft Excel

Function FilterUniqueSort(rng As Range)

Dim ucoll As New Collection, Value As Variant, temp() As Variant
Dim iRows As Single, i As Single
ReDim temp(0)

On Error Resume Next
For Each Value In rng
If Len(Value) gt; 0 Then ucoll.Add Value, CStr(Value)
Next Value
On Error GoTo 0

For Each Value In ucoll
temp(UBound(temp)) = Value
ReDim Preserve temp(UBound(temp) + 1)
Next Value

ReDim Preserve temp(UBound(temp) - 1)

iRows = Range(Application.Caller.Address).Rows.Count

SelectionSort temp

For i = UBound(temp) To iRows
ReDim Preserve temp(UBound(temp) + 1)
temp(UBound(temp)) = ""
Next i

FilterUniqueSort = Application.Transpose(temp)

End Function
Function SelectionSort(TempArray As Variant)
          Dim MaxVal As Variant
          Dim MaxIndex As Integer
          Dim i, j As Integer
        
          For i = UBound(TempArray) To 0 Step -1
              MaxVal = TempArray(i)
              MaxIndex = i

              For j = 0 To i
                  If TempArray(j) > MaxVal Then
                      MaxVal = TempArray(j)
                      MaxIndex = j
                  End If
              Next j

              If MaxIndex < i Then
                  TempArray(MaxIndex) = TempArray(i)
                  TempArray(i) = MaxVal
              End If
          Next i

      End Function

Back to top

Where to copy VBA code?

  1. Press Alt + F11 to open VB Editor
  2. Click "Insert" on the menu
  3. Click "Module" to create a module
  4. Copy (Ctrl + c) above VBA code and paste (Ctrl +v)  to the code module

Download example file

Extract unique distinct sorted values from a cell range.xls

Back to top

More powerful User Defined Functions

Excel udf: Filter unique distinct records (case sensitive)

How to use udf (array formula) Select cell range F3:G6 Type =UniqueRecords((C3:D8) in formula bar. Press and hold CTRL + SHIFT […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Filter unique distinct words from a cell range in excel (udf)

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Back to top

Filter unique distinct values from multiple sheets add-in

Filter unique distinct values is an add-in for Excel 2007/2010/2013 that lets you extract

  • unique distinct values
  • duplicate values
  • unique distinct records
  • duplicate records

from multiple sheets. The Add-In contains 4 user-defined functions.

If a value in one of the ranges changes the function will automatically and instantly update the list.

Features

  • All user-defined functions remove blank values and blank records.
  • No error values when all values are extracted.
  • Filter values or records from up to 255 different cell ranges or sheets.

Watch this video where I demonstrate the Excel Add-In

What are unique distinct values?

What are unique distinct records?

Example - How to filter unique distinct values from multiple sheets using the Add-In

Purchase Filter Unique Distinct Values From Multiple Sheets Add-in For Excel 2007/2010/2013 - Price $19 USD

Add to CartView Cart


Questions

Is there a money back guarantee?
Sure, you have a unconditional money back guarantee for 14 days.

Back to top

Useful tips

Excel tables

An Excel defined table is very cleverly designed, it is constructed to automatically expand if you add more data. You don't need to do anything.

Structured references are cell references to an excel defined table. They let you easily see what the data contains as long as you give it good descriptive column header names.

I recommend you use excel defined tables instead of named ranges or dynamic named ranges as long as you are working with more than one value.

Here is how to convert a list to an excel defined table:

  1. Select a cell in your list
  2. Go to tab "Insert" on the ribbon and click Table button or press Ctrl + T
  3. Click OK button
  4. Your excel defined table is created

Read more about excel defined tables

Back to top

Named ranges

In excel you can name a cell range, a constant or a formula. You can then use the named range in a formula, making it easier for you to read and understand formulas.

Example

List : A2:A20

Tip! Use dynamic named ranges to automatically adjust cell ranges when new values are added or removed.

How to create a named range

The downside with named ranges is that you need to adjust the range every time you add or delete a value in the list, the named range will then not fit the value list. I recommend using excel defined tables if you know that the list may change in the future.

  1. Select cell range A2:A20
  2. Type List in name box
  3. Press Enter

Array formula and named range in cell B2:

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))

Back to top

Create a list of unique distinct values [Array Formula]

I recommend using the regular formula above since it is smaller and has an advantage of not being an array formula.

Array formula in cell D3:

=INDEX($B$3:$B$21, MATCH(0, COUNTIF($D$2:D2, $B$3:$B$21), 0))

Thanks to Eero, who contributed the original array formula!

Back to top

The formulas above has an issue with blank cells, it returns a 0 (zero) in your list. This article shows you how to ignore blanks:

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several […]

How to create an array formula

You don't need to follow these steps if you chose the regular formula.

  1. Copy the array formula above (Ctrl + c)
  2. Double click cell B2
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter
  6. Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
{=INDEX($B$3:$B$21, MATCH(0, $D$2:D2, $B$3:$B$21), 0))}

Don't enter these characters yourself, they appear automatically.

Copy cell B2 and paste to cells below as far as needed.

Back to top

How the array formula in cell B2 works

Step 1 - Create an array with the same size as the list

COUNTIF($B$1:B1,$A$2:$A$20)

becomes

COUNTIF("Unique distinct list",{Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas} )

and returns:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

This means the cell value in $B$1:B1 can´t be found in any of the cells in cell range $A$2:$A$20. If it had been found, somewhere in the array the number 1 would exist.

Recommended reading:

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

Step 2 - Return the position  of an item that matches 0 (zero)

MATCH(lookup_value,lookup_array, [match_type] returns the relative position of an item in an array that matches a specified value.

MATCH(0,COUNTIF($B$1:B1,$A$2:$A$20),0)

becomes

MATCH(0,{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

and returns 1.

Recommended reading:

How to use Excel’s MATCH function

Identify the position of a value in an array.

Step 3 - Return a cell value

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

=INDEX(List,1)

becomes

=INDEX({Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas}, 1)

and returns "Federer, Roger"

Recommended reading:

INDEX function explained

Fetch a value in a data set based on coordinates.

Relative and absolute cell references

When you copy the array formula down the countif formula range ($B$1:B1) expands. This is created by using relative and absolute references.

The first cell, B2: COUNTIF($B$1:B1,$A$2:$A$20)

Second cell, B3: COUNTIF($B$1:B2,$A$2:$A$20)

and so on.

Recommended reading:

How to use absolute and relative references in excel

What is a refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

Back to top

How to filter unique values from a list

Unique values are values existing only once in a list. Example, AA exists twice in the list below and is not unique. BB and CC exist only once each and is unique in the list.

Column D in the picture below filters all unique values from column B. Unique values are values that exist only once in column B.

Example, Roger, Federer is not in column D because there is more than one value of this name in column A. In other words, the name is not unique in column A. You can find the name twice in the list, in cell A2 and A8.

Update 2017-08-30 
This formula is even smaller than the array formula and you are not required to enter this as an array formula.

Formula in cell D3:

=LOOKUP(2, 1/((COUNTIF(D2:$D$2, $B$3:$B$21)=0)*(COUNTIF($B$3:$B$21, $B$3:$B$21)=1)), $B$3:$B$21)

This post shows you how to extract a case sensitive unique list from a column:

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

Learn to create a list of unique values extracted from a cell range:

Extract unique values from a range using array formula in excel

Question: How do I extract values only occuring once in a range? Answer: A range (tbl_text) containing text values Array […]

The following article shows you how to filter unique values sorted alpabetically:

Filter unique values sorted from A to Z

Introduction A unique list contains cell values that only exist once. A unique distinct list contains all cell values but […]

Recommended article

Create unique list from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

Array formula in D3:

=INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+(COUNTIF($B$3:$B$21, $B$3:$B$21)<>1), 0))

How to enter an array formula

Back to top

Explaining array formula in cell D3

Step 1 - Count each value in array and check if it is not equal to one

(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}<>1

and returns

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}

This array tells excel that the first value in the array is not unique and that is true because Roger, Federer is not unique in the list. However the second value is FALSE and that value is unique, etc.

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

Step 2 - Keep track of previous values

C1:$C$1 is a dynamic cell reference, it changes as the formula is copied to cells below. You can read more about absolute and relative cell references here:

How to use absolute and relative references in excel

What is a refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

COUNTIF(C1:$C$1, $A$2:$A$20)

becomes

COUNTIF("Unique list", {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

A zero (0) means that no values have yet been displayed and that is true in cell C2. However when excel calculates the value in cell C3, cell C2 shows "Djokovic, Novak" and the array becomes {0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}. The second value in the array contains 1. This tells excel that value has already been shown.

Step 3 - Add arrays

COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} + {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

and returns

{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}

TRUE is 1 and FALSE is zero. So True + 0 equals 1 and False + 1 equals 1.

Step 4 - Find first zero value in array

A zero in the array indicates {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0} that the corresponding value is unique and has not yet been displayed in the list.

MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0)

becomes

MATCH(0, {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, 0)

and returns 2.

How to use Excel’s MATCH function

Identify the position of a value in an array.

Step 5 - Return corresponding value

INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0))

becomes

INDEX($A$2:$A$20, 2)

and returns "Djokovic, Novak" in cell C2.

INDEX function explained

Fetch a value in a data set based on coordinates.

Back to top

Download excel *.xlsx file

Extract-a-unique-distinct-list-in-excelv7.xlsx

To extract duplicates, see this post:

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Back to top

Highlight unique values [Conditional Formatting]

The following video shows you how to color unique values using conditional formatting. Remember, it highlights only unique values, in other words values that exist only once in the list.

Instructions

  1. Go to tab "Home" on the ribbon
  2. Click "Conditional Formatting" button
  3. Hover over "Highlight Cell Rules"
  4. Click on "Duplicate Values..."
  5. Click on the leftmost drop-down list and change it to "Unique"
  6. Pick a formatting if you like
  7. Click OK button

The picture above shows you, for example, that the first name in the list has a duplicate so that name is not highlighted in any cell.

Tip! Did you know that you can put highlighted values to the top

  1. Right-click on a highlighted cell
  2. Click on "Sort"
  3. Click on "Put Selected Cell Color On Top"

Back to top

Useful blog posts

Want to learn more about filtering unique distinct values? You must read these blog posts:

Learn more about sorting unique distinct values. Read these blog posts:

Learn more about counting unique distinct values and records. Read these blog posts:

Learn more about filtering and comparing unique distinct records. Read these blog posts:

Read more about custom functions in excel