Guide to using the UNIQUE function in Excel to filter unique values in a range

Do you want to filter out unique values from a specific list? You can use Advanced Filter or Remove Duplicate, but both cannot automatically update the data when there are changes in the original list. Don’t worry, now the birth of the UNIQUE function will help you accomplish this easily and quickly. Let’s find out in detail how to use this function with FPT Shop!

What is the UNIQUE function in Excel?

The UNIQUE function is a data filter function in Excel’s Office 365 version. This function helps you remove duplicate values, find and keep unique values in a row, column, or a specific data range on a worksheet. The values to be filtered can be in the form of text, numbers, or dates, times,…

With the UNIQUE function syntax, you no longer have to manually filter, saving you a lot of time and effort. Excel operations also become more professional. In addition, the UNIQUE function can be combined with some other functions in Excel to efficiently support your work.

The formula of the UNIQUE function in Excel

The UNIQUE function has the following formula: =UNIQUE(array, [by_col], [occurs_once]).

The formula has 3 arguments. Among them, the first argument (array) is required, and the remaining 2 arguments are optional.

• array: A column, a row, or a data range that needs to filter out unique values.
• [by_col]: A logical value that determines how the matching is done. The default is FALSE (sort by row) or TRUE (sort by column).
• [occurs_once]: A logical value that allows you to find truly unique values. The default is FALSE to filter out representatives of each value once or TRUE to filter out a value that appears only once in the array.

Instructions on how to use the UNIQUE function in Excel

To help you efficiently use the UNIQUE function in Excel, each usage method below will have specific illustrated examples.

Used to find unique values in a table

Example: Find unique values in the following worksheet:

Step 1: Enter the formula =UNIQUE(A2:A8) into the cell where you want to display the result (in the example, it’s cell B2).

Step 2: Then, press the Enter key, and the result will be displayed.

Combined with the SORT function

Example: Create a list of unique values in ascending order:

Step 1: Enter the formula =SORT(UNIQUE(A2:A9)) into the cell where you want to display the result (cell B2).

Step 2: Then, press the Enter key to get the desired result.

Filter duplicates by column in a data table

Example: Filter out the list of items that each employee has sold in the following data table:

Step 1: Enter the formula =UNIQUE(B3:E3;TRUE;FALSE) into the cell where you want to display the result (cell B8).

Step 2: Then, press the Enter key on the keyboard.

Step 3: From the cell with the formula (B8), you can drag the mouse down the remaining rows in the table to display the corresponding results.

Filter out values that appear only once in the list

Example: Find the unique fabric colors used in the following statistics table:

Step 1: Enter the formula =UNIQUE(B2:B7;;TRUE) into the cell where you want to display the result (C2).

Step 2: Press the Enter key on the keyboard to get the filtered result.

Notes when using the UNIQUE function in Excel

• The UNIQUE function currently only applies to Microsoft Office 365, so other versions of Excel will not have this function.
• The UNIQUE function returns an array, and Excel will automatically create a suitable range of cells to support displaying the result.
• You can use the UNIQUE function between workbooks, but it only supports when all those workbooks are open. If you close the source workbook, the resulting link will return a #REF! error when refreshed.

Conclusion

So, the article has helped you understand more about the UNIQUE function and how to use this type of function in Excel. Hopefully, through that, you will know more interesting tricks to help you efficiently exploit and use Excel in your work.