New Charts in Excel 2016

With Office 2016 officially launching on September 22, 2015 I thought I’d give you a sneak peek at a few of the new charts in Excel 2016.

Treemap Chart

A Treemap chart is an area based visualisation that displays your data in a hierarchical pattern.

The tree branches are represented by rectangles, with sub-branches displayed as a smaller rectangle.

The size of the rectangle represents the quantity measure and the colour of the boxes can be used to create associations in the data, or a colour scale can indicate performance comparisons in the data.

Treemaps are good for visualising a lot of data with multiple levels or groupings, i.e. hierarchical data.

The chart above enables you to quickly see that Red Delicious apples have the most stock and the sub-branches enable you to see the different ages of the stock. It is based on the following data:

Alternatively you could arrange the data by age, which might make more sense for monitoring fruit stocks, and use the colours to create associations between the different types of apples like this:

Treemap Cautions

• In my opinion Treemaps could quickly become the pie chart of Data Visualisation if they’re used purely for the purpose of having some eye candy in your report.
• Careful labelling of these charts is required for them to be useful in decision making.
• Comparisons in the data are tricky because there isn’t a common baseline for all categories.
• Unfortunately the colour encoding must be done manually so this chart still needs some development before it meets all the requirements of a Treemap.

Waterfall Chart

Waterfall charts, sometimes called Bridge Charts, show the breakdown from a starting value to the end result. They are often used to visualise financial statements by illustrating the cumulative effect of income and expenses to get to the final figure.

Learn how to create Office 365 Excel Waterfall Charts here, including a workaround for earlier versions of Excel.

Pareto Charts

We’ve always been able to build a Pareto chart in Excel by combining a column and line chart, but in Excel 2016 it’s built in so there’s a little less effort required to create it. Plus there are some settings for the bins so you don’t need to collate the data into bins first:

Box and Whisker Chart

Box-and-whisker plots are a great way to display data broken into four quartiles, each with an equal number of data values.

The box shows where the middle of the data lies and the whiskers show the smallest and largest values in the data set, which are sometimes also outliers.

It's a nice chart to use when analysing how your data is skewed.

Sunburst

I’ve left this one till last because it’s my least favourite. Sure it looks pretty, but so do pie charts. It doesn’t mean they are always useful!

Clearly there is too much data in this example as you can only read the labels on the largest segments, but then maybe that’s all you’re interested in.

Is Excel 2016 a Pipedream?

If the thought of upgrading to Excel 2016 is a pipedream for you and you want to be able to create Waterfall charts, Box and Whisker charts and more then you needn’t wait. Check out these Excel add-ins:

Charting Collection by add-ins.com – use coupon code MOTH to get 10% off.

Peltier Tech Chart Utility – available for PC and Mac:

Disclosure: If you purchase the Excel chart add-ins I earn a few dollars, but that's not why I recommend them. I do so because they're excellent tools.

Thanks

Special thanks to Shane Devonshire for providing the Olympics data for the sunburst chart.