# 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)))``

## 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.

## 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.

## 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.

## 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.
1. CA
Caro

What the value '& ""' is used for? in 'obj(data(i, 1) & "") = ""'

2. JU
Juan

Great job!

3. MI
Michael

You may call me nit-picker, but using select / selection should not be used in code

4. KO
Kongju_na

can we use the same formula in method 1, excel version 2007 and up if there are blank cells in between the values in a column?

5. KE
Keyur

Macro Code is showing error. Run time Error: 13 Type mismatch.

• AS
Ash

@Keyur You need to select (highlight) the data first... then run the code.

6. KR
kr

Macro Code is showing error.