6 new Excel functions that simplify your formula editing experience

The Excel team is happy to announce six new functions that simplify some of your common calculations and help you avoid the tedious work of building custom functions to accomplish these tasks. We picked these six functions based on your feedback and will continue to provide more improvements in the future as part of your Office 365 subscription. We added TEXTJOIN and CONCAT to combine a list or range of text strings, MAXIFS and MINIFS to find the maximum or minimum in a range given one or more conditions and IFS and SWITCH to avoid messy nested IF functions. Read on for details on each function.

Combine text strings using TEXTJOIN and CONCAT

A very common task for users in spreadsheets is to combine text strings, but until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter, such as a comma separating each item. You can simply refer to the range and specify the delimiter once and let Excel do all the heavy lifting.

Microsoft Excel

Turn data into insights.

The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)

The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)

Let’s say you just want to join the parts of an address into a single text string. The old way would require you to specify each cell and repeat a comma that separates each part:

The new way is much simpler. You simply specify the comma (or whatever separator you want), choose whether to ignore empty cells and then specify the range.

If you want to know more, see the online help for TEXTJOIN and CONCAT.

IFS and SWITCH functions help specify a series of conditions

The new IFS and SWITCH functions give you an alternative to using a series of nested IF functions, like “IF(IF(IF()))”, when you have more than one condition that you want to test to find a corresponding result. The IF function is one of the most commonly used functions in Excel, and using IF inside IF (nested IF functions) has been a common practice in Excel, but it can be challenging or confusing at times.

The advantage of using the new IFS functions is that you can specify a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true—making it very straightforward to create and read the formula afterward. For example, let’s say you want to get the grade letter for a given score on a test. Using the IFS function, it might be something like this:

=IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1<60, “Fail”)

This can be read as, if the grade in C1 is greater than or equal to 90, it’s an A. Otherwise, if it’s greater than or equal to 80, it’s a B. Otherwise, if it’s greater than or equal to 70, it’s a C and so on. It’s pretty easy to write it this way and it’s also straightforward to read and understand what’s going on.

The SWITCH function also handles multiple conditions. What makes it different is that rather than specifying a series of conditional statements, you specify an expression and a series of values and results. The values are compared to the expression, and when the first exact match is found, the corresponding result is applied to the cell. You can also specify a “default” result that will be returned if none of the values are an exact match for the expression. The advantage of the SWITCH function is that you can avoid repeating the expression over and over, which sometimes happens in nested IF formulas.

In the example below, the first part of the formula extracts the size code (i.e. XS, M and G) from the middle of the item in column B. It’s rather long, so it’s nice that SWITCH only needs it to be written once and it can be compared to a list of values.

The example below can be explained as:
Extract the size code from the item in column B. If it equals “XS”, the result is “Extra Small.” Otherwise, if it equals “S”, the result is “Small” and so on. If there’s no match, the result is “Not Specified.”

The same result could be calculated using nested IF functions, but it would be significantly longer, as shown below.

If you want to know more, go to the online help for IFS and SWITCH.

Filter and calculate data with MAXIFS and MINIFS

If you’re familiar with COUNTIFS, SUMIFS and AVERAGEIFS, then MAXIFS and MINIFS don’t need much explanation. The classic MAX and MIN functions calculate the maximum or minimum value in a range, but what if you need to apply conditions to filter your data? This is precisely what MAXIFS and MINIFS allow. You can specify one or more conditions that filter the data before calculating the max or min. The conditions can be applied to adjacent ranges or the range that contains the values. For example, let’s say a retailer has a table containing sales data for all their stores. They can use the MAXIFS and MINIFS functions to calculate the maximum and minimum sales figure for a specified item in stores located in a specified region.

In the example shown below, MINIFS and MAXIFS are used to calculate the min and max sales figures from the table, but it only includes values from the Sales column if the value in the Retailer column is “BigMart,” the value in the Brand column is “Longlast” and the value in the Sales column is greater than zero.

If you want to know more, go to the online help for MAXIFS and MINIFS.

Try them yourself!

Try the new functions and see how they can help you simplify your formulas.