Excel is a great tool for reporting, analyzing, organizing and automating data. The Excel Functions help a great deal with working on data. The functions like COUNTIFS, SUMIFS, VLOOKUP, etc are the most powerful and frequently used functions since there inception in Excel world.
Although the functions available in Excel 2016 and older are enough to work out any kind of calculation and automation, but sometimes the formulas get tricky. For example, if you won't find the maximum value with some conditions, you have to use some tricks in the 2016 older Excel version. These kinds of minor but important things are solved in Excel 2019 and 365.
Microsoft Excel is the world’s most famous and widely used spreadsheet editing application.Originally developed by the Microsoft in 1987, this software received numerous expansions of capabilities, making it the go-to source for spreadsheet editing, use of graphing tools, pivot tables, macro programming, and much more.Available today on all modern platforms, including Windows, MacOS, Android. Organize, visualize and extract insights from your data more easily than ever with powerful new features. Collaborate with ease. Microsoft Excel with a Microsoft 365 subscription is the latest version of Excel. Previous versions include Excel 2013, Excel 2010, Excel 2007.
There are 10+ new functions in Excel 2019 and 365 that reduce the human effort and complexity of the formulas.
Microsoft Excel 2020 full offline installer setup for PC 32bit/64bit Microsoft Excel is the world’s most famous and widely used spreadsheet editing application. Originally developed by the Microsoft in 1987, this software received numerous expansions of capabilities, making it the go-to source for spreadsheet editing, use of graphing tools, pivot tables, macro programming, and much more.
1. The MAXIFS Function
In excel 2016 and older, if you want to get the maximum value in a range when one or more conditions match, you have to use MAX with IF with some tricks. That's not much difficult but time taking and confusing to some.
The Excel 2019 introduces a new function named MAXIFS function. This function returns the maximum value from an array when all the given conditions are matched.
The syntax of the function is:
| =MAXIFS(max_range,criteria_range1,criteria1, criteria_range2,criteria2...) |
Max_range1: It is the numeric range that contains the max value.
Criteria_range1: It is the criteria range that you want to filter before getting max value.
Criteria1: It is the criteria or the filter that you want to put on criteria_range before getting the Max value.
Suppose you need to get the maximum marks from class 3 then the formula will be
| =MAXIFS(marks,class,3) |
Here marks are the named range that contains the marks and class is the named range that contains the class.
Read about MAXIFS function in detail here.
2. The MINIFS Function
Same as MAXIFS function the MINIFS function is used to get the minimum value from the given range when all of the given conditions are satisfied.
The syntax of the function is:
| =MINIFS(min_range,criteria_range1,criteria1, criteria_range2,criteria2... |
Min_range1: It is the numeric range that contains the minimum value.
Criteria_range1: It is the criteria range that you want to filter before getting minimum value.
Criteria1: It is the criteria or the filter that you want to put on criteria_range before getting the minimum value.
Suppose you need to get the minimum marks from class 3 then the formula will be
| =MINIFS(marks,class,3) |
Here 'marks' is the named range that contains the marks and 'class' is the named range that contains the class.
Read about MAXIFS function in detail here.
To find minimum value from in range with conditions in excel 2016 and older read this.
3. The IFS Function
As the nested Ifs has a special place in our daily work life, we like it very much. But to some new learners, it is complex. The nested ifs let us check multiple conditions and return a different value when any of the conditions is met. The formulas get complex with more and more IF in function.
The Excel 2019 and Excel 365 now use IFS function. It can check multiple conditions and return different values for each condition.
Syntax of IFS function:
| =IFS (condition1, Value1_If_True, [condition2, Value2_If_True], ...) |
Condition1: The first condition.
Value1_If_True: Value if the first condition is true.
[Condition2]: This is optional. The second condition, if you have any.
[Value1_If_True]: Value if the second condition is true.
You can have as many combinations of conditions and values you want. There's a limit but you will never need to reach that limit.
Let us say that you need to give the grades to the students by there marks. For marks more than 80, grade A, B for more than 60, C for more than 40 and F for less than or equal to 40.
| =IFS (A1>80, 'A', A1>60, 'B',A1>40, 'C',A1<=40, 'F') |
A detailed explanation of the IFS function can be found here.
4. The SWITCH Function
The switch function returns different values depending on the one expression results. Sounds like IFS? It is kind of. Actually this function is for replacing another kind of nested IF formulas.
Unlike the IFS function that returns values based on TRUE, FALSE; the SWITCH function return values based on VALUES returned by the expression.
| =SWITCH (expression, value1,result1, [default or value2,result2],...) |
Expression: This can be any valid expression that returns some values. A cell reference, a formula or static value.
Value1,result1: The value and result are paired. If the value returned by the expression is value1 then the result1 will be returned.
[Default or value2,result2]: If you want to return some default value, define it here. Else define the value2 and result2. It is optional.
For example, if you have a formula that returns the names of animals. Now, depending on the returned name of the animal you want to return the signature sound of that animal.
| =SWITCH (A1, 'Dog','Bow Wow', 'Cat','Meow', 'Speaks') |
I have explained the SWITCH function in detail here.
5. The FILTER Function
The FILTER function is used to filter data based on some criteria. We have used the filter option from the home tab in Excel. The FILTER function works the same as the filter option. It just returns the filtered data using a function. This filtered data can be used as a source of data to other formulas.
The syntax of the FILTER function is:
| =FILTER(array,include,[if_empty]) |
Array: This is the array that you want to filter. It can be one dimensional or two dimensional.
Include: It is the filter that you want to put on the Array. Like, colors='red'.
[if_empty]: This is optional. Define any text or expression if the filter returns nothing.
The below formula returns all the fruits whose color is red.
| =FILTER(fruits,color='red' ,'no fruits found') |
Here fruit and color are named ranges that contain names of the fruits and their colors, respectively.
You can read about the FILTER function in detail here.
6. The SORT Function
In excel 2016 and older, it was really tricky to get a sorted array using a formula. This process is simplified in Excel 2019 and 365.
The Excel 2019 introduces function SORT. The SORT function sorts the given array in ascending or descending order by the given column/row.
The syntax of the SORT function is:
| =SORT(array,[sort_index],[sort_order],[by_col]) |
Array: It is the reference of array or range that you want to sort.
[sort_index]: The column number in the two-dimensional array by which you want to sort the range. By default, it is 1.
[sort_order]: The order by which you want to sort the array. For ascending it is 1 and for descending it is -1. By default, it is 1.
[by_col]: Set it True(1) if you want to sort a horizontal array. By default it False(0) for Vertical data.
Let's say if you want to sort values in the range A2:A11 ascendingly. then the formula will be.
| =SORT(A2:A11) |
I have explained the SORT function in detail here.
7. The SORTBY Function
The SORTBY function is similar to the SORT function. The only difference is the sorting array does not need to be the part of the sorted array in the SORTBY function.
| =SORTBY(array,sorting_array1,[order],...) |
Array: This is the array that you want to sort.
Sorting_array1: This is the array by which you want to sort the array. The dimension of this array should be compatible with the array.
[order]: Optional. Set it to -1 if you want the order to be descending. By default, it is ascending(1).
Let's say if you want to sort the range A2:A11 by range B2:B11, in descending order. Then the formula in excel 2019 or 365 will be:
| =SORTBY(A2:A11,B2:B11,-1) |
I have explained the SORTBY function here in detail.
8. The UNIQUE Function
In Excel 2016 and older, we used a number of functions in combination to get all the unique values from the given list. The formula used is quite complex and hard to understand.
The Excel 2019 and 365 introduces one simple UNIQUE function that returns all the unique values from a given array.
The syntax of the UNIQUE function is:
| =UNIQUE(array,[by_col],[exactly_once]) |
Array: The array from which you want to extract unique values:
[by_col]: Set it TRUE(1) if the array is horizontal. By default, it is FALSE for vertical data.
[exactly_once]: set it TRUE(1) if you want to extract values that occur only once in the array. By default, it is FALSE(0) to extract all unique values.
Let's say I want to get only one instance of each value from range A2:A11, then the formula will be:
| =UNIQUE(A2:A11) |
To read about the UNIQUE function in detail you can click here.
9. The SEQUENCE Function
To get a sequence of numbers in excel 2016 and older we use a combination of functions. The solution does work but it is complex.
The Excel 2019 and 365 provides the solution in the form of the SEQUENCE function. The sequence function simply returns the series of the number.
The syntax of the SEQUENCE function is:
| =SEQUENCE(rows,[columns],[start],[step]) |
Rows: The number of rows to which you want to spill the sequence.
[column]: The number of columns to which you want to spill the sequence. The numbers will first fill in the columns and then rows. The column is optional. By default, it is 1.
[start]: Optional. The starting number of the sequence. By default, it is 1.
[step]: This the increment number for the next number. By default, it is 1.
The simple example is getting a series of 1 to 10. The formula will be:
| =SEQUENCE(10) |
To understand the SEQUENCE function in Excel 365 in detailed read this.
10. The RANDARRAY Function
This is another dynamic array formula that returns an array of random numbers. It is a combination of RAND and RANDBETWEEN function. You can get fractional random numbers or whole numbers. You can specify the number of random numbers you want. Even rows and columns in which you want to distribute these numbers.
The syntax of the RANDARRAY function is:
| =RANDARRAY([rows],[columns],[min],[max],[integer]) |
All the arguments in this function are optional. By default, it works as the RAND function.
[rows]: The number of numbers that you want vertically (number of rows you want to fill).
[columns]: The number of numbers that you want horizontally (number of columns you want to fill).
[min]: The starting number or the minimum value of the random number/s.
[max]: The maximum range of the number.
[integer]: Set it true, if you want the random numbers to be whole numbers. By default, it is false and returns fractional random numbers.
The below function will return five random fractional number row-wise:
| =RANDARRAY(5) |
Read about the RANDARRAY function in detail here.
Excel New Version 2016 Download
11. The CONCAT Function
In Excel 2016 and older, it is not easy to concatenate more than one cell or range using one formula.
The excel 2019 and 365 the problem is solved with the function CONCAT. The function can take multiple cells, ranges as arguments.
The syntax of the CONCAT function is :
| =CONCAT(text1,[text2],...) |
Text1 : The text1 can be any text or range that you want to concatenate.
[text2]: This is optional. This also can be any text or range.
Excel New Version Share Workbook
Let's say if you want to concatenate each cell in range A2:A11, then the formula will be
| =CONCAT(A2:A11) |
To explore the CONCAT function in detail click here.
12. The TEXTJOIN Function
The above function does concatenate all the cells in a range but it does not concatenate the cells with any specified delimiter. Let's say if you are preparing a file for CSV formate than you will need to concatenate the cells with comma. In that case CONCATENATE and CONCAT function, both will fail.
Here the TEXTJOIN function works wonder and concatenates the given texts with the given delimiter.
| =TEXTJOIN(delimiter, ignore_empty_cells,text1,[text2],...) |
Delimiter: This is the delimiter that you want to use a separator among individual texts. It can be a comma (,), semicolon (;) or anything, even nothing.
Ignore_empty_cells: This is a binary variable. Set it to TRUE if you want to ignore empty cells in ranges, else set to FALSE to include the empty cells.
Text1: This is the text that you want to join. It can be individual texts, cells or entire ranges.
Let's say I want to concatenate range A2:A11 with a comma, ignoring the blank cells.
| =TEXTJOIN(',',1,A2:A11) |
To understand this function in detail, click here.
This article was only an introduction to the new function of Excel 365 and 2019. I have explained these functions in detail in separate articles. You may click on the links available on each function in the article to understand the function fully. There are other functions like XLOOKUP that are not released yet.
If you have any doubts regarding Excel or VBA topics, ask in the comments section below. Tell us how can we improve. We appreciate your suggestion and eager to hear from you.
Related Articles:
Create VBA Function to Return Array | To return an array from user defined function, we have to declare it when we name the UDF.
Arrays in Excel Formul|Learn what arrays are in excel.
How to Create User Defined Function through VBA | Learn how to create user-defined functions in Excel
Using a User Defined Function (UDF) from another workbook using VBA in Microsoft Excel | Use the user-defined function in another workbook of Excel
Return error values from user-defined functions using VBA in Microsoft Excel | Learn how you can return error values from a user-defined function
Popular Articles:
Split Excel Sheet Into Multiple Files Based On Column Using VBA | This VBA code split excel sheet base on unique values in a specified column. Download the working file.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016 | To turn off warning messages that interrupt the running VBA code, we use the Application class.
Add And Save New Workbook Using VBA In Microsoft Excel 2016 | To add and save workbooks using VBA we use Workbooks class. Workbooks.Add adds new workbook easily, however...