Unique items in the list
Excel
365
To get unique items from a range, you can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location.
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>.
- TRUE, returns items that appear exactly once in the columns or rows of the <data range>. For example:
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.