As you know Excel has many functions for different tasks to be performed. The UNIQUE function is used for a cell range that in return gives a unique items list in that particular range. Moreover, you can use this function to specify the duplicate values in the list and the unique values in the column. Also, bear in mind that the UNIQUE function is case-sensitive, that’s why it gives different outcomes when the text value is added differently.
Today, you will find how to use UNIQUE function in Excel simply by using different scenarios.
Excel UNIQUE Function:
With the help of this function, Excel lets you have a list of unique values extracted from a range. You can use it with all kinds of data, such as text, numbers, dates, times, etc. The UNIQUE function is actually native to Dynamic Array functions that’s why the outcome you get will be a dynamic array that simply diverts into the adjacent cells by default. This diversion could be horizontal or vertical.
Below is the UNIQUE function Syntax:
UNIQUE(array, [by_col], [exactly_once])
Let’s have some explanation of each argument used in this formula:
Array: This argument is required and is used for the array from which unique values are returned.
By_col: This argument is optional in which you get a value that shows how to compare data:
- TRUE: it helps in data comparison over columns.
- FALSE: it helps in data comparison over rows.
Exactly_once: This argument is also optional in which you get a value that explains unique values in terms of:
- TRUE: it gives values that appear only once.
- FALSE: it gives values that are different in range.
How to Use UNIQUE Function in Excel for Text Values?
With the UNIQUE function, you can find out unique values from a text string. Let’s assume that you need to find the fruit name from the Product Name column present in your dataset. For this, have a look at the following steps:
- Put the following formula in cell D4 to find the UNIQUE values.
=UNIQUE(B4:B12)
- Choose a cell range. Here the cell range B4:B12 is selected.
- Press ENTER key and you will get a UNIQUE list of values extracted from the range.
How to Use Unique Function in Excel for Numeric Values?
When your dataset contains numbers, the UNIQUE function is also helpful in finding unique values. Let’s assume that you need to find the unique IDs from the Order ID column. For this, below are the steps to follow:
Enter the following formula in cell D4.
=UNIQUE(C4:C12)
The cell range C4:C12 is selected in the UNIQUE function. Now, press ENTER key and you will see a UNIQUE function gives a list of unique values.
Basic UNIQUE Formula in Excel
Let’s have a look at the simplest form of the UNIQUE formula. You can use it to find a list of unique values from a range B2:B10. For this, you need to enter the formula in D2:
=UNIQUE(B2:B10)
While pressing the ENTER key, you will see the result as the first appeared name in D2 that leaks other names as well. Ultimately, you will get all the unique values present in a column:
When you are comparing data over the columns from B2 to I2, you need to let the 2nd argument set to TRUE:
=UNIQUE(B2:I2, TRUE)
Now, enter the above-given formula in B4 and the outcome will appear in the cells. Below is the presentation of the unique values in a row:
Excel UNIQUE Function Tips and Tricks?
Like other functions in Excel, UNIQUE has also some limitations that you must be aware of:
- The formula should be typed in one cell only or else you will encounter the #SPILL error.
- Whenever you make changes in the source data, the outcome updates by default. Also, new values are not added in the formulas that are included outside of the reference array.
- Remember that you need to open both workbooks when dynamic arrays are required to be functional. You may encounter a #REF error when the source workbook is not opened.
- A unique function is useful only in a normal range just like other dynamic arrays. If it is added with tables, you may encounter a #SPILL error.
Summary:
You are now familiar with how to use UNIQUE function in Excel while having text values as well as numeric values. This function is super handy when you need to extract specific entries from a huge dataset.
