EXCEL FUNCTIONS -UNIQUE
UNIQUE is another new function which is still available for only OFFICE 365, Excel for Office 365 for Mac Excel for the web Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones.
So its quite possible that you get confused and keep searching this function in your desktop version of Excel even if its 2019.
So as the name of the function appears, UNIQUE function extracts the unique values and discard the repeated values simply.
Sometimes when we are handling large reports, we come across such situations where we have large data and lot of repeated data is present. It is a cumbersome task to remove repeated data.
So this function is very easy to use and does the job perfectly. Let us see how to use this function.
PURPOSE OF UNIQUE IN EXCEL
UNIQUE FUNCTION returns a list of UNIQUE VARIABLES from the given list or range. It filters out the repeated data and keeps the unique data.
SYNTAX: UNIQUE FUNCTION
=UNIQUE(ARRAY to be operated , by rows/columns , Exactly once /All)
The UNIQUE function has the following arguments:
ARRAY to be operated It is the array which needs to be searched for unique values.
by rows/columns [optional, default-false] if true- Search for repeated rows if false-search for repeated columns
Exactly once/All [optional, default-false] if True, will result only those entry which occur only once. If false, will result those entries also which occur more than once [ofcourse will return a single copy]
EXAMPLE1 :UNIQUE FUNCTION
We’ll try to remove the duplicate entries.
We put the formula as
EXAMPLE 2:REMOVE DUPLICATE COLUMNS USING UNIQUE FUNCTION
Let us try to remove the duplicate columns.
EXAMPLE 3:INCLUDING THE ENTRIES WHICH OCCUR ONLY ONCE
Third argument decides whether the entries with more than one copy would be kept in the result or not. Let us try this with UNIQUE function and see if it works or not.
Let us try to keep only those entries which has unique single entry.
THE RELATIVE ROW AND COLUMN NUMBER
A cell is filled with YELLOW COLOR which we want to find. Now let us see in how many ways we can get this value.
The row and column number of the INDEX FUNCTION is relative to the RANGE SELECTED.
If we select Table 1, the value to be found will be at the row no. 4 and column no. 3.
If we select Table 2, the value to be found will be at the row no. 4 and column no. 3 again.
If we select Table 3, the value to be found will be at the row no. 3 and column no. 2.
So all these three different notations result in the same result. So always take care of the range selected and row and column no. selected.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.