5 Ways To Get Unique Values In Excel

Are you looking to get unique values (or remove duplicate values) in Excel, and as quick as possible? Have no fear - doing so is easy! Excel formula, Advanced Filter, Remove Duplicates command, PivotTable, and VBA as ways of extract unique values quick.

Method 1 of 5: extract unique values using Excel formula

Formula

For example: range A2:A16 contains duplicate values, an array formula in B2:B16 extracts an unique distinct list from column range A1:A16. You can copy below formula to B2, and press CTRL+SHIFT+ENTER, then drop down B2 to B16.

For Excel 2007 and later:

=IFERROR(INDEX($A$2:$A$16, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$16), 0)),"")

For Excel 2003:

=IF(ISNA(INDEX($A$2:$A$16, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$16), 0))),"",INDEX($A$2:$A$16, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$16), 0)))

Note: changing the range address ($A$2:$A$16) to suit your needs.

Video

Method 2 of 5: Advanced Filter for unique values

Step by step

1. Select the range of cells, or make sure the active cell is in a table.
2. On the Data tab, in the Sort & Filter group, click Advanced.
   
3. In the Advanced Filter dialog box, do one of the following:
   1. To filter the range of cells or table in place, click Filter the list, in-place.
   2. To copy the results of the filter to another location, do the following:
      1. Click Copy to another location.
      2. In the Copy to box, enter a cell reference. eg. C1. or click Collapse Button  to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Button .
4. Select the Unique records only check box, and click OK. The unique values from the selected range are copied to the new location.

Video

Method 3 of 5: remove duplicate values in Excel 2010 and later

Step by step

When you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.

Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

1. Select the range of cells, or make sure that the active cell is in a table.
2. On the Data tab, in the Data Tools group, click Remove Duplicates.
   
3. In the Remove Duplicate dialog box, do one or more of the following:
   • Under Columns, select one or more columns.
   • To quickly select all columns, click Select All.
   • To quickly clear all columns, click Unselect All.
   • If the range of cells or table has headers, select My data has headers.
   • Tips: if the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click, and then under Columns, select those columns.
4. Click OK. A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.
5. Click OK to close the message box.

Video

Method 4 of 5: get unique values with PivotTable

Step by step

1. Select the range of cells, or make sure that the active cell is in a table.
2. On the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.
   
3. In the Create PivotTable dialog box, do one or more of the following:
   • In Table/Range: select the data that you want to analyze.
   • In Choose where you want the PivotTable report to be placed, choose Existing Worksheet, then enter a cell reference. eg. B1. or click Collapse Button  to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Button .
4. Click OK
5. In the PivotTable Field list widow, to add fields to the report, do one or more of the following:
   • To place a field in the default area of the layout section, select the check box next to the field name in the field section.By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and date and time hierarchies are added to the Column Labels area.
   • To place a field in a specific area of the layout section, right-click the field name in the field section, and then select Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values.
   • To drag a field to the area that you want, click and hold the field name in the field section, and then drag it to an area in the layout section.
6. Click Close.

 Video

Method 5 of 5: get unique values with VBA

VBA Code

Sub GetUniqueValues()
    Dim data As Variant, temp As Variant
    Dim obj As Object
    Dim i As Long
    Set obj = CreateObject("scripting.dictionary")
    data = Selection
    For i = 1 To UBound(data)
        obj(data(i, 1) & "") = ""
    Next
    temp = obj.keys
    Selection.ClearContents
    Selection(1, 1).Resize(obj.Count, 1) = Application.Transpose(temp)
End Sub

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert -> Module.
   
4. Type or paste the code in the newly created module.
5. Close the VBE widow.
6. Select the range which you want to remove duplicate values.
7. On the Developer tab, in the Code group, click Macros.
8. Select the macro which you want to run, in this case we select GetUniqueValues, then click Run.