3 new Excel 365 functions to make your work easier

 

by Kara Hiltz

Application: Microsoft Excel 365/2016

Operating System: Microsoft Windows

Building Excel formulas isn’t always easy, and sometimes Excel functions can get complex—raising the stakes for human error. Excel 365 offers three new functions that will replace some of your old standbys and simplify your toughest data analysis needs.

To get familiar with three new Excel functions, we’ll:

  • Reveal how the TEXTJOIN function can soothe your CONCATENATE woes.
  • Unveil the IFS function, which will eliminate those pesky nested IF formulas.
  • Introduce the MAXIFS and MINIFS functions, similar to the AVERAGEIF and SUMIF functions.

Excel’s functions have sometimes left something to be desired. Many are not user-friendly or require an extra step, such as to combine and nest functions, to make a formula work the way you need it to work. One of Excel 365’s greatest features is its new functions. Some of these functions are game-changers for Excel users. We’ll introduce you to three of the most important additions to Excel’s functions: (1) TEXTJOIN, as shown in Figure A, (2) IFS, and (3) the MAXIF/MINIF functions.

Note: If you have a standalone desktop copy of Excel 2016 without an Excel 365 subscription, you won’t have the functions discussed in this article. They are only available to Excel 365 subscribers.

IME18701A

A:

The TEXTJOIN function simplifies the process of consolidating text into one long string.

 

TEXTJOIN will replace CONCATENATE

The CONCATENATE function was always a go-to for Excel users who needed to string together text from different cells into one. But, especially when you needed to add spaces or punctuation, CONCATENATE functions could get bloated and complex.

Excel 365’s TEXTJOIN function allows you to simplify the process of consolidating text strings. In our example, we want to join the text in columns A, B., C, and D (prefix, first name, last name, and suffix) into a column that contains full names, as shown in Figure B. You can use CONCATENATE to accomplish this task, but you’ll see that the new TEXTJOIN function is much easier.

IME18701B

B:

The new TEXTJOIN function will help us consolidate the text in these columns in simpler terms than the old CONCATENATE function.

The CONCATENATE way

Let’s first take a look at the formula you would need with the CONCATENATE function to get the job done. Here’s the CONCATENATE formula you would need to type in cell E2 to create the combined text string:

=CONCATENATE(A2," ",B2," ",C2," ",D2)

Essentially, you would need to list all of the cells from which you want to pull text and separate them with any spaces or punctuation you’d want to include between quotation marks. If you’re only joining a few cells, this might not seem too bad. But the more cells you involve, the more convoluted the formula becomes.

TEXTJOIN changes the game

Now let’s see how the new TEXTJOIN function works in Excel 365. The function includes a minimum of three arguments: delimiter, ignore_empty, and text1 (plus text2, text3, etc. if needed). The delimiter argument defines how you want to separate the text strings. The delimiter can be a space (“ “), a punctuation mark (“,”), or a cell reference. The second argument, ignore_empty, must be TRUE or FALSE. If true, the function will ignore empty cells. Finally, the text1, text2, text3, etc. arguments include either the text you want to include between quotation marks or cell references to the text you want to include in the final text string. According to Microsoft, you can include up to 252 text arguments.

In our example, we’ll use the following TEXTJOIN formula in cell E3:

=TEXTJOIN(" ",TRUE,A3,B3,C3,D3)

As shown in Figure C, the results are the same but the TEXTJOIN function is simpler and easier to work with.

The one downside about using TEXTJOIN instead of CONCATENATE is that the TEXTJOIN function applies the same delimiter between all of the text arguments. The CONCATENATE function, however, allows more flexibility in delimiters because you need to define the delimiter between each new piece of the text.

IME18701C

C:

TEXTJOIN is a cleaner way to consolidate text in cells into one long text string.

IFS make nested IF functions history

Nested IF functions have caused so many headaches among Excel users. Trying to keep track of all of the conditions in a nested IF function is frustrating and leaves your formula prone to errors.

One of the new functions included in Excel 365 can solve your nested IF function problems by simplifying the process. The IFS function contains a simple list of conditions that must be true and what the cell will display if the condition is true. Microsoft will allow you to test up to 127 of these conditions.

As shown in Figure D, the Award column in our gradebook will cal[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage