How to Get a List of the Unique Values in an Excel Spreadsheet

Written by melissa kelly
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Get a List of the Unique Values in an Excel Spreadsheet
There are 3 ways to obtain a list of unique values in Microsoft Excel. (Chart analysis image by Dmitriy Lesnyak from

In Microsoft Excel 2007, there are 3 basic ways to view and/or generate a list of unique values, including using an advanced filter, removing duplicates or applying conditional formatting. The method you choose depends upon whether you wish to simply filter values by temporarily hiding duplicates or whether you want to permanently remove values from your spreadsheet. The third option, conditional formatting, simply highlights the unique values for ease of viewing within the spreadsheet.

Skill level:

Other People Are Reading

Things you need

  • Microsoft Excel

Show MoreHide


  1. 1

    Highlight the cells you wish to filter for unique values.

  2. 2

    Click the "Data" tab and select "Sort & Filter," then "Advanced." Inside the box that pops up, choose "Filter the list, in place" if you wish to filter the data within the spreadsheet. You can also copy the filtered list into a new table by clicking "Copy to another location," then selecting the cell range you wish to paste by entering the range in the "Copy to" field.

  3. 3

    Choose the check box for "Unique records only," then click "OK" to generate the list of values.

  1. 1

    Highlight the cells from which you wish to remove duplicate values.

  2. 2

    Navigate to the "Data" tab and select "Remove Duplicates," which is located in the data tools group.

  3. 3

    Select one or more columns under the "Columns" section in the pop up box, based upon those columns from which you wish to remove duplicates. Click "OK."

  4. 4

    A message will display showing how many values were removed from the data table and how many unique values are left. Document the information you need, then click "OK" to view the table.

  1. 1

    Highlight the cells to which you wish to apply conditional formatting for unique values.

  2. 2

    Select "Conditional Formatting" under the "Home" tab and within the "Style" group,

  3. 3

    Choose "Manage Rules," then select "New Formatting Rule."

  4. 4

    Ensure the cell range and worksheet displayed in the "Show formatting rules for" box are appropriate. Then select "Edit rule."

  5. 5

    Select "Format only unique or duplicate values." in the Edit Formatting Rule box that pops up. In the "edit the rule description" field, choose "Format all" as the list and then choose "unique" or "duplicate," depending upon which items you want to highlight. Choose the type of formatting you want (i.e., colour, font, fill) then click "OK."

Tips and warnings

  • Prior to obtaining a list of unique values, ensure your data is formatted consistently. For example, if the same date is listed as 8/27/83 in one cell and August 27, 1983 in another cell, Excel will treat each as a unique value, even though they are the same date.
  • In Excel 2003, unique values can be identified using filters, just as described for Excel 2007.
  • Conditional formatting and removal of duplicate values are not readily available in Excel 2003.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.