Hot Off the Presses: New Excel Functions Cut Steps, Enhance Dashboards

Tips from MrExcel, Bill Jelen

As a former financial analyst and current Microsoft Certified trainer, I’ve watched Microsoft Excel undergo a myriad of changes since its introduction in the mid-1980s. When I’m approached about how to solve a particular problem in Excel, I’m happy to tell people that the Microsoft development team has a renewed emphasis on creating new calculation functions that can help them simplify formulas and be more efficient.

For managers and C-level executives, Microsoft also continues to add more visual features to support sophisticated dashboards within Excel. In this blog, I’ll discuss five of Excel’s very latest additions for cutting steps and summarizing data with improved dashboards.

 1. Pivot Table Defaults

Pivot tables let you summarize thousands of rows in a few clicks, but they’ve had some annoying aspects for years. When created, the row labels aren’t specific or attractive, requiring you to repeat multiple steps to get the table into a more consumable format—every single time you create a pivot table.

So, I entered the idea for an improved pivot table experience at excel.uservoice.com, where you can submit your Excel ideas and if you get enough user votes, Microsoft responds. The input was overwhelming. As of last week, Microsoft Office 365’s Excel lets you change the default layout of pivot tables. Look for the feature under File → Options → Data or File → Options → Advanced → Data.

Now, if you are using Microsoft Office 365, you can change the default layout of your pivot table so that each field gets its own column—and you aren’t stuck cleaning up the pivot table each time.

2. Six New Chart Types

There are six powerful new charts in Excel that help you quickly visualize common financial, statistical and hierarchical data: Waterfall, Histogram, Pareto, Box & Whisker, Treemap, and Sunburst. All of these are available in Excel 2016 except for Pareto, which is only with Office 365.

3. Six New Functions

Available with Office 365, these six new functions have been added to the longstanding list of 455. They are part of 30 new functions envisioned by Excel ModelOff Financial Modeling World Champion Joseph McDaid, who now works with Microsoft.

  • IFS – If you are tired of nesting all of those IF functions when you have multiple decision levels, you’ll really appreciate the new IFS function. You simply list all the conditions separated by commas:

IFS(A2=100,”Perfect”,A2>90,”Great”,A2>80,”Good”,A2>70,”Average”,True,”Fail!”)

  • MINIFS and MAXIFS – Similar to IFS, these functions return the minimum or maximum value among cells specified by a given set of conditions or criteria. It would be great if you were looking for the product with the highest revenue in a particular region, for example:

MINIFS(Revenue,Region,”West”,Product,”Gizmo”)

MAXIFS(Revenue,Region,”West”,Product,”Gizmo”)

  • SWITCH – Also similar to IFS, but you don’t have ranges and instead look for different values from a list. It would work well if you are checking for text values:

SWITCH(WEEKDAY(A2),1,”Weekend!”,7,”Weekend!”,”Got to go to work…”)

  • CONCAT and TEXTJOIN – These new functions are awesome if you want to string together a list of all sales reps, regions or customers, for example. In the past, if you had a list of names you wanted to join, you had to point to each cell. CONCAT lets you put them together with one function:

CONCAT(A2:A20)

In reality, you’d probably want to separate first and last names, for instance, rather than run them all together. TEXTJOIN lets you do so with a delimiter of your choice. Previously, you’d have had to tediously enter each delimiter manually in a formula.

TEXTJOIN(“or”,True,A2:A8)

 4. Insert Icons

Another enhancement for improved visual effect and dashboards, these are handy for jazzing up reports with descriptive clip-art type icons. You might use it in a dashboard for a travel expenses, for example. It’s available in Office 365.

 5. Insert TAB 3DMAP

This feature lets you insert a map with associated data for a sophisticated three-dimensional effect. You could, for example, see your customer list by location or products, complete with zoom-in capability. This feature is available in both Excel 2016 and Office 365.

With these five powerful new Excel additions, you can become more efficient at attractive, real-time data delivery. This is especially true if the Excel spreadsheet links directly to a data source like your ERP system, allowing you to achieve advanced business intelligence (BI) functionality.

 

For more Excel tips and tricks from Bill Jelen, attend a webinar in your region: AmericasEurope-Africa-Middle East, or Asia-Pacific.