UNIQUE Function in Excel

The UNIQUE function is one of the Lookup & reference functions of Excel.

It looks up a value either from a one-row or one-column range or from an array. Provided for backward compatibility.

We can find this function in Lookup & reference of insert function Tab.

How to use UNIQUE function in excel

1. Click on empty cell (like F5).

2. Click on fx icon(or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select Lookup & reference category.

5. Select UNIQUE function.

6. Then select ok.

7. In function arguments Tab, you will see UNIQUE function.

8. Lookup value is a value that UNIQUE searches for in Lookup vector and can be a number, text, logical value, or a name or reference to a value.

9. Lookup vector is a range that contains only one row or one column of text, numbers, or logical values, placed in ascending order.

10. Result vector is a range that contains only one row or column, the same size as the lookup vector.

11. Lookup value is a value that LOOKUP searches for in Array and can be a number, text, a logical value, or a name or reference to a value.

12. Array is a range of cells that contain text, number, or logical values that you want to compare with Lookup value.

13. You will see the result in formula result section.

Examples of UNIQUE function in excel

The UNIQUE function in Excel returns a list of unique values from a range or array of cells.

This means that it will remove any duplicate values and only show each unique value once.

Here are some examples of using the UNIQUE function in Excel:

1. Basic Example: Assume you have a list of fruits in column A, and you want to get a list of unique fruits.
   • You can use the UNIQUE function as follows: =UNIQUE(A:A) This will return a list of all unique fruits in column A.
2. Using with Filters: You can also use the UNIQUE function with filters to get a unique list based on certain criteria.
   • For example, if you have a list of sales data in columns A and B, and you want to get a list of unique products that have sold more than 100 units, you can use the following formula: =UNIQUE(FILTER(A:B, B:B>100)) This will return a list of unique products that have sold more than 100 units.
3. Using with Spill Range: Since the UNIQUE function is a spill range function, you can simply type the formula into a cell and it will spill out the results automatically.
   • For example, if you have a list of cities in column A and you want to get a unique list of cities, you can use the following formula in cell C1: =UNIQUE(A:A) This will spill out a list of all unique cities in column A starting from cell C1.

These are just a few examples of how the UNIQUE function can be used in Excel.

It’s a powerful tool for quickly getting a list of unique values from a range or array of cells.

How does the UNIQUE function work in Excel?

The UNIQUE function in Excel is used to extract unique values from a list or a range of cells.

It returns a new array that contains only the unique items from the selected data set.

The syntax for the UNIQUE function is:

=UNIQUE(array, [by_col], [exactly_once])

where:

• array: the range of cells or array to be checked for unique values.
• by_col (optional): a boolean value indicating whether to extract unique values by column (TRUE) or row (FALSE). The default value is FALSE.
• exactly_once (optional): a boolean value indicating whether to extract values that appear exactly once in the selected data set. The default value is FALSE.

Here are some examples of how the UNIQUE function works:

Example 1: Extracting unique values from a single column Suppose we have a list of fruits in column A, and we want to extract the unique values from this list. We can use the following formula:

=UNIQUE(A1:A10)

This will return an array containing the unique values from column A.

Example 2: Extracting unique values from multiple columns Suppose we have a table containing sales data for various products across different regions.

We want to extract the unique combinations of products and regions.

We can select the range of cells containing the data and use the following formula:

=UNIQUE(A1:C10, TRUE)

This will return an array containing the unique combinations of products and regions.

Example 3: Extracting values that appear exactly once Suppose we have a list of numbers in column A, and we want to extract the values that appear exactly once in the list. We can use the following formula:

=UNIQUE(A1:A10, FALSE, TRUE)

This will return an array containing only the values that appear exactly once in column A.

In summary, the UNIQUE function in Excel is a useful tool for extracting unique values from a list or range of cells, and it can be used in a variety of ways depending on the data being analyzed.

What are the arguments of the UNIQUE function in Excel?

The UNIQUE function in Excel has three optional arguments:

1. Array: This is the range of cells or array from which you want to extract unique values.
   • This is a required argument.
2. By_col: This is an optional boolean (TRUE/FALSE) argument that specifies how the function should treat the input data.
   • If set to TRUE, it treats each column in the range as a separate category and returns unique values for each column.
   • If set to FALSE or omitted, the function treats all data as one category and returns only the unique values across all columns.
3. Exactly_once: This is an optional boolean (TRUE/FALSE) argument that specifies whether the function should return only those values that appear exactly once in the input data.
   • If set to TRUE, the function returns only those values that appear once.
   • If set to FALSE or omitted, the function returns all unique values regardless of their frequency.

Let’s look at some examples to understand how these arguments work:

Example 1:

Suppose you have a table of sales data with two columns – Region and Product. You want to extract the unique products sold in each region.

You can use the UNIQUE function with the following formula:

=UNIQUE(A2:B10, TRUE)

Here, A2:B10 is the range containing the sales data, and TRUE indicates that you want to extract unique values by column.

The resulting output will contain two columns, one for each region, with the unique products sold in each region.

Example 2:

Suppose you have a list of fruits where some fruits are repeated. You want to extract only those fruits that appear exactly once.

You can use the UNIQUE function with the following formula:

=UNIQUE(A2:A10,,TRUE)

Here, A2:A10 is the range containing the fruit names, and TRUE indicates that you want to extract unique values by row.

The blank space between the commas indicates that you want to treat all data as one category. The resulting output will contain only those fruits that appear exactly once in the input data.

Example 3:

Suppose you have a list of numbers where some numbers are repeated.

You want to extract all unique values, without regard for how frequently they appear.

You can use the UNIQUE function with the following formula:

=UNIQUE(A2:A10)

Here, A2:A10 is the range containing the numbers. Since both optional arguments are omitted, the function treats all data as one category and returns all unique values regardless of their frequency.

In summary, the three arguments of the UNIQUE function in Excel allow you to customize how the function extracts unique values from an input range or array of cells.

Can I use the UNIQUE function to remove duplicates in Excel?

Yes, you can use the UNIQUE function in Excel to remove duplicates from a range of cells or an array.

The UNIQUE function returns only the unique values from the input range or array, thereby removing any duplicates.

Here’s how you can use the UNIQUE function to remove duplicates in Excel:

1. Select the range of cells from which you want to remove duplicates.
2. Type the following formula in the formula bar: =UNIQUE(range) Here, “range” represents the cell range that you want to remove duplicates from.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula.

The UNIQUE function will return an array of unique values from the selected range, effectively removing any duplicate values.

Let’s look at an example to understand this better:

Suppose you have a list of employee names with some duplicates. You want to remove the duplicates and extract only the unique names.

Here are the steps to do this using the UNIQUE function:

1. Select the range of cells containing the employee names.
2. Type the following formula in the formula bar: =UNIQUE(A1:A10) Here, A1:A10 represents the range containing the employee names.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula.

The UNIQUE function will return an array of unique employee names, excluding any duplicates in the original list.

It is important to note that the UNIQUE function does not remove duplicates directly from the original data set – it returns a new array of unique values.

If you want to modify the original data set and remove duplicates permanently, you can copy the resulting array of unique values and paste them back into the original range of cells.

In summary, yes, you can use the UNIQUE function in Excel to remove duplicates from a range of cells or an array, although it does not modify the original data set directly.

Can I use the UNIQUE function to remove duplicates in Excel?

Yes, you can use the UNIQUE function in Excel to remove duplicates from a range of cells or an array.

The UNIQUE function returns only the unique values from the input range or array, thereby removing any duplicates.

Here’s how you can use the UNIQUE function to remove duplicates in Excel:

1. Select the range of cells from which you want to remove duplicates.
2. Type the following formula in the formula bar: =UNIQUE(range) Here, “range” represents the cell range that you want to remove duplicates from.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula.

The UNIQUE function will return an array of unique values from the selected range, effectively removing any duplicate values.

Let’s look at an example to understand this better:

Suppose you have a list of employee names with some duplicates. You want to remove the duplicates and extract only the unique names.

Here are the steps to do this using the UNIQUE function:

1. Select the range of cells containing the employee names.
2. Type the following formula in the formula bar: =UNIQUE(A1:A10) Here, A1:A10 represents the range containing the employee names.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula.

The UNIQUE function will return an array of unique employee names, excluding any duplicates in the original list.

It is important to note that the UNIQUE function does not remove duplicates directly from the original data set – it returns a new array of unique values.

If you want to modify the original data set and remove duplicates permanently, you can copy the resulting array of unique values and paste them back into the original range of cells.

In summary, yes, you can use the UNIQUE function in Excel to remove duplicates from a range of cells or an array, although it does not modify the original data set directly.

How do I use the UNIQUE function to extract unique values in Excel?

You can use the UNIQUE function in Excel to extract unique values from a range of cells or an array.

The UNIQUE function returns only the unique values from the input range or array, which can be useful for data analysis and organization.

Here’s how you can use the UNIQUE function to extract unique values in Excel:

1. Select the range of cells or array from which you want to extract unique values.
2. Type the following formula in the formula bar: =UNIQUE(range) Here, “range” represents the cell range or array that you want to extract unique values from.
3. Press Enter.

The UNIQUE function will return an array of unique values from the selected range.

Let’s look at an example to understand this better:

Suppose you have a list of fruit names with some duplicates. You want to extract only the unique fruit names.

Here are the steps to do this using the UNIQUE function:

1. Select the range of cells containing the fruit names.
2. Type the following formula in the formula bar: =UNIQUE(A1:A10) Here, A1:A10 represents the range containing the fruit names.
3. Press Enter.

The UNIQUE function will return an array of unique fruit names, excluding any duplicates in the original list.

You can also use the optional arguments of the UNIQUE function to customize how it extracts unique values.

For example, you can use the by_col argument to extract unique values by column instead of by row. You can also use the exactly_once argument to extract only those values that appear exactly once in the input data.

In summary, the UNIQUE function in Excel is a handy tool for extracting unique values from a range of cells or an array. It can help you organize your data and perform more accurate analyses.

Does the UNIQUE function preserve the order of values in Excel?

Yes, the UNIQUE function in Excel preserves the order of values in the input range or array.

The function returns only the unique values from the input data set in the order in which they appear.

For example, suppose you have a list of numbers in column A, as follows:

A

2

4

1

3

2

1

4

If you use the UNIQUE function with the formula =UNIQUE(A1:A7), it will return an array of unique numbers in the order in which they appear:

2

4

1

3

As you can see, the UNIQUE function preserves the order of values in the input range and returns only the unique values.

However, if you use the SORT function to sort the resulting array, the order of values may change.

For example, if you use the formula =SORT(UNIQUE(A1:A7)), it will return a sorted array of unique numbers:

1

2

3

4

In summary, the UNIQUE function in Excel preserves the order of values in the input range or array, but the order may change if you use other functions in combination, such as SORT.

What happens if there are blank cells in the data range used in the UNIQUE function in Excel?

When using the UNIQUE function in Excel, blank cells in the data range are treated as distinct values and may be included in the resulting array of unique values.

However, you can customize how the function treats blank cells by using the IF function in conjunction with the UNIQUE function.

Here are some examples to illustrate what happens when there are blank cells in the data range used in the UNIQUE function:

Example 1: Suppose you have a list of city names in column A, with some blank cells.

If you use the formula =UNIQUE(A1:A10), the resulting array of unique values may include a blank cell if it exists in the input data set. For example, if the input data set is:

New York Los Angeles

Chicago

Houston Los Angeles

the resulting array of unique city names will be: New York Los Angeles Chicago

Houston

Note that the blank cell is treated as a distinct value and is included in the output.

Example 2: Suppose you have a list of numbers in column A, with some blank cells.

If you want to exclude blank cells from the output, you can use the IF function in conjunction with the UNIQUE function.

The formula would be:

=UNIQUE(IF(A1:A10<>””,A1:A10))

This formula checks whether each cell in the input range is blank. If it is not blank, it includes the cell in the output of the UNIQUE function.

If it is blank, it excludes the cell from the output. This ensures that the resulting array of unique values does not include any blank cells.

In summary, when using the UNIQUE function in Excel, blank cells in the data range are treated as distinct values and may be included in the resulting array of unique values. However, you can customize how the function treats blank cells by using the IF function in conjunction with the UNIQUE function.

Can I sort the results of the UNIQUE function in Excel?

Yes, you can sort the results of the UNIQUE function in Excel using the SORT function.

The SORT function sorts the values in an array or range in ascending or descending order, and can be used to sort the unique values returned by the UNIQUE function.

Here’s how you can sort the results of the UNIQUE function in Excel:

1. Use the UNIQUE function to extract the unique values from a range of cells or array.
2. Highlight the resulting array of unique values.
3. In a new cell, type the following formula: =SORT(array) Here, “array” refers to the selected range containing the unique values.
4. Press Enter.
5. The SORT function will return a sorted array of unique values in either ascending or descending order.

For example, let’s say you have a list of numbers with some duplicates, as follows:

A

1

2

3

2

4

5

5

To extract the unique values from this list, you could use the formula =UNIQUE(A1:A7).

This would return an array of unique values: 1, 2, 3, 4, 5.

If you want to sort this array in ascending order, you can select the array and enter the formula =SORT(A1:A5) in a new cell. This would return a sorted array of unique values: 1, 2, 3, 4, 5.

If you want to sort the array in descending order, you can add a second argument to the SORT function, indicating that you want to sort in descending order.

For example, the formula =SORT(A1:A5, -1) would return a sorted array of unique values in descending order: 5, 4, 3, 2, 1.

In summary, you can use the SORT function to sort the results of the UNIQUE function in Excel, either in ascending or descending order.

This can be useful for organizing and analyzing data sets with unique values.

Can I use the UNIQUE function to extract unique values from multiple columns in Excel?

Yes, you can use the UNIQUE function to extract unique values from multiple columns in Excel.

The UNIQUE function can be applied to a range of cells that includes multiple columns, and it will return an array of unique values for each column.

Here’s how you can use the UNIQUE function to extract unique values from multiple columns in Excel:

1. Select the range of cells that includes the multiple columns from which you want to extract unique values.
2. In a new cell, type the following formula: =UNIQUE(range, TRUE) Here, “range” refers to the selected range containing the multiple columns, and the optional argument “TRUE” tells the function to treat each column as a separate category.
3. Press Enter.

The UNIQUE function will return an array of columns, with each column representing the unique values from the corresponding column in the input range.

For example, let’s say you have a table of sales data with two columns – Region and Product. You want to extract the unique products sold in each region.

Here are the steps to do this using the UNIQUE function:

1. Select the range of cells containing the sales data, including both the Region and Product columns.
2. In a new cell, type the following formula: =UNIQUE(A2:B10, TRUE) Here, A2:B10 represents the range containing the sales data, and the optional argument “TRUE” tells the function to treat each column as a separate category.
3. Press Enter.

The UNIQUE function will return an array with two columns, one for each region, with the unique products sold in each region.

Note that when using the UNIQUE function to extract unique values from multiple columns, the resulting array may contain blank cells if there are no unique values in certain columns.

To remove these blank cells, you can wrap the UNIQUE function in the IF function, as follows:

=IF(UNIQUE(range, TRUE)=””,””, UNIQUE(range, TRUE))

This formula checks whether the output of the UNIQUE function is blank. If it is, it returns a blank cell.

If not, it returns the unique values from the input range.

In summary, you can use the UNIQUE function to extract unique values from multiple columns in Excel by selecting the range and including the optional argument “TRUE” to treat each column as a separate category.

Are there any limitations or compatibility issues with the UNIQUE function in Excel?

The UNIQUE function in Excel is a relatively new feature and was introduced as part of the Dynamic Arrays feature set, which was added beginning with the version of Excel 365 for Windows released in 2020.

As such, there may be some limitations or compatibility issues to keep in mind when using the UNIQUE function in Excel.

Here are some limitations and compatibility issues to be aware of:

1. Compatibility: The UNIQUE function is not available in versions of Excel prior to Excel 365 for Windows (released in 2020) or Excel for Mac 2019 (version 16.20).
   • If you are using an earlier version of Excel, you will not be able to use the UNIQUE function.
2. Formula Spill: In order to use the UNIQUE function effectively, you must have the formula spill enabled.
   • This feature allows formulas to automatically expand into neighboring cells as needed.
   • If you have formula spill disabled, the UNIQUE function will return an error.
3. Non-Contiguous Ranges: The UNIQUE function does not support non-contiguous ranges.
   • If you need to extract unique values from multiple non-contiguous ranges, you may need to use a combination of the INDEX, MATCH, and CONCATENATE functions instead.
4. Array Size Limitations: The size of the output array returned by the UNIQUE function is limited to 2^20 rows or columns.
   • If the output array exceeds this limit, you may need to use other methods to extract unique values from your data set.
5. Performance: Depending on the size of your input range, the UNIQUE function can be slow to calculate.
   • You may experience decreased performance or slower calculation times when working with large data sets.

In summary, while the UNIQUE function in Excel is a powerful tool for extracting unique values from data sets, it does have some limitations and compatibility issues to keep in mind.

If you encounter any issues with the function, you may need to use alternative methods to extract unique values from your data set.

Related posts:

SORT Function in Excel SORTBY Function in Excel Group Duplicates in Excel How to fix the #SPILL! error