Tech Talk: Excel 2016

Microsoft recently released Office 2016, and with it, the next version of my favorite program—which is, of course, Excel.

The user interface is about the same as 2013 and you’ll feel right at home. Microsoft focused on expanding Excel’s data visualization capabilities by introducing several new chart types and on Excel’s ability to import and transform data from a wide variety of external sources. Let’s walk through the improvements that caught my attention.

Consistency Across Platforms

Microsoft has been working to make the Excel experience more consistent across platforms. In previous versions, Excel for Mac felt much different than Excel for Windows. Now, when I run both and compare them side-by-side, they are similar—not identical, but much closer. I’ve included screenshots from Excel for Mac and for Windows in figures 1 and 2. Can you tell which is which?

Figure 1

Figure 2

This is a welcome update for Excel users, because it means we can simply learn Excel and use it regardless of operating system or device. Although I primarily use Excel for Windows, I do occasionally use Excel for Mac, iPad and the web—and I’m happy they have a similar interface.

External Data

If you retrieve data from external data sources, you will be pleased with Excel 2016. Capabilities previously only available via the separate Power Query add-in are now built into Excel. For example, you can now define queries and data transformations in Excel. This is great news if you frequently work with data that comes from external sources because it will reduce the amount of manual effort required to get and prepare data for use. The related commands are found on the Get & Transform group, on the Data ribbon tab.

As I look through the options in the New Query command icon, I see many supported data sources, such as Oracle, IBM DB2, MySQL, Hadoop, Active Directory, Exchange, SalesForce and Facebook. This opens up many opportunities to streamline the data acquisition process.

Data Visualization

Excel users who present data and information graphically will be happy to know that there are several new chart types, including waterfall, treemap (Figure 3) and histogram.

Figure 3

The ability to generate 3D maps to visualize geographic data, previously available with the separate Power Map add-in, is now built into Excel. The related commands are found on the Insert ribbon tab.

PivotTables

There are several enhancements to the PivotTable feature, the most dramatic of which is automatic time grouping. In a previous article (PivotTable Month Groups, May 2015 California CPA) I discussed the steps for grouping a PivotTable by month. Now, the moment you insert a date field into a PivotTable report, Excel detects the data type and automatically provides month groups. Wow! This means it is far easier to group by month since it doesn’t require an extra step. As you know, I love anything that helps us get our work done more quickly.

Those are the enhancements that I find most interesting, but there are many others, including the Tell Me box and the FORECAST function. For a more complete list of what’s new in Excel 2016, check out the Microsoft website.

Heavy Excel users who retrieve data from external data sources and want many data visualization options will absolutely love the upgrade to Excel 2016. Casual Excel users probably won’t use or benefit from the enhancements, so staying put with 2013 for a while should be just fine.

To be sure, with the new capabilities and improvements, this is the most powerful version of Excel released to date. Thanks, Microsoft! And remember, Excel rules.

Note: If you’re looking for the answer, Figure 1 is Excel for Windows; Figure 2 is Excel for Mac.