Unique items in the list

Excel 2016
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:

Data in Excel 365

Extract unique values with advanced filter

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

Advanced button of Sort and Filter in Excel 365

In the Advanced Filter dialog box:

Advanced Filter Excel 365
  • 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:
Unique items from the list in Excel 365

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]):

Unique items using UNIQUE in Excel 365

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:
      Unique items using UNIQUE 1 in Excel 365
    • 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:

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.