{"id":244777,"date":"2024-01-23T02:05:41","date_gmt":"2024-01-23T02:05:41","guid":{"rendered":"https:\/\/vmlogger.com\/excel\/?p=244777"},"modified":"2024-01-30T13:24:57","modified_gmt":"2024-01-30T13:24:57","slug":"mastering-excels-unique-function-a-comprehensive-guide","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2024\/01\/mastering-excels-unique-function-a-comprehensive-guide\/","title":{"rendered":"Mastering Excel’s UNIQUE() Function: A Comprehensive Guide"},"content":{"rendered":"

UNIQUE() function in Excel is yet another very useful function with Dynamic array and spill range capabilities<\/a>. UNIQUE() function helps in handling data deduplication and extraction of distinct values efficiently. In this blog post, we’ll look into the details of the UNIQUE() function. We will learn how to use this function, its syntaxes and other tips and tricks related to this function by providing examples. We will also briefly look in to few real world scenarios where this function can be useful.<\/p>\n

\n

Note:<\/h4>\n

This is the 3rd article of Dynamic Arrays and Spill Functions in Excel<\/strong> series. In this series we are going to learn about FILTER()<\/a>, SORT()<\/a>, UNIQUE()<\/a> and SEQUENCE()<\/a> functions.\n<\/div>\n

What is the UNIQUE() Function:<\/h2>\n

The UNIQUE() function in Excel is designed to extract distinct values from a range, eliminating duplicates and presenting a clean list of unique items. Its distinctive feature lies in its ability to spill results into adjacent cells automatically when used in an array formula.<\/p>\n

Syntax<\/h3>\n

Let’s look in to its syntax and understand each of its input parameters.<\/p>\n

\r\n=UNIQUE(array, [by_col], [exactly_once])\r\n<\/pre>\n
\n
    \n
  1. array: <\/strong>The range or array from which unique values are to be extracted.<\/li>\n
  2. [by_col]: (Optional) <\/strong>A logical value that specifies whether to compare values by columns (TRUE) or rows (FALSE). Defaults to FALSE.<\/code><\/li>\n
  3. [exactly_once]: (Optional) <\/strong>A logical value that determines whether to include items that appear exactly once. Defaults to FALSE.<\/code><\/li>\n<\/ol>\n<\/div>\n
    \nThe UNIQUE() function, when used as an array formula, automatically spills the unique values into adjacent cells. This dynamic spill behaviour simplifies the process of handling large datasets and makes the function incredibly versatile.\n<\/div>\n
    How to use UNIQUE() function in Excel?<\/h2>\n
    Let’s jump in to the examples and understand how to use this function. We will learn its usage by different types of examples.<\/p>\n
    Example 1: Get unique values from a single Column Range<\/h3>\n
    Let’s suppose, you have list of fruits in Excel Column B, as shown in below picture.<\/p>\n
    \"Table
    Table : Fruits Orders<\/p><\/div>\n
    You want to extract all the unique fruits, there are there in column C<\/strong>. This is the simplest example to use UNIQUE() function in excel.<\/p>\n
    =UNIQUE(C3:C12)<\/code>
    \nAs we learned previosuly, UNIQUE() function has only one mandatory parameter and that is the range where you want to check all the unique values, I have provided the Cell range which has all the fruit names in the table. After the typing the above formula in a cell all the unique fruits are spilled over adjacent cells as shown in the below gif file. <\/p>\n
    \"Example
    Example 1 : UNIQUE() Function in Excel – Single Column<\/p><\/div>\n
    Example 2: Get unique values from a multiple Column Range<\/h3>\n
    Lets extend the previous example, and add one more column city. Now lets find out the unique values from multiple columns. In this case, unique() function will spill over unique combination of both the column values. In the below example, as you can see although, the values in each columns are still having duplicates. But the combination of the two columns are unique. For example, Mango-Mumbai combination has appeared 3 times in the table, but UNIQUE() function returns this combination only once. <\/p>\n
    =UNIQUE(G3:H12)<\/code>
    \n
    \"Example
    Example 2 : UNIQUE() function on multiple Columns<\/p><\/div><\/p>\n
    Example 3: Filtering Unique Values Based on Criteria<\/h3>\n
    Suppose you have a sales dataset with product names in column A and sales in column B. You want to extract unique products with sales greater than a certain threshold, let’s say 100 units. Use the following formula:<\/p>\n
    =UNIQUE(FILTER(A:B, B:B>100))<\/code>
    \nThis formula combines UNIQUE() with FILTER() to provide a dynamic list of unique products with sales exceeding 100 units. Refer the below gif.<\/p>\n
    \"Example
    Example 3: Filtering Unique Values Based on Criteria<\/p><\/div>\n
    Example 4: Extracting Unique Values Exactly Once<\/h3>\n
    Lets understand the use of its optional parameter Exactly_Once<\/code>. Lets take the same Sales data from the above example. Here if you want to identify only those products which are sold only once then in such case, you can use this flag and set it to TRUE<\/code><\/p>\n
    =UNIQUE(K3:L12;FALSE;TRUE)<\/code><\/p>\n
    \"Example
    Example 4: Extracting Unique Values Exactly Once<\/p><\/div>\n
    Example 5: Example 2: Extracting Unique Values by Column<\/h3>\n
    Imagine you have a dataset like shown in the below picture where all the countries and the year they won the world cup, are arranged column wise.
    \nIn such case, You can use UNIQUE() function to extract all the countries uniquely which have won world cup so far.<\/p>\n
    =UNIQUE(C30:N30;TRUE)<\/code><\/p>\n
    \"Example
    Example 5: Extracting Unique Values by Column<\/p><\/div>\n
    Your Playground: Excel Workbook<\/h2>\n
    Now in the below excel workbook, you can play around with the UNIQUE()<\/strong><\/code> formula by providing different types of parameters.<\/p>\n