Unique items in the list

Excel 2016 2013 2010 2007 2003
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.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more