The Office 2016 Public Preview is now available for download! Included in the preview of Excel 2016 are a handful of new chart types and since I’m a huge fan of awesome data visualizations, I thought I’d take a few moments to play around with them and share my experience with you so you can have a better idea of what to expect in the next version of Excel. But to be honest, if you’re a data & visualizations nerd like me, you’re probably pretty excited!
Now one thing to be aware of with these new chart types is that if you attempt to create this chart on top of some data in a pivot table, you’ll get a warning like the one seen here. In order to use these charts, you’ll need to create them on top of data that is not in a pivot table (at least for the time being).
Box and Whisker Chart
The Box & Whisker chart is a really nice visualization for getting a quick look at the distribution of data including outliers, mean, range and quartiles, for example. In the below chart, I pulled in some data from NFL.com with Power Query and performed some analysis on the yards per game for the top four running backs from last season.
You also have control over the chart formatting through some options specific to the Box & Whisker chart type.
The Waterfall Chart was just added to Power BI so you’ve probably already had a look at that visualization. The neat thing about Waterfall chart is that it allows us to see how the small pieces of a whole contribute to the total. For instance, below I have a Waterfall chart that shows the play stats from the New England Patriots first drive in their conference championship game against the Indianapolis Colts which resulted in a touchdown. Using this chart I can see how each play in the drive led to the total yards gained on the play. Pretty cool!
A Waterfall chart could be really useful for monitoring changes in inventory or for viewing balance sheet data.
The Sunburst chart is good for viewing hierarchical data. So if you wanted to view how individual accounts contribute to their parent accounts in a balance sheet, the Sunburst chart could be a really interesting way to visualize that type of data.
Here I’m using the Sunburst chart to analyze the receivers of a few different teams by player and position.
If you’ve been playing around with Power BI like me, you’ve already seen the Treemap chart. The Treemap chart is kind of like a pie chart except, in my opinion, is sometimes easier to use since its supposedly easier to compare squares and rectangles in a Treemap than it is slices in a pie or donut chart. But you’ll still need to be careful with the Treemap that you can actually see the differences between the rectangles as you can see below.
But if I only use two categories, it’s a little easier to gauge quickly which player had more rushing yards.
The Histogram chart is another visualization used to observe data distribution. Using the Histogram chart I can quickly observe the distribution of the yards per game for each running back in order to make the best decision in my upcoming fantasy draft. I’m not taking any chances!
The Pareto Chart is very similar to the Histogram chart because both charts display the number of occurrences for each category via the column chart on the left vertical axis. But with the Pareto chart, we also get to see a Pareto line on the right vertical axis which indicates how much each category contributes to the total.
By looking at the Pareto chart, I can see which players are the most consistent in their yardage totals or are more likely to hit a long run. If the chart showed frequent short runs with only one or two long runs which contributed to the majority of their yards gained, I would probably steer clear of that player since their success last season was due to a couple “lucky” breaks.
My Thoughts on the New Excel Charts
I really like the new chart types on the whole. I think the most useful chart types that I can see being very beneficial is the Histogram, Pareto, Waterfall and Box & Whisker charts.
I don’t think I will see myself using the Sunburst and Treemap chart all that much simply because it can be harder to analyze the differences in sizes between the pieces. In my opinion there are other visualizations that can do what these visualization types can do except better, such as the trusty bar and column charts.
Also, I’m not sure how much I’ll get to use any of the new visualizations since most of the data I work with is in databases at this point. These visualizations don’t work with pivot tables yet so that does create a bit of a problem for me. Now this is the public preview of Excel 2016 so in the final release of Excel 2016 this ability may be there but I’m not aware if it is included.
I’m really loving the investments made in Power BI and self service BI with the new data visualizations! I think this is a great move by Microsoft in putting the power in the hands of the users with the tools their already familiar with. So what do you think of the new chart types in Excel 2016? Leave a comment below and lets discuss!