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.
There are 10+ new functions in Excel 2019 and 365 that reduce the human effort and complexity of the formulas.
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:
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
Here marks are the named range that contains the marks and class is the named range that contains the class.
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:
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
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”)|
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.
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:
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.
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:
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.
I have explained the SORT function in detail here.
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.
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:
I have explained the SORTBY function here in detail.
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:
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:
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:
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:
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:
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:
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 :
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.
Let’s say if you want to concatenate each cell in range A2:A11, then the formula will be
To explore the CONCAT function in detail click here.
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.
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.
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.