Count on Excel: What Accountants Can Expect in Office 2016

Excel tips

This fall, Microsoft launches Office 2016 for Windows*, and it’s already offered some preliminary downloads. This is especially big news for accounting and finance organizations that rely on Excel.

And that includes a lot of businesses. Sixty-three percent of U.S. companies and 75 percent of small companies interviewed for the Benchmarking the Accounting & Finance Function from Robert Half and Financial Executives Research Foundation (FERF) said they use Excel for budgeting and long-range planning.

A boon to finance professionals, the latest revisions to Excel include a number of tools to help manage data, mine it for relevant information and present it in useful ways. Here are four key improvements to look for in Office 2016.

1. Improved “Pivot Tables”

Excel can be a boon for many accounting services that rely on analyzing and visualizing data. For example, the “Pivot Tables” filter, sort and calculate in just a few clicks, turning blocks of figures into meaningful tables.

To try out pivot tables, select an array of data and use “Recommended Pivot Tables” from the “Insert” ribbon. You will then be presented with a range of options, including what you’d like to calculate and how you’d like it presented.

Excel 2016 includes the added ability to work with dates, meaning that you can drill down into your figures with even more accuracy, looking at specific dates or periods of time. The ability to handle data sources has also been adjusted; if you are compiling data from more than one table, Excel 2016 will automatically detect any relationships and define them for you.

2. New chart types

Bar graphs, line graphs, pie charts and more can be used to show trends and comparisons. They provide quick snapshots and are perfect when numbers alone don’t tell the whole story.

In Excel 2016, charts have been upgraded with a new focus on business intelligence. Five new types have been introduced, including box and whiskers, waterfall, histograms and paretos, and hierarchical treemaps and sunbursts. To create a chart, highlight the figures to visualize and click “charts” within the “insert” ribbon. Here you will see a range of available chart styles.

3. New formulas

In addition to doing basic math, Excel’s formulas can also search, sort and filter data. They are among the most versatile and powerful aspects of Excel but can also be complicated to master. Formulas are typed directly into cells, effectively programming them with a short line of code. For finance professionals, SUMIFS and SUMPRODUCT are useful for filtering numbers before adding them together. IRR, NPV and PV perform complex functions relating to Internal Rate of Return, Net Present Value and Present Value forecasts.

Excel 2016 introduces FORECAST to predict future values based on historical data. The formula can also be used to calculate the confidence interval or show the length of a repetitive pattern that Excel has detected. To get the most from Excel, learn more about the complex formulas that make light work of difficult calculations. 

4. Power Query as standard

Excel 2016 comes packaged with Power Query, a downloadable add-on in previous versions. Power Query inserts data from external sources, either your own or public information from Internet sources such as Wikipedia.

[NOTE: You can add Power Query to previous versions of Excel by choosing “File” from the ribbon, then “Options” and “Add-Ins.” Select “Manage,” “COM Add-Ins” and choose “Microsoft Power Query for Excel.” This will add a “Power Query” ribbon. Click this and choose “From Web,” “From File” or “From Database” to import data.]

According to the buzz within the finance industry, Microsoft’s latest software update comes loaded with tools to help assess, process and visualize data and trends in new and more complex ways. By mastering the built-in features, you’ll get more insight for your effort and more value from your information.

*Office 2016 for Mac will be available at the same time as the Windows version and has already launched for anyone who is an Office 365 subscriber.

Share you're your tips and advice for getting the most out of Excel in the comments below.

Excel Tips and Tricks from A CPA
5 More Excel Tips and Tricks for Accountants
Continue to Excel: 4 More Easy Spreadsheet Tips for Accountants

Excel Use

Tags: Excel