×

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.

Excel Unique Values

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

Excel Unique Values

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.

Excel Unique Values

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.

Excel Unique Values

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.

Excel Unique Values

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

Excel Unique Values

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.

Excel Unique Values

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.

Excel Unique Values

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

Excel Unique 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.


Related Topics

How to make use of the Wildcard in Excel

In this modern world, it is known that the wildcard characters in Microsoft Excel are considered to be the most essential and unrated features of Microsoft Excel. Besides all these,...

8 minutes read.

Excel VALUE() Function

The VALUE() function in excel converts the specific text representing a number (i.e., a number, date, or time format) into a numerical value. Syntax VALUE(Text) Parameter Text(required)- This parameter represents the string that you want...

1 minute read.

Excel MID() Function

The MID() function in excel is used to return a specific number of characters from a text string starting at the position you specify. One can change the start of the string, thus...

1 minute read.

Square Root Function in Excel

The concept of Mathematical calculations includes a large number of numbers and calculations are based on the numbers present in the data. Likewise, Square Root is a function used to...

3 minutes read.

Count Cells with Text in Excel

Excel spreadsheets are widely used in many fields to store and analyze data. Usually, the cells present in excel are a combination of numbers and text. To perform the calculation...

3 minutes read.

Row Difference in Excel

Microsoft Excel is used for multiple purposes in various organizations. It is a combination of numeric values, alphabets etc. To calculate data, the user organizes the data according to their...

5 minutes read.

Averageif Function in Excel

Average If in HTML In Mathematics, Average function is used to find the arithmetic mean of the given data. It is defined as the dividing the sum total of given data...

7 minutes read.

Excel LEFT() Function

The LEFT () function in excel returns the leftmost characters from a text value. This function is used for data manipulation. Syntax LEFT(Text, [num_chars]) Parameter Text (required)- This parameter represents the string from which you want to...

1 minute read.

Excel File using Password

How to Protect an Excel File using Password? Nowadays Microsoft Excel is widely used by large organizations to small organizations for their personal and professional usage. They share files to various...

2 minutes read.

Purpose of Randomize in Excel

Excel spreadsheets are used on a large scale for business and personal usage. It is used to store data, perform multiple calculations, make assumptions based on charts and graphs etc....

7 minutes read.

How to use Index and Match in Excel?

The Index and Match function is a familiar and frequently used function in Excel. It is one of the most powerful functions and is easy to use. By default, Excel...

6 minutes read.

Nearest Multiple in Excel

In Excel, the values in Multiplication can be round up, down and to nearest Multiplication number using Excel default function. This makes the calculation to be performed in an easier...

3 minutes read.

Excel MAX() Function

Excel MAX() Function The MAX() function in excel returns the largest or maximum number from a range of cells. It ignores logical values and text. Syntax MAX(Number1,Number2, ...) Parameter Number1(required)- This parameter represents the string for which you...

1 minute read.

Division in Excel

Excel worksheet are widely used for calculation purposes. Mathematics calculations include addition, subtraction, multiplication, division etc. Performing this calculation manually takes time and sometimes leads to error. To get the...

5 minutes read.

Text to Columns

Text to Columns This Excel feature is used to split the cell content name of one cell into multiple columns based on a delimiter, such as a space or a special character or based...

2 minutes read.

How to print titles in Excel

In Microsoft Excel, if a particular task is completed in the worksheet, the user wants to print the worksheet. While performing this job, the row and column headings will display...

3 minutes read.

Shortcut Keys in Excel

Shortcut Keys in Excel Shortcut keys in Excel are used to simplify the work and save time by pressing a few keys.  These keys are used for navigating and working with Excel spreadsheets quickly and easily. Basic Selection:...

9 minutes read.

Excel COUNT() Function

Excel COUNT() Function The COUNT() function counts the number of cells in a range that contain numbers. Syntax COUNT([value1], [value [2], ...) Parameter value1(required)- This parameter represents the first cell in the range. value2, …(optional)- It represents the...

1 minute read.

Formats in Excel

What are the formats? Formats are different options that one uses to change the appearance of data (maybe text, number, etc..) in an excel file. They do not change the value of the...

3 minutes read.

Notes in Excel

Notes in Excel Notes are used to include additional information to the cell and explain formulas calculation, any modification made to the cell, providing necessary information on data that is entered or to provide any...

3 minutes read.