Unique Values in Excel

The Unique function in Excel filters the list of unique values from the given data like range or array. The data presented is either number, text, date, time etc. This Excel Unique function comes under Dynamic Array Functions and splits the neighboring cells horizontally or vertically. The unique values are extracted in various data such as numeric values, names in a separate column or a combination of both rows and columns.

Syntax

UNIQUE (array, [by_col].

[exactly once])

Parameters

Array – The unique values are obtained from this range or collection.

By_col - A logical value is presented and used to compare data.

• True – It compares the data presented in columns
• False – It compares the data presented in rows.

Exactly_once – A logical value is presented where it defines what values are considered unique.

• True – It returns the value presented only once in the database notion of unique.
• False – It returns the different values from the given set of data

Unique Formula in Excel

STEP 1: Enter the data in the spreadsheet.

STEP 2: Select the range of cells to find the unique value. Click Data> Advanced Filter group from Sort & Filter group.

STEP 3: The advanced Filter dialogue box appears. In that, select "Copy to another location". A list range is chosen as A2:A10, and a cell is selected where the result will display. In the Copy to box, enter the cell chosen name.

STEP 4: Click the Unique Records Only. Press OK.

STEP 5: The result will be displayed in the above image, where the unique values are filtered.

In the above example, the duplicate values are removed from the cells C6, C9, and C10. This method is also used in Excel rows to remove duplicate values.

How to Filter the Unique Values in Excel?

There are several methods to filter the unique values in excel. This is one of the methods. The below steps are followed to filter the unique values in Excel.

STEP 1: Enter the data in the spreadsheet.

STEP 2: Click the Advanced Option from Sort and Filter Group in the data Tab.

STEP 3: A dialog box appears in that choose "Filter the list in place" and select the listed range. Click the Unique Record Only. Press OK.

STEP 4: The result will be displayed in the image below.

The above example shows that cells A6, A9, and A10 are hidden, and the unique value is displayed while the duplicate values are hidden. Click the clear option in the Sort and Filter Group to bring the data back to its original position. The data returns to its original position.

How to remove duplicate values in Excel?

The duplicate values in Excel are removed by the following Method. The duplicate values are drawn either in a single column or a combination of rows and columns. Here Remove Duplicate Tool is used to find the special deals and delete the duplicate values.

STEP 1: Enter the data in the spreadsheet.

STEP 2: Choose the Remove Duplicates from the data Tools in the data Tab. After selecting the option, the Remove Duplicate option removes the identical data present in the spreadsheet.

STEP 3: A Remove duplicate dialog box appears where that selects the name of the column to remove the duplicate values

STEP 4: From the above image, the duplicate value is removed from cell A6, and the original values are arranged in an order.

Summary

The above tutorial discusses the functions and features of unique value. Various methods exist for finding the unique value and removing the duplicate value.