Unique function in Excel 365

Introduction

In this article, we will explore creative, efficient techniques that allow us to rapidly locate unique values within various sheets or worksheets. Excel's 365 advanced built-in functions integrated into multiple workbook references ensure flawless consistency as formulas perform checks against each dataset independently before returning results accurately. Unique function is one of the predominant functions and works well with dynamic arrays to remove duplicate items from the specified range of the table. It performs efficiently with other functions like SORT, CHOOSE, FILTER so on.

Unique function in Excel 365

Step 1

Open a new worksheet in Excel 365 and consider the following dataset, comprising four columns named Series, Vegetable, Cost, and Sale, and enter the data in these columns as shown below image −

Step 2

Now, we need to fetch the nonduplicate items from the given range B5:B13. To achieve this, enter the formula =UNIQUE(B5:B13) in the G5 cell as shown below −

Step 3

Press the Enter tab to populate the unique vegetable list in a specified range.

Step 4

Now, sort the resultant array through the SORT function which is effectively used with the Unique function. Locate the pointer in the G4 cell and write the formula =SORT(UNIQUE(B5:B13)) as highlighted in below image −

Step 5

The sorted list of distinct Vegetables is displayed in the resultant array −

Step 6

Suppose users wish to fetch only those vegetables that occur more than two times. In this case, use this formula =UNIQUE(FILTER(B5:B13,COUNTIF(B5:B13,B5:B13)>2)) as shown in the below image.

Step 7

Press the “Enter” key to obtain the resultant value. Tomato is the only vegetable whose occurrence is more than twice in the specified range B5:B13.

The following error may occur while running the Unique function.

• The “#Spill!” error would appear if the data is already in the resultant column or resultant row. Users should choose the designated empty column or row range to remove this error.

• If users try to run the Unique function in an older Excel version other than Excel 365 then #Name? error may occur.

Conclusion

It would be concluded that the users may enhance their Excel skills through this simple method which is depicted in this article. The Unique function works efficiently with any data type and fetches the unique value from the defined dataset in the worksheet. Users need to specify the valid array range to obtain accurate results.