New graphs in Excel 2016

aat comment

Much like waiting for a bus, new charts in Microsoft take a long time to materialise.

In fact there have been no new charts in Excel since the ’97 version but 2016 has seen the release of six new charts for you to sink your teeth into.

Histograms and Pareto charts

Histograms show you frequencies, for example how much of one product has sold against another. They’re not based on categories but on values which are allocated into different bins. Although if you want to alter this to show categories you can do that. Pareto charts go one step further by sorting these frequencies and adding a cumulative percentage line to give you a trend through the data.

Sunburst charts

These show values by hierarchy. A good use for sunburst charts would be to analyse sales of a company and break it down by salesperson, customers and products purchased. The chart will plot the sales people at the highest level and size their sections by the sales they have made. The next level will be the customers each sales person has and the total amount they have spent with their respective sales person and the final level would show the products bought by each customer. Sunburst charts allow you to go to dozens of levels so you can really drill down into your data.

Tree maps

Tree maps also show values via hierarchy but unlike sunburst charts, they display the data in boxes instead of concentric circles. It is also restricted to only two levels so they are much more limited than sunburst charts.

If you’re not a statistician you’ll probably not have used one of these. It shows you the various statistical breakdowns of a group of data such as median values, marking the position of quartiles and also highlighting outliers.

Waterfall charts

Waterfall charts are great for showing movement from an opening position to a closing position and are therefore ideal for plotting financial data such as cash flow over a period of time. They show your opening balance and the progression through the various stages of trading you may have and then stop at your closing balance. Useful for business of all sizes.

3D data maps

Perhaps the most impressive looking of all the new visualisations in Excel 2016, the 3D data map, was actually an add-in in the 2013 version but is now a fully integrated option. This chart is perfect for analysing data with a global span, for example a company sales report where the company operates with a global client base. Obviously you need locational data to use this type of chart and you can then add other fields to this to build your picture. The chart will give you a map of the world with bars or columns in the locations that have fields attached to them. What’s more, if you set this against a particular period of time the chart will allow you to record a simulation video showing the change in values over that period. Great for presenting sales growth to the board or your team.

Alan Gurney is AAT Comment’s Excel tips writer.

Related articles