If you’re an Office 365 user, a January 2016 software update to Excel 2016 introduced a new Funnel Chart feature, an improved AutoComplete feature, and six new worksheet functions. This article will explain these features in more detail.
Microsoft has decided to up its game with regard to pushing users toward its Office 365 subscription plans versus buying perpetual licenses (sometimes referred to as shrink-wrapped software). A recent Microsoft blog post uses the distinctions “Office 365 subscribers and Office 2016 one-time purchasers.” The Office 365 distinction also includes Excel Online, Excel for iOS, and Excel for Android.
The ability to create a Funnel Chart won’t affect most other Excel users that you share your work with, nor will the AutoComplete feature. However, Office 365 subscribers are going to have to be mindful when using any of the six new worksheet functions.
In short, if anyone other than an Office 365 subscriber using Excel 2016 opens a workbook that contains these new functions, then the formulas will return #NAME? instead of the expected values.
The Funnel Chart format is shown in Figure 1. This is designed to offer a sense of scale for amounts that can be arranged in descending order. If the Funnel Chart is available in your version of Excel 2016, you can access it by way of the Recommended Charts command on Excel’s Insert menu.
If a Funnel Chart doesn’t appear on the Recommended Charts tab, look on the All Charts tab of the Recommended Charts dialog box just below the Waterfall option. Note that Funnel Charts can be compromised in the same fashion as Waterfall Charts when opened in nonsupported versions of Excel.
Figure 1: Funnel Charts in Excel 2016 offer the ability to present numbers in a descending scale.
The AutoComplete option is actually a very nice enhancement. Let’s say that you want to use the CUMIPMT worksheet function in a spreadsheet, but can’t remember the exact function name. In Excel’s formula bar, or any worksheet cell, you can type =PMT, and as shown in Figure 2, the autocorrect list will show a list of five functions that include the letters PMT.
One-time Excel purchasers of Excel 2016, as well as anyone using an earlier version of Excel, must either use the Function Wizard button shown in Figure 2 or spell the function name correctly when crafting formulas.
Figure 2: You can now find worksheet functions by typing consecutive letters from any part of the function name.
Here are the new worksheet functions that you can only use in an Office 365-based version of Excel 2016:
TEXTJOIN: As shown in Figure 3, this function combines text from two or more cells, along with a delimiter of your choice.
Figure 3: The TEXTJOIN function simplifies combining text.
CONCAT: As shown in Figure 4, this function serves as a shorter and more flexible alternative to Excel’s CONCATENATE function.
Figure 4: The CONCAT function also combines text together, but without a specific delimiter.
IFS: As shown in Figure 5, the IFS function offers an alternative to nesting multiple IF statements within a formula. IFS supports up to 127 tests, as opposed to nesting up to 64 IF statements. With that said, in my opinion, if you’re testing more than a few items with IF or IFS, there’s most likely a better way to structure your data.
Figure 5: The IFS function enables you to write cleaner alternatives to nested IF statements.
SWITCH: As shown in Figure 6, the SWITCH function can serve as an alternative to both nested IF statements and the CHOOSE function.
Figure 6: The SWITCH function returns a value that corresponds to a number that you specify.
MAXIFS: You may have used the MAX function, which returns the largest value from a range of numbers. The MAXIFS function shown in Figure 7 enables you to return the largest value based upon one or more criteria in a list. Fortunately, you can replicate this functionality in any version of Excel.
Figure 7: The MAXIFS function returns the largest value from a list based upon one or more criteria.
MINIFS: This function in Figure 8 is the yin to MAXIFS yang, meaning it returns the smallest value from a range of numbers based upon one or more criteria. You can duplicate this just as you can replicate MAXIFS in any version of Excel.
Figure 8: The MINIFS function returns the smallest value from a list based upon one or more criteria.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.