If I have a column with values, and I want to find out what distinct values are in there (not how many - but the actual distinct values), how can I do that?

In SQL Server I would do something like

SELECT Distinct(MyColumn) FROM MyTable

7 Answers 7


Simpler than you might think:

  • Click the Data Ribbon Menu
  • Select the Advanced Button in the Sort & Filter section
  • Fill in the dialog Box, copying the results to another location and making sure you tick Unique records only

enter image description here

  • 39
    For those of us still living a ribbon-free existence, it's Data->Filter->Advanced. Oct 1, 2009 at 16:39
  • 11
    +1 This method is superior to Remove Duplicates if you want to keep the original data intact.
    – Excellll
    Jul 13, 2012 at 0:58
  • 22
    If copying data to another sheet, you must initiate the Advanced dialog from the sheet you want to copy to, otherwise you get a You can only copy filtered data to the active sheet error.
    – Dave Zych
    Jan 23, 2015 at 17:08
  • 11
    This doesn't update when the source range values change. Is there any way to make it dynamic?
    – hughes
    Apr 27, 2016 at 21:24

Excel 2007 and 2010:
Use the Remove Duplicates menu option under the Data header.

Excel 2003:
Easy way:

  1. Make sure your data has a header
  2. Data --> Filter --> Advanced Filter
  3. Check Unique Records Only
  4. Select Copy to another location
  5. Click OK

Hard way:

Write a macro with the following code:

'Remove duplicates from sorted list
Sub getDistinct()
    Do While ActiveCell.Value <> ""
        If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
            Selection.Delete Shift:=xlUp
            ActiveCell.Offset(1, 0).Activate
        End If
End Sub

That gives you your distinct list. You may want to copy your list to another sheet first.


Or (a simple crude way):

In B1,


and copy down. It just copies the first occurrence of each value across (in the row that it occurs in).

  • 3
    As with all Excel formulas, you may need to use semicolons (;) instead of commas (,) based on your system’s regional settings. Sep 20, 2017 at 23:12

I use two main methods to get distinct values - the first is the invaluable Remove Duplicates tool as suggested by Nathan DeWitt.

The downside to Remove Duplicates is that you lose data. Despite the power of Ctrl+z you may not wish to do this for various reasons - eg you may have filters currently selected that make it quite messy to remove duplicates to get your distinct list of values.

The other method, not mentioned above is to use a Pivot Table.

Let's say this is your table and you want to get the distinct list of Platforms. dummy table

Insert a Pivot Table with the table as your data source.

Now select the Platform(s) column as your Row Labels.

Voila! A list of distinct values.

pivot table as distinct value list

If you wish to tidy this up, you can switch off Field Headers and set Grand Totals to be Off for Rows and Columns.


Or you can include the filter option in a macro

    Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

For me, the most simple way to sort is to use "Filter" option.

First step is to find the duplicate values with Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.

Then click the arrow on the Column you have the duplicates (in this case "Platforms") -> Filter by Color (Cells color or font color).

  • 1
    The outcome here is different, as all instances of duplicates are removed. E.g. the column with values [a, a, b] should reduce to [a, b], not [b]. Feb 13, 2018 at 10:13
  • @HugoIdeler, "all instances of duplicates are removed", exactly what I am needing! Feb 19, 2020 at 20:48

In Excel 365 we now have UNIQUE(), which returns an array of unique rows.

  • Thanks - I had not noticed this function appear. Could you expand your answer to explain how to use UNIQUE() please so anybody unfamiliar with Excel can apply this?
    – Andi Mohr
    Sep 2, 2021 at 11:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .