Here's a complete 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 attractive. However, familiar pie charts and scatter plots can only go so far.
There's a Whole World of Data Visualization Enhance Your PowerPoint Presentation With Excel Data Visualizations Enhance 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 an animated presentation. Read More They're all pretty handy, under the right circumstances, so it pays to know 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 your mark, every time.
A histogram is a relatively simple type of graph that represents the frequency of a range of values in a data set.
We only need one column of data. Excel will group this into sensible bins, but we can adjust the limits manually later. Select all your data and go to the Insert tab, then find the Letters section. Select Insert statistical graph> Histogram .
You should see a graph that looks like the one below.
Our data has been classified 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'd rather tune things up myself. To do so, I need to right click on the X axis and click Format Axis.
There are several different ways to adjust the trays, and the correct option will depend on your data. I'm going to specify that I want five columns by selecting Number of bins in the Axis format Menu and entering 5 in the accompanying input field.
This makes my letter look like this.
I think it's a better representation of the information. Now all that's left to do is make the necessary changes to the chart layout.
A Pareto chart is not very different from a histogram. However, the columns are always sorted in descending order and are accompanied by a line that tracks your running total. This makes it particularly useful in the context of quality control.
Here is the data I am using. It's pretty self explanatory. To create a Pareto chart, we must select all the data and go to the Cards Insert section tongue. Then select Pareto .
This produces a graph 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 eliminate the problems of forgetting fruit, storing it incorrectly, and buying bad fruit at the store, we could eliminate 90 percent of waste. All that's left to do is make our visualization look a bit more attractive.
If you're wondering how you can change the appearance of your charts, read our guide on how to format Excel charts. 9 Tips for Formatting an Excel Chart in Microsoft Office. 9 Tips for Formatting an Excel Chart in Microsoft Office. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know to make your charts beautiful and engaging in Excel 2016. Read More.
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 am going to use the data related to the ages of the current active roster of three Philadelphia sports teams. I've already set up a spreadsheet that lists the ages of several different players along with their chosen sport.
This is only a small part 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 Keep using VLOOKUP To find information in your spreadsheet? Here is how INDEX and MATCH can provide a better solution. Read more to show.
Select all your data and navigate to Insert> Insert statistical graph> Box and mustache .
As you can see below, our graph looks fine, but there are some adjustments we can make to present our data more efficiently.
For example, there is a lot of dead space towards the bottom of our graph. We can fix this by right-clicking on the Y-axes and selecting Axis Format .
I've chosen to set the min limit to 15 and the max limit to 40. This uses space more efficiently, allowing the chart to provide a more noticeable comparison.
This version of our table makes it easier to capture details like the basketball team that has the smallest range of ages or the baseball team that has the most extreme outliers. This is a good time to make any aesthetic adjustments to your letter.
Roadmaps 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 my products are divided into subcategories; weekend and weekday sales, and whether they are classified as a sandwich or side order. By doing this, we're telling Excel how we want our chart to be arranged.
First, we need to select our data. We could create a visualization of the price of each item, but perhaps it would be more useful to look at our overall earnings, so we're going to press Ctrl As we drag our cursor so that we only select columns. ONE , second , do , and F .
Then open the Insert tongue. Find the Insert Hierarchy Table dropdown in the Graphics section, and click Treemap.
This will produce a table like the one below.
As you can see, our benefits have been laid out in the subcategories that we dictate. This makes it easy to see if weekdays are more profitable than weekends, if sandwiches outperform secondary orders, and which are the most popular, all from the same table.
Again, now is the time to make any cosmetic changes to your treemap.
Sunburst charts are also good for comparing data sets, but they represent ratios in a slightly different way. They're a bit like stacking several different pie charts on top of each other.
Above is the data that we are going to use for our graph. It is a record of the amount of rain that occurred in the city of Edinburgh throughout 2015, organized in quarters, months and weeks. It doesn't matter that we only have weekly data for February:Excel will present this information as a monthly total and weekly breakdown.
Select all your data and go to the Insert tongue. In the Charts section, click Insert Hierarchy Table drop-down menu and select Sunray Chart .
You should see something like this appear.
The inner ring allows you to compare quarters, while the center ring provides the proportional values for each month. As you can see in the February section, there is an additional ring section that offers our week-by-week breakdown.
Now it's time to prepare our chart for publishing.
Waterfall charts are great when you want to keep track of a running total. As your total increases or decreases, the bars will represent the difference, making this type of chart particularly useful for financial visualizations.
There are a couple of things to note about this data. You must stipulate your losses as negative integers, which you can do by surrounding them with a square bracket. However, if you want to make it easier to edit your data by linking the cells (e.g., Set Wages Minus Rent to equal the cell containing Wages minus the cell containing Rent), remember to add the cells, in instead of Subtraction, because we are working with negative numbers..
Once you have configured your data, select it and go to Insert tongue. Go to the Graphics section and use the Insert Cascade or Stock dropdown menu to select the Waterfall Box .
However, the above table 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 analytics 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 this time, our Salary minus Rent and Salary minus Rent and Food bars are treated as increases, when they are actually the new totals. They should be touching the baseline.
We can correct this by double-clicking on each series and using the contextual options that appear. In the Data Point Format Menu, check the box marked Set as total .
Do this for the results of both bars in the next frame.
Now we can see the effect the decreases have on our running total. We are now ready to do any desired visual editing.
Excel makes it easy to turn your data into elegant visualizations. However, it is important that you choose your graphics wisely. 8 Types of Excel Charts and Graphs and When to Use Them 8 Types of Excel Charts and Graphs and When to Use Them Charts are easier to understand 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 which type. Read more.
A Pareto chart has a very different purpose than a sunburst chart, and a waterfall chart has very different applications than 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 something useful.
Always consider your data first, then choose a chart. Excel can do the work for you. 10 Easy Excel Times Savers You May Have Forgotten 10 Easy Excel Times Savers You Might Have Forgotten. These ten tips will help you minimize your time on small Excel tasks and increase the productivity of your spreadsheet. Read More
Do you have any tips for other users looking to improve their visualizations? Or do you need help setting up a particular type of chart? Either way, why not join the conversation in the comments section below?
