Unique items in the list

For example, if you have a range:

Extract unique values with advanced filter

On the Data tab, in the Sort & Filter group, choose the Advanced button:

In the Advanced Filter dialog box:

• In the Action group, choose the Copy to another location or leave the Filter the list, in place option, if you prefer to rewrite the source list.
• In the List range field, choose a range (E.g., $A$1:$A$10).

  Note: Excel defines lists with headers, so the first cell of the range will be duplicated.

• If available, in the Copy to field, choose a new location (E.g., C1:C10).
• Select the Unique records only option.
• Click OK:

Extract unique values with UNIQUE formula

The UNIQUE function returns a list of unique values in a list or range:

= INIQUE (<data range>, [col_row], [exactly_once]):

where:

• The optional parameter [col_row] can be:
  • TRUE, returns unique values in the columns of the <data range>,
  • FALSE (used by default), returns unique values in the rows of the <data range>.
• The optional parameter [exactly_once] can be:
  • TRUE, returns items that appear exactly once in the columns or rows of the <data range>. For example:
    
  • FALSE (used by default), returns every unique item from the columns or rows of the <data range>.

Note: This formula results can be referenced using the spilled range operator, #.

For example:

• In another formula:
  
• For the new named range as an absolute reference:
  
• In the data validation (see How to create a dependent drop-down list):
  

See also this tip in French: Comment utiliser les éléments uniques de la liste.