Home
How to use Unique Formula in Excel with 3 Example

How to use Unique Formula in Excel with 3 Example

Excel Formulas June 30, 2022

Welcome to Excel Avon

Unique Formula in Excel

To get unique values from a range or a list use the Excel UNIQUE function. The UNIQUE function supports text, numbers, dates, times, etc.                       types of values. In this article, I will show you various examples of using the Excel UNIQUE function. This is a very simple function, you can                   extract distinct values both unique and unique and it also helps to compare column by column or rows of rows.

Formula

=UNIQUE (array, [by_col], [exactly_once])

Arguments

 

array – [Required) It is a cell range or array from which to extract unique values.
by_col – [Optional] It is a Boolean value for how to compare and extract unique values. Here,FALSE=row;TRUE = default (false).
exactly_once – [Optioanl] It is also a Boolean value. Here, TRUE (occurred once); FALSE  (all unique values).

   Example Of Unique Formula

DOWNLOAD USED EXCEL FILE FROM HERE>>

  Unique By Row 

To do the unique by row formula, we have created an excel sheet, in which we have written some names, in the names written in the sheet,                      some names have come many times and we will know each name only once.

UNIQUE-FORMULA-IN-EXCEL-

Whichever name appears multiple times in the given image, we will find it once. So Now I apply the UNIQUE formula-

=UNIQUE(A2:A31)

unique-formula-in-excel

After applying the formula press enter.                                                                                                                                                                                                               As we can see after applying the formula, the name that appears more than once is shown only once.

UNIQUE-FORMULA-IN-EXCEL

  Unique By Columns 

For the unique by column, we have taken the data in the column and suppose a name must have appeared many times in that column,                              then the formula can show it single time by doing it, this formula is very useful and easy.

unique-formula-by-column

Now I apply the UNIQUE Formula  And you will get single entry of all the names and the names which have come many times will also come once. 

=UNIQUE(B1:AE1,TRUE) Here 'TRUE' is use for Column, which is Optional.

Unique-byColumn-Formula-in-excel

After applying the formula press enter.                                                                                                                                                                                                               As we can see after applying the formula, the name that appears more than once is shown only once.

  Unique-By-Columns

Extract only Once

Extract by once is used when a name appears multiple times in the data sheet but we want to find the name that appears only once.

UNIQUE-formual-ny-unique-exatrctTable1

Now I apply the UNIQUE Formula  And you will get all those who have come to the whole table only once

=UNIQUE(Table1[Names],,TRUE) Here, TRUE (occurred once) and it is Optional.

FOrmula-in-unique-by-extract

After applying the formula press enter.                                                                                                                                                                                                               As we can see after applying the formula, you will get all those who have come to the whole table only once.

Extract-only-Once-time-in-excel

DOWNLOAD USED EXCEL FILE FROM HERE>>

So I hope you have understood this formula and for more information you can follow us on Twitter, Instagram, LinkedIn and YouTube as well.

You can also see well explained video here

You can learn more formulas from here
1. Vlookup formula

2. Index & Match

 

Related Posts

Leave a Reply