Microsoft Excel 2016 Charts and Analytics: Are You Ready? Can You Wait?

Excel

Microsoft Excel 2016 was released September 22, 2015. Do you need its new charts and a few analytics gadgets it has added? I’m not so sure. However, if you work with teams there are some enterprise collaboration features that will make life less frustrating. (It works on Windows 7 SP 1 or later.)

Overall, the major change between Excel 2010/2013 and Excel 2016 is better collaboration for enterprise users and consistent interface for mobile users. There are few interface changes from Office 2013; it’s a pretty flat learning curve. Although there are a few new features such as “Tell Me” that help do analysis and forecasting for you. What I’d like to show you here are some of the new charting and analytic features.

Let’s start with charting and then cover analytics improvements. There are six new charts in Excel 2016. They include Waterfall, Histogram, Pareto, Box & Whisker, Treemap and Sunburst. Some of these are not that hard to create in Excel 2010 and 2013. A couple, Treemap and Sunburst, are next to impossible.

Excel 2016 Pareto Chart

The Pareto Chart, the story behind it, and the turmoil it created in the world is a story that I find fascinating. It was all sparked by a sociology study into the distribution of wealth. Vilfredo Pareto’s chart and studies on wealth fueled fascism and may have helped ignite World War II. But, his statistics have stood the test of time for predicting profitable customers, wealth distribution, word usage, and manufacturing errors. Even in the democracies of Europe and the US the distribution of wealth does not fit a normal bell-shaped distribution as most people think, the distribution is extremely uneven with a small percentage holding the majority of wealth. https://en.wikipedia.org/wiki/Vilfredo_Pareto

Pareto Charts are the visual representation of the 80/20 rule.

Pareto Charts are the visual representation of the 80/20 rule.

A Pareto Chart is the visual representation of the 80/20 rule – 20% of input accounts for 80% of output. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/07/02/introducing-new-and-modern-chart-types-now-available-in-office-2016-preview/

Pareto Charts are a powerful tool for identify where to focus to get maximum impact. Pareto’s chart is the graphical representation of the 80/20 rule – 20% of your effort produces 80% of your results – 20% of your customers produce 80% of your revenue, 20% of your customers produce 80% of your support issues, and so forth. Think about the 80/20 rule when you next make choices about what you want to do or where you want to spend your energy!

Excel 2016 Waterfall Chart

Waterfall charts show gain or loss in financial statements or project management reports. It’s a quick way to identify sources of positive contributions and negative losses.

Use the Excel 2016 Waterfall chart to show  loss and gain in finance and project management.

Use the Excel 2016 Waterfall chart to show loss and gain in finance and project management.

Use Waterfall charts to show gain or loss in financial or project management reports. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/07/02/introducing-new-and-modern-chart-types-now-available-in-office-2016-preview/

Excel 2016 Histogram Chart

If you had high school biology or physics you probably hand-drew many histograms. Histograms are like column charts, but instead of showing data for discrete points, like Jan, Feb, Mar, they show data as it fits in groups or bins. For example, population age groups of 0 – 5, 6 – 10, 11 – 20, 21 – 30, 31 – 40, etc.

Histograms show the frequency of distribution with bins.

Histograms show the frequency of distribution with bins.

Use Histograms to see the frequency of occurrence within ranges. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/07/02/introducing-new-and-modern-chart-types-now-available-in-office-2016-preview/

Excel 2016 Box and Whisker Chart

Box and Whisker charts are the Swiss Army knife or multi-purpose tool of charting. You can really pack a lot of information into a small area. However, I would recommend you always include an example chart with instructions and explanations of mean, median, and the percentile groups.

In one or two small charts you can show the mean, median, major distribution bins, and outliers. The following chart from Microsoft shows the price distribution of book genres in a small book store.

The Box and Whisker chart is the Swiss Army knife of charting.

The Box and Whisker chart is the Swiss Army knife of charting.

Box and Whisker charts are like the Swiss Army knife for reporting statistics. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/07/02/introducing-new-and-modern-chart-types-now-available-in-office-2016-preview/

Learning more about how to use the Histogram, Pareto, and Box and Whisker Charts with Excel

To learn more about how to use the Histogram, Pareto, and Box and Whisker charts in business, then check out this blog written by the Excel chart team. It’s a very concise description of how to use this trio.

https://blogs.office.com/2015/08/18/visualize-statistics-with-histogram-pareto-and-box-and-whisker-charts/

Excel 2016 Treemap

I have seen the Treemap chart created in Excel, but it is an excruciating process. It looks like Excel 2016 has taken all the torture out of it.

Use a Treemap when you have a lot of data in different categories and subcategories and you want to show the relative size and relationship of the data. In this example, from the Microsoft blog, categories of books are grouped by color, and the size of each rectangle is proportional to that book sub-categories revenue.

This is a chart you can use for a 30 second PowerPoint slide to show relative sizes of revenue contribution, or you could settle down for an hour with a cup of coffee, start comparing relative sizes, and dig into the background detail behind each rectangle.

Excel 2016 Treemaps group categories by color and location.

Excel 2016 Treemaps group categories by color and location.

Treemaps group categories by color and location and show relative contribution by size of rectangle. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/07/02/introducing-new-and-modern-chart-types-now-available-in-office-2016-preview/

For more information on the Treemap and Sunburst charts check this Microsoft blog.

https://blogs.office.com/2015/08/11/breaking-down-hierarchical-data-with-treemap-and-sunburst-charts/

Excel 2016 Sunburst

The Sunburst is another chart that would be extremely difficult to create in Excel, if it weren’t built-in to Excel 2016. Imagine the Sunburst as a special type of Treemap.

Like the Treemap the Sunburst shows relative size of different categories with each category having a unique color. What the Sunburst does is arrange the relative sizes in rings around the core. The largest contributors are in the interior rings with smaller contributors farther from the center.

Like the Treemap, the Sunburst organizes by categories and size.

Like the Treemap, the Sunburst organizes by categories and size.

The Sunburst also groups categories, but larger contributors are near the core and smaller near the outer edge. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/07/02/introducing-new-and-modern-chart-types-now-available-in-office-2016-preview/

For more information on the Treemap and Sunburst charts check this Microsoft blog.

https://blogs.office.com/2015/08/11/breaking-down-hierarchical-data-with-treemap-and-sunburst-charts/

The One Click Forecasting Wizard

The FORECAST function has been extended to allow exponential smoothing. You can even do a one-click forecast by clicking the Forecast Sheet button on the Data tab  to quickly create a chart with visual forecast like the one above.

The Forecast Wizard will help you pick good forecasting attributes.

The Forecast Wizard will help you pick good forecasting attributes.

The Forecast Wizard will help you select the right forecast parameters. (Chart from Microsoft Office blog)

https://blogs.office.com/2015/08/27/whats-new-for-business-analytics-in-excel-2016/

Time Grouping Enhancements

One very helpful feature in Excel 2016 is Time Grouping. In PivotTables Excel automatically detects and groups the data in time-related fields such as Date(Year), Date(Quarter), and Date(Month). This enables you to quickly analyze by time segments without having to create custom time calculations. This feature also enables you to aggregate data larger than one million rows.
Automatically generated time groups save you time. (Chart from Microsoft Office blog.)

https://blogs.office.com/2015/10/13/time-grouping-enhancements-in-excel-2016/

Power Query is Built-in for Quick Access and Transformation to Corporate Data

In Excel 2013 you can access a myriad of data sources using Power Query, but first you had to install Power Query. Now, Power Query is built-in. On the Data tab click Get & Transform to access and transform just about every type of data source imaginable. You can access tables in public websites, corporate data found in major data source types, and even data in Hadoop and services like Salesforce.

https://blogs.office.com/2015/08/27/whats-new-for-business-analytics-in-excel-2016/

 

Share the power...

Leave a Comment:

Leave a Comment: