By Thomas G. Stephens Jr., CPA, CGMA, CITP

Excel 2019 has been available for several months now, and many companies have begun the process of upgrading to the new version of Microsoft’s spreadsheet tool. However, the upgrade will not pay dividends unless team members are aware of and take advantage of some of the new features found in the 2019 version. This blog introduces five of the top new Excel functions for accounting and how you or your staff might take advantage of each.

Two New Data Types

Excel 2019 introduced two new data types – Stock and Geography – that you can use to link data from the internet into your spreadsheets. As shown in Figure 1, when using the Stock data type, you can link selected elements related to a company into a spreadsheets to assist with reporting and analysis.

Figure 1 - Data linked from a Stock data type in Excel 2019.

Additionally, you can use the Geography data type to link demographic and similar data about cities and states into your spreadsheets, as shown in Figure 2.

Figure 2 - Data linked from a Geography data type in Excel 2019.

Both Stock and Geography can provide quick and easy access to data that many accounting and finance professionals need within their spreadsheets, without them having to spend the time finding and manually entering the data. As such, these two new options should help CPAs work more efficiently and accurately within Excel.

Ideas

Excel’s new Ideas feature is the embodiment of artificial intelligence in Excel. With Ideas, simply click in a range of data and then click “Ideas” from the Insert tab to have Excel automatically analyze the data for patterns and trends. Once Excel completes the analysis, it provides numerous prebuilt PivotCharts that you can incorporate into your workbook.

New Charts

With Map and Funnel charts, Excel 2019 adds two chart types to help you communicate more effectively. Map charts help you to plot data by countries, states/provinces, counties, or even postal codes. You can use Funnel charts to help tell a story, such as how a business went from receiving 500 customer contacts during a month to closing sales on 37 of these contacts.

New Functions

Excel 2019 adds six new functions:

• CONCAT
• MAXIFS
• MINIFS
• TEXTJOIN
• IFS
• SWITCH

A full discussion of each is beyond the scope of this brief blog, but one of the new functions – IFS – warrants a call-out. The IFS function allows you to write sophisticated formulas that, in the past, required including multiple “IF” functions nested inside each other. With IFS, you will only have to include the function once in the formula. Consider the formula shown below. In it, notice that the IF function had to be included three times.

=IF(A1=1,"Blue",IF(A1=2,"Yellow",IF(A1=3,"Green",)))

With the new IFS function, the formula can be simplified so that the IFS function needs to be stated only one time:

=IFS(A1=1,"Blue",A1=2,"Yellow",A1=3,"Green")

The new IFS function should lead to shorter and simpler formulas, adding efficiency and reducing spreadsheet errors.

Default PivotTable Layouts

Beginning with Excel 2019, you can establish a default layout for all new PivotTables. To do so, click File then Options, and near the top of the window access Data Options. There you will select and store your default settings for PivotTables (see Figure 3). Once you establish these options, they will control how all future PivotTables appear, but they will not have any impact on PivotTables already created.

Figure 3 - Default PivotTable options in Excel 2019.

Conclusion

Some past releases of Excel provided an overwhelming volume of new features. Excel 2019 differs in that regard. While I did not attempt to mention all the new features in Excel 2019, it is fair to say that the overall number is small when compared to prior releases. But it is also fair to say that many of these new features are more substantial than some features released in prior versions.

If you already have access to this version of Excel, begin exploring how these tools can help you work more efficiently and accurately. If you do not have access to Excel 2019 yet, keep these new features in mind so that when you do gain access, you will be prepared to take advantage of all that Excel has to offer.

Thomas G. Stephens Jr., CPA, CGMA, CITP, is a shareholder in K2 Enterprises, where he develops and presents continuing professional education programs to accounting, financial, and other business professionals across North America. You can reach him at tommy@k2e.com.

For more on Excel, try one of these courses.

Sign up for weekly professional and technical updates in PICPA's blogs, podcasts, and discussion board topics by completing the form here.