Today in a blog post, the Microsoft Excel team highlighted the newest functions for formula editing. These 6 new functions were picked based on feedback from Office 365 and work to help simplify the task of common calculations or spending long amounts of time, building custom functions.
Here are the udpates:
- TEXTJOIN & CONCAT Functions
Theses two commands help simplify the common task of combining text strings in Excel. When combining two or more cells in Excel of information, you would need a delimiter or comma to separate the each item:
- Example:
The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)
The new process eliminates the need for separation with commas or quotations, simplifying the process in Excel.
- Example:
The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)
- IFS & SWITCH Functions
The IF function in Excel is commonly used for instances where you want a result from a test on a common condition. This command is created by nested IF which resemble this, “IF(IF(IF()))”, however this old method can both confusing and time consuming. The Excel team has since made an update to alleviate this confusion with the new IFS function. An example highlighted by the Excel team, for the new IFS function, was the use of this function to dictate a grade from a test based on the number value of each score.
- Example
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
This new IFS function simplifies the formula by allowing the user to specify these conditions on a single function. For SWITCH, this function allows multiple conditions by specifying an expression and series of results and values.Values are compared to the expression and when a match is made, generates a result.
- Example
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 previous method with Nested IF functions, before this latest update from the Excel team would result in this example being much longer in length and complicated to create, as the blog post shows below:
- MAXIFS & MINIFS Functions
These two functions help calculate a minimum and maximum in a range. The additional abilities these new functions have allow users to create conditions, within that range, before calculating the minimum or maximum values.
- Example:
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.
These latest function updates in Excel are available for all Office 365 subscribers. With Microsoft continuing to listen to their customers and applying that feedback, users are receiving quick updates to the most-requested features. This latest update is a part of the continued focus on Office updates from Microsoft and Office 365 subscribers.
How do you see yourself benefiting from these latest additions to Excel? Let us know in the comments.
