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:

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