How to get distinct values in Excel? (6 ways)

How to get distinct values in Excel? (6 ways)
Table of Contents

Very often we need to get distinct or unique value from a column in Excel. In SQL, it is as simple as Select Distinct. However, we don’t have such thing as Select distinct in Excel. What should we do?

No worries at all! There are a couple of ways to get distinct values in Excel.

In this article, I will show you 6 different ways. They are “Remove duplicates”, “Advanced filter”, “Function”, “Formula”, “Pivot table” and “VBA” respectively.

These ways allow you to deal with almost all kinds of situation when it comes to getting unique values in Excel.

You may also be interested in How to Generate Random Dates in Excel

Example

In this article, we will be using the following list as an example.

On the list, there are “apple”, “banana”, “pear”, “banana”, “orange”, “orange” and “pear” respectively.

Among the list of value, there are a few number of duplicates.

If we are to get the distinct value from the list, they would be “apple”, “banana”, “pear” and “orange”.

It is easy to spot the distinct value if the list is short but that is usually not the case. That is why we will need the following solutions.

Original list
How to get distinct values in Excel? (6 ways)
Distinct values from the original list

Get distinct values in Excel by Remove Duplicates

Step 1: Select the data range

How to get distinct values in Excel? (6 ways)

Step 2: Select Remove Duplicates

How to get distinct values in Excel? (6 ways)

Step 3: Press Remove Duplicates

How to get distinct values in Excel? (6 ways)

Result

How to get distinct values in Excel? (6 ways)

Get distinct values in Excel by Advanced Filter

Step 1: Select the data range

How to get distinct values in Excel? (6 ways)

Step 2: Select Advanced

How to get distinct values in Excel? (6 ways)

Step 3: Check Unique records only and press OK

How to get distinct values in Excel? (6 ways)

Result

How to get distinct values in Excel? (6 ways)

You may be interested in How To Center Cells Across Multiple Columns?

Get distinct values in Excel by Function

UNIQUE function Syntax

UNIQUE(array, [by_col], [occurs_once])

Formula of Cell B1

=UNIQUE(A1:A7) 

Result

How to get distinct values in Excel? (6 ways)

You may be interested in How To Reverse Concatenate In Excel (3 ways)?

Get distinct values in Excel by By Formula

UNIQUE function is purposefully made to get a distinct list of values. The only downside is that UNIQUE() is currently available in certain version of Excel. Not every Excel users get to use this wonderful function.

Fortunately, we can use a formula to replicate the UNIQUE function. The formula is a bit long yet not as complicated as many people think.

To get a better understanding of this formula, you may want to check out How to Get Unique Values Without Unique function? The article explains the formula in details.

Standard Formula

=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$9),0,0),0)),"")

Array Formula

=IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$9), 0)), "")

Result

How to get distinct values in Excel? (6 ways)

You may be interested in How to Sum Intersections of Multiple Ranges?

Get distinct values in Excel by By Pivot table

Step 1: Select the data range

Make you give your data range a header or things may go wrong.

How to get distinct values in Excel? (6 ways)

Step 2: Select PivotTable

How to get distinct values in Excel? (6 ways)

Step 3: Select Existing Worksheet, choose a location and press OK

I selected cell B1 as the location.

How to get distinct values in Excel? (6 ways)

Now your Excel should become like this.

How to get distinct values in Excel? (6 ways)

Step 4: Select the field

The top cell in your data range will be perceived by Excel as the name column header.

By selecting “Fruit”, you are basically telling Excel “Please show the column in my PivotTable”.

How to get distinct values in Excel? (6 ways)

Result

The PivotTable automatically shows distinct values only.

How to get distinct values in Excel? (6 ways)

You may be interested in How to use REPT functions to the Most.

Get distinct values in Excel by VBA

Check out How to Insert & Run VBA code in Excel – VBA101 to see why you should learn VBA.

So basically we are trying to do the following things:

  1. Identify cells that are not unique
  2. Delete those cells

Below are the code that does the things we suggested above.

How to use the code

Step 1: Select cell ranges

Step 2: Run the code

Sub GetDistinctvalue()
    'perform the code only when the active cell is not equal to zero
    Do While ActiveCell.Value <> ""
        'If the value of the cell in loop is equal to any of the cell selected
        If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
            'Select the active cell
            ActiveCell.Select
            'Delete it and move the remaining cells upward so there should be no blank cell
            Selection.Delete Shift:=xlUp
        Else
            'Make the next cell in selection active
            ActiveCell.Offset(1, 0).Activate
        End If
    Loop
End Sub

Result

How to get distinct values in Excel? (6 ways)

You may be interested in How to use REPT functions to the Most.

Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
International Open Academy

Join Our Newsletters!

JOIN OUR NEWSLETTERs!