How to Get Unique Values from Range in Excel (8 Methods)

Sometimes working with Excel, you will need to find out the unique values in a column or a range. For example, you might need to find out how many distinct or unique products a shop has in its inventory or how many unique employee names are there in an Excel sheet with the information about all the employees of a large company. This tutorial will show you multiple ways to get unique values from range in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


8 Easy Methods to Get Unique Values from Range in Excel

Let’s assume a scenario where we have an Excel file that contains information about the products that a country exports to different countries in Europe. We have the Product name, exported Amount, and the Country to which the product is exported. We will find out every unique product that this country exports and each distinct country that this country exports the product to using Advanced Filter, INDEX and MATCH formula together, LOOKUP and COUNTIF function together, UNIQUE function (Excel 365), VBA macro, and Remove Duplicates. The image below shows the Excel worksheet we are going to work with.

Excel Get Unique Values from Range


1. Advanced Filter to Get Unique Values From Range

You can use the Advanced Filter under the Data ribbon to get all the unique values of a range or column. Do the following:

Step 1:

  • First, go to the Data. Select Advanced from the Sort & Filter section.

Advanced Filter to Get Unique Values From Range

  • A new window titled Advanced Filter will appear. Choose Copy to another location as Action.
  • In the List Range box, select the range you want to extract the unique values from. In this example, we are trying to get all the unique or distinct products under our Product column (B5:B20). So, our List Range will be $B$5:$B$20. $ signs have been inserted to make the cell reference absolute.

List Range will be $B$5:$B$20

  • In the Copy to box, we will select a range where we want our unique values to be. We have selected the range E5:E20. Check the box with the title Unique records only.
  • Click OK.

Step 2:

  • Upon clicking OK, you will get all the distinct products in the Unique Products column (E5:E20).

Distinct Products in the Unique Products Column

Read More: How to Extract Unique Items from a List in Excel (10 Methods)


2. Insert the INDEX and MATCH Formula to Get Unique Values From Range

We can also use the Excel INDEX and MATCH functions together to get the unique values from a range or column. Just follow the below steps to use these functions to get unique values from the range.

Step 1:

  • Select cell E5. Write down the below formula in the cell.
=INDEX(B5:B20,MATCH(0,COUNTIF($E$4:E4,B5:B20),0))
Formula Explanation

The driving force of this formula is the INDEX function which will perform the basic lookup.

=INDEX(array, row_num, [column_num])  

INDEX function has two required arguments: array and row_num.

So, if we provide the INDEX function with an array or list as the first argument and a row number as the second argument, it will return a value that will be added to the unique list.

We have provided B5:B20 as the first argument. But the hard part is to figure out what we will give the INDEX function as the second argument or row_num. We have to choose the row_num carefully so that we will only get unique values.

We will achieve this using the COUNTIF function.

=COUNTIF($E$4:E4,B5:B20)  

COUNTIF function will count how many times items in the Unique Product column appear in the Product column which is our source list.

It will use an expanding reference. In this case, it is $E$4:E4. On one side, an expanding reference is absolute, while on the other, it is relative. In this scenario, the reference will extend to include more rows in the unique list as the formula is copied down.

Now that we have the arrays, we can start looking for row numbers. To find zero values, we use the MATCH function, which is set up for the precise match. If we use MATCH to combine the arrays generated by COUNTIF, the MATCH function locates the items while searching for a count of zero. When there are duplicates, MATCH always returns the first match. So, it will work.

Finally, INDEX is provided the positions as row numbers, and INDEX returns the name at these positions.

Note: This is an array formula. So, you must press CTRL+SHIFT+ENTER together to insert the formula in a cell. It will put two curly braces around the whole formula.

Insert the INDEX and MATCH Formula to Get Unique Values From Range

Step 2:

  • Upon entering the formula, you will get the value Apple in cell E5. We will drag the fill handle downward to apply the formula to the rest of the cells.

Excel Get Unique Values from Range

  • After releasing the fill handle, we will get all the unique values in the Unique Products.

Unique Values in the Unique Products Column

Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)


3. Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells

Sometimes the range we are wanting to extract the unique values from might have some empty cells. In such a case, we have to modify the formula a little bit to take the empty cells into account. For example, we have removed some of the products from the range. The image below shows the modified excel sheets with the product column having some empty cells in it.

Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells

We will now get the unique values from this range with empty cells following the below steps.

Step 1:

  • First, we will write down the following formula in Cell E5.
=INDEX(B5:B20, MATCH(0,IF(ISBLANK(B5:B20),1,COUNTIF($E$4:E4, B5:B20)), 0))
Note: This is an array formula. So, you must press CTRL+SHIFT+ENTER together to insert the formula in the cell. It will put two curly braces around the whole formula.

Excel Get Unique Values from Range

Step 2:

  • Upon entering the formula, you will get the value Apple in cell E5. We will drag the fill handle downward to apply the formula to the rest of the cells.

Drag the Fill Handle to Apply the Formula to the Rest of the Cells

  • After releasing the fill handle, we will get all the unique values in the Unique Products.

Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells


4. Use the LOOKUP and COUNTIF Formula to Get Unique Values From Range

We can also use the Excel LOOKUP and COUNTIF functions together to get the unique values from a range or column. Just follow the below steps to use these functions to get unique values from the range.

Step 1:

  • Select cell E5. Write down the below formula in the cell.
=LOOKUP(2,1/(COUNTIF($E$4:E4,$B$5:$B$20)=0),$B$5:$B$20)
Formula Explanation

The structure of the formula is similar to that of the combination of the INDEX and  MATCH formula above, but LOOKUP handles array operations natively. The LOOKUP function takes three arguments exactly.

=LOOKUP(lookup_value, lookup_vector, [result_vector])

COUNTIF produces a count of each value in the expanding range $E$4:E4 from the range $B$5:$B$20. Then the count of each value is compared to zero and an array consisting of TRUE and FALSE values is generated.

Then the number 1 is divided by the array, resulting in an array of 1s and #DIV/0 errors. This array becomes the second argument or the lookup_vector for the LOOKUP function.

The lookup_value or the first argument of the LOOKUP function is 2 which is greater than any of the lookup vector’s values. The last non-error value in the lookup array will be matched by the LOOKUP.

LOOKUP returns the corresponding value in result_vector or the third argument for the function. In this case, the third argument or the  result_vector is $B$5:$B$20.

Note: This is a non-array way of getting unique values. So you do not have to press CTRL, SHIFT, and ENTER. Instead, you should just press only the ENTER key.

Use the LOOKUP and COUNTIF Formula to Get Unique Values From Range

Step 2:

  • Upon entering the formula, you will get the value Apple in cell E5. We will drag the fill handle downward to apply the formula to the rest of the cells.

Excel Get Unique Values from Range

  • After releasing the fill handle, we will get all the unique values in the Unique Products.

Unique Values in the Unique Products Column


5. Perform the LOOKUP and COUNTIF Formula to Get Unique Values that Appear Only Once

You can also use this same formula but modified a little bit to get the unique values each of which appears only once in the range. For example, we have modified our Excel worksheet so that we have the product Blueberry and Carrot appeared only once in our worksheet. We will now do the below steps to get these two unique values that appear only once in our worksheet.

Step 1:

  • First, select cell E5. Write down the below formula in the cell.
=LOOKUP(2,1/((COUNTIF($E$4:E4,$B$5:$B$20)=0)*(COUNTIF($B$5:$B$20,$B$5:$B$20)=1)),$B$5:$B$20)

Perform the LOOKUP and COUNTIF Formula to Get Unique Values that Appear Only Once

 Step 2:

  • Upon entering the formula, you will get the value Carrot in cell E5. We will drag the fill handle downward to apply the formula to the rest of the cells.

Drag the Fill Handle to Apply the Formula to the Rest of the Cells

  • After releasing the fill handle, we will get the 2 unique values that appear once only in cells E5 and E6 under the Unique Product The rest of the cells below them will show the #N/A value. We will clear the contents of these cells.

Perform the LOOKUP and COUNTIF Formula to Get Unique Values that Appear Only Once


6. Use the UNIQUE Function to Get Unique Values in the Range

Microsoft Excel 365 has a function called UNIQUE that returns a list of unique values in a specific range or column that the function takes as the argument. We will follow the below steps to get the unique values from our Country column using the UNIQUE function in Excel 365.

Step 1:

  • Select cell E5. Write down the below formula in the cell.
=UNIQUE(D5:D20)

Use the UNIQUE Function to Get Unique Values in the Range

 Step 2:

  • The above range D5:D20 indicates our Country So, we will get all the unique countries using the UNIQUE function. If we press ENTER, we will get all the unique countries in our Unique Country column.

Excel Get Unique Values from Range

Note: UNIQUE Function is an exclusive function currently available only for Excel 365. So, it will not work in your worksheet if you do not have Excel 365 on your PC.

Read More: How to Use Excel UNIQUE Function (20 Examples)


7. Run a VBA Macro Code in Excel to Get Unique Values in the Range

Another very easy but more effective way to get all the unique values in the range is to use VBA macro to find out those values. The VBA Macro will take a similar approach as the Advanced Filter in Method 1 took. Instead of applying the Advanced Filter ourselves, this time we will let the VBA Macro do it for us. We will now do the below steps.

Step 1:

  • We will select Visual Basic from the Developer We can also press ALT+F11 to open it.

Run a VBA Macro Code in Excel to Get Unique Values in the Range

  • Now, click on the Insert button and select Module.

Click on the Insert Button and Select Module

Step 2:

  • Write down the following code in the window that appears.
Option Explicit
 Sub GetUniqueValues()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
       ActiveSheet.Range("B5:B" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("E5"), _
    Unique:=True    
End Sub
  • Finally, click on the Run button to execute the code.

Excel Get Unique Values from Range

  • We will get all the unique products in the Unique Products

Unique Products in the Unique Products Column


8. Remove Duplicates in Excel to Get Unique Values in the Range

Of all these methods, the easiest way to get unique values from a range is to use the Remove Duplicates option in Excel. To get unique values in the range using Remove Duplicates, do the following.

Step 1:

  • First, we will select all the cells under the Country The range of the Country column is D5:D20. So, we will select the range and copy it.

Remove Duplicates in Excel to Get Unique Values

  • We will then paste it in the adjacent Unique Country. Remove Duplicates feature in Excel will remove all the duplicate values in the range. But we want our source data to be intact. So, we will make a copy of the range in the Unique Country column and perform the Remove Duplicates operation there.

Remove Duplicates in Excel to Get Unique Values in the Range

Step 2:

  • While the Unique Country column is selected, we will select the Remove Duplicates option from the Data tab.

Click on Remove Duplicates Button

  • A new window titled Remove Duplicates Warning will appear. We will select Continue With Current Selection. We will only want to perform this operation on the Unique Country column. So, we will not Expand the selection.
  • Then, we will click on the Remove Duplicates.

Click on the Remove Duplicates Button

  • Now, we will see our Unique Country column has only 4 distinct or unique countries in it.

Excel Get Unique Values from Range


Things to Remember

  • The INDEX and MATCH functions together are an array formula. So, you must press CTRL+SHIFT+ENTER together to insert the formula in a cell. It will put two curly braces around the whole formula.
  • While using the Remove Duplicates feature to get unique values from the range, we have selected only the Unique Country But you can add more columns or select all the columns by selecting the Expand the selection option. But if you expand the selection to add more columns, then the Remove Duplicates feature will not remove any value unless it finds two or more rows with identical data.

Conclusion

In this article, we have learned how to get the unique values from the range in Excel. I hope from now on you will find it very easy to get the unique values from a range in Excel. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo