6 New Excel Charts and How to Use Them
Pinterest Stumbleupon Whatsapp
Advertisement

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 Improve Your PowerPoint Presentation with Excel Data Visualizations Improve Your PowerPoint Presentation with Excel Data Visualizations Nothing makes information more vivid than a great visualization. We show you how to prepare your data in Excel and import the charts into PowerPoint for a lively presentation. Read More 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.

1. Histograms

A histogram is a relatively simple kind of chart that represents the frequency of a range of values across a data set.

histogram data excel

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.

histogram insert excel

You should see a chart that’s something like the one below.

histogram chart excel

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.

histogram format axis excel

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.

histogram number of bins excel

This makes my chart look like this.

histogram chart amended excel

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.

histogram chart final excel

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.

pareto data excel

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.

pareto insert excel

This produces a chart that looks like this.

pareto chart excel

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.

pareto final excel

If you’re wondering how you can change the look and feel of your graphs, read our guide on formatting Excel charts 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions matter. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know about making your charts attractive and engaging in Excel 2016. Read More .

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.

box whisker player ages excel

This is only a small portion of the data — box and whisker charts tend to work best when you have a lot of information Search Excel Spreadsheets Faster: Replace VLOOKUP With INDEX and MATCH Search Excel Spreadsheets Faster: Replace VLOOKUP With INDEX and MATCH Still using VLOOKUP to search for information in your spreadsheet? Here's how INDEX and MATCH can provide a better solution. Read More to display.

Select all your data and navigate to Insert > Insert Statistic Chart > Box and Whisker.

box whisker excel

As you can see below, our chart looks OK, but there are a few tweaks we can make to present our data more efficiently.

box whisker chart v1 excel

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.

box whisker minimum bound excel

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.

box whisker chart v2 excel

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.

box whisker chart v3 excel

4. Treemaps

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.

treemaps data excel

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.

treemaps data selection excel

Next, open the Insert tab. Find the Insert Hierarchy Chart dropdown in the Charts section, and click Treemap.

treemaps insert excel

This will produce a chart like the one below.

treemaps chart excel

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.

treemaps final excel

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.

sunburst chart data excel

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.

sunburst chart insert excel

You should see something like this appear.

sunburst chart excel

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.

sunburst final excel

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.

waterfall data excel

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.

waterfall chart excel

However, the chart above might not be the best way to present our data Google's New Tool Turns Data Into Professional Animated GIFs Google's New Tool Turns Data Into Professional Animated GIFs Google's latest data analysis tool makes it easy to visualize raw data in a professional, animated way -- and it's even easy to share with others. Read More . 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.

waterfall format data point excel

Doing this for both bars results in the chart below.

waterfall chart amended excel

Now we can see the effect that the decreases have on our running total. We’re now ready to make any desired visual edits.

waterfall final excel

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 8 Types of Excel Charts & When You Should Use Them 8 Types of Excel Charts & When You Should Use Them Graphics are easier to grasp than text and numbers. Charts are a great way to visualize numbers. We show you how to create charts in Microsoft Excel and when to best use what kind. Read More .

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 10 Easy Excel Timesavers You Might Have Forgotten 10 Easy Excel Timesavers You Might Have Forgotten These ten tips are sure to minimize your time performing menial Excel tasks and boost your spreadsheet productivity. Read More , 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

Leave a Reply

Your email address will not be published. Required fields are marked *