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 information about all the employees of a large company. This tutorial will show you multiple ways to get unique values from range in Excel.
How to Get Unique Values from Range in Excel (8 Easy Ways)
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 from Column. The image below shows the Excel worksheet we are going to work with.
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 in a column or range. Do the following:
Step 1:
- First, go to the Data. Select Advanced from the Sort & Filter section.
- 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.
- 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).
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))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) Â
The 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 provides the positions as row numbers, and INDEX returns the name at these positions.
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.
- After releasing the fill handle, we will get all the unique values in the Unique Products.
3. Apply the INDEX and MATCH Formula to Get Unique Values with Empty Cells
Sometimes the range we want 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.
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))
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.
- After releasing the fill handle, we will get all the unique values in the Unique Products.
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)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.
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.
- After releasing the fill handle, we will get all the unique values in the Unique Products.
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) 
 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.
- 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.
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) 
 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.
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. 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.
- Now, 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.
- We will get all the 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.
- 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.
Step 2:
- While the Unique Country column is selected, we will select the Remove Duplicates option from the Data tab.
- 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.
- Now, we will see our Unique Country column has only 4 distinct or unique countries in it.
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.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
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 leave a comment below. Have a great day!!!
<< Go Back to Unique Values | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
