Here’s a one-stop guide to some of the new charts introduced in the latest version of Excel.
Charts are an easy way to make a dry, dull spreadsheet a little more engaging. However, familiar pie charts and scatter graphs can only go so far.
There’s a whole world of data visualization out there, and Excel 2016 introduces a few new types of charts for users to utilize. They’re all pretty handy — under the right circumstances — so it’s well worth knowing when to employ these recent additions.
With a working knowledge of these charts, you’ll have even more tools in your arsenal to make sure your data analysis hits its mark, every single time.
A histogram is a relatively simple kind of chart that represents the frequency of a range of values across a data set.
We only need one column of data. Excel will group this into sensible bins, but we can adjust the boundaries manually later on. Select all your data and head to the Insert tab, then find the Charts section. Select Insert Statistic Chart > Histogram.
You should see a chart that’s something like the one below.
Our data has been sorted into three bins: between 4.5 and 6.3 hours of sleep, 6.3 and 8.1 hours of sleep, and 8.1 and 9.9 hours of sleep. This is fine, but I would prefer to fine-tune things for myself. To do so, I need to right-click the X-axis and click Format Axis.
There are several different ways to adjust the bins, and the right option will depend on your data. I’m going to specify that I want five columns by selecting Number of bins in the Format Axis menu and entering 5 into the accompanying input field.
This makes my chart look like this.
I think that’s a better representation of the information. Now all that’s left to do is to make any necessary changes to the design of the chart.
2. Pareto Charts
A Pareto chart isn’t too different from a histogram. However, columns are always sorted in descending order, and they’re accompanied by a line that tracks their cumulative total. This makes it particularly useful in the context of quality control.
Here’s the data that I’m using. It’s fairly self-explanatory. To create a Pareto chart, we need to select all the data and head to the Charts section of the Insert tab. Then, select Pareto.
This produces a chart that looks like this.
As you can see, our columns are in descending order, but the line tracks a running total. This allows us to see that if we eliminated the problems of forgetting about the fruit, storing it incorrectly, and buying bad fruit at the store, we could eliminate 90 percent of wastage. All that’s left to do is to make our visualization look a little more appealing.
If you’re wondering how you can change the look and feel of your graphs, read our guide on formatting Excel charts.
3. Box and Whisker Charts
Box and whisker charts are useful when you want to show the spread of a particular set of data. It’s a good way to compare the range of different sets, while picking out useful details like the mean and median.
I’m going to use data relating to the ages of the current active roster for three Philadelphia sports teams. I’ve already set up a spreadsheet that lists the ages of various different players alongside their chosen sport.
This is only a small portion of the data — box and whisker charts tend to work best when you have a lot of information to display.
Select all your data and navigate to Insert > Insert Statistic Chart > Box and Whisker.
As you can see below, our chart looks OK, but there are a few tweaks we can make to present our data more efficiently.
For instance, there’s a lot of dead space toward the bottom of our chart. We can amend this by right-clicking the Y-axes and selecting Format Axis.
I’ve chosen to set the minimum bound to 15 and the maximum bound to 40. This uses the space more efficiently, which allows the chart to offer a more perceptible comparison.
This version of our chart makes it easier to pick up on details like the basketball team having the smallest range of ages, or the baseball team having the most extreme outliers. This is a good time to make any aesthetic tweaks to your chart.
Treemaps are useful when you want to see a proportional overview of a particular data set. For this example, I’ve prepared some sales records for a sandwich shop.
As you can see, all of my products are divided into subcategories; sales on the weekend and on weekdays, and whether they’re classified as a sandwich or a side order. By doing this, we’re telling Excel how we want our chart to be organized.
First, we need to select our data. We could create a visualization of the price of each item, but it would perhaps be more useful to look at our overall profits, so we’re going to hold down Ctrl while we drag our cursor so that we only select columns A, B, C, and F.
Next, open the Insert tab. Find the Insert Hierarchy Chart dropdown in the Charts section, and click Treemap.
This will produce a chart like the one below.
As you can see, our profits have been laid out in the subcategories we dictated. This makes it easy to see whether weekdays are more profitable than weekends, whether sandwiches provide a better return than side orders, and which sandwiches are most popular, all from the same chart.
Again, now is the time to make any aesthetic tweaks to your treemap.
5. Sunburst Charts
Sunburst charts are also good at comparing sets of data, but they represent proportions in a slightly different way. They’re a bit like stacking several different pie charts on top of one another.
Above is the data we’re going to use for our chart. It’s a record of how much rainfall there was in the city of Edinburgh over the course of 2015, arranged in quarters, months, and weeks. It doesn’t matter that we only have the weekly data for February — Excel will present this information as a monthly total and as a weekly breakdown.
Select all your data and head to the Insert tab. From the Charts section, click the Insert Hierarchy Chart dropdown and select Sunburst chart.
You should see something like this appear.
The inner ring lets you compare quarters, while the middle ring provides the proportional values of each month. As you can see for the February section, there’s an added section of the ring that offers up our week-by-week breakdown.
Now it’s time to get our chart ready to publish.
6. Waterfall Charts
Waterfall charts are great when you want to track a running total. As your total increases or decreases, bars will represent the difference, which makes this type of chart particularly useful for financial visualizations.
There are a couple of things to note about this data. You need to stipulate your losses as negative integers, which you can do by surrounding them with a bracket. However, if you want to make your data easy to edit by linking the cells together (e.g. setting up Wages less Rent to equal the cell containing Wages minus the cell containing Rent), remember that you’ll need to add the cells, rather than subtract, because we’re working with negative figures.
Once you’ve set up your data, select it and head to the Insert tab. Navigate to the Charts section and use the Insert Waterfall or Stock chart drop-down to select the Waterfall chart.
However, the chart above might not be the best way to present our data. At the moment, our Wages less Rent and Wages less Rent and Food bars are being treated as increases, when really they’re the new totals. They should be touching the baseline.
We can amend this by double-clicking on each series and using the contextual options that appear. In the Format Data Point menu, check the box marked Set as Total.
Doing this for both bars results in the chart below.
Now we can see the effect that the decreases have on our running total. We’re now ready to make any desired visual edits.
Know Your Excel Charts!
Excel makes it easy to turn your data into slick visualizations. However, it’s important that you choose your charts wisely.
A Pareto chart serves a very different purpose to a sunburst chart, and a waterfall chart has very different applications to a box and whisker chart. If you try to use them interchangeably based on what looks good, you’ll quickly find that Excel struggles to turn your data into anything useful.
Always consider your data first, then pick a chart. Excel can do the legwork for you, but you need to make the decisions.
Do you have a tip for other users looking to improve their visualizations? Or are you in need of assistance with setting up a particular type of chart? Either way, why not join the conversation in the comments section below?
Image Credit: Nomadic Photo Studio via Shutterstock.com