Transform complex data into clear visual stories by mastering Excel charts like stacked column charts, tree maps, funnel charts, and donut charts. Learn to effectively visualize your information and enhance your data presentation skills.
Key Insights
- Stacked column charts visually consolidate multiple data categories like desktops, laptops, tablets, and licenses into a single column per month, clearly illustrating their proportions and reducing visual clutter.
- 100% stacked area charts present data as cumulative percentages over time, similar to filling a glass container with layers of sand, enabling easy identification of trends and changes from month to month.
- Alternative visualizations, including tree maps and funnel charts, effectively represent hierarchical data and descending order values, respectively, giving users a concise view of large datasets without unnecessary details.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Charts. Charts replace underlying data with easily understood visuals. In this section, we're going to take a look at information that's displayed in columns and rows, and we're going to turn them into charts.
So the first type of chart we're looking at is the stacked column chart. Now, instead of displaying the bars as four individual colored bars in a column, which is normally what you would see with a column chart, I'll go over to Insert. I'll click the dropdown here.
The nice thing about Windows, as opposed to Mac, is if I hover my mouse over a chart, I can get a preview of what that chart looks like. Now, this is normally how we would see the chart in a clustered column chart. But if I wanted to do a stacked column chart, not 100%, but a stacked column chart, well, now the information would look like this.
I take up less space, but people can clearly see where desktops, laptops, tablets, and licenses take up space within a certain month. And so that is a stacked column chart. A 100% stacked column chart fills the entire area.
If I select this information, I'm going to go to Insert. I can also choose Recommended Charts, and I can take a look if there are certain recommendations based on the data I selected that Microsoft recommends. And one of those recommendations is a stacked area chart.
We have a stacked column chart, but I don't see the one that we're supposed to choose, which is a 100% stacked area chart. So I'm going to go over here, and I'm going to choose Area. And again, I can hover my mouse over the different, or I can click here.
Here I can click. This is a 100% stacked area chart. I'll click OK.
The information here is displayed as if you took sand and you filled up a glass container all the way to the top. You can clearly see, as you transition from January to February, March, and April, where the values get larger or smaller. So this is a nice visual representation of the data where you're seeing everything, 100% of the information and how it breaks down.
All right, so, and one of the ways you can tell that, by the way, which may or may not be obvious to you, is over on the left in the y-axis, the values go from 0% to 100% as opposed to just showing values. Let's try the tree map and the funnel charts. Top row, hold down Control, and then choose the bottom row.
Well, actually, I don't need the months, so I'm just going to select Desktops to Licenses. I'm going to hold down Control, and then I'll select the values here. Now I'll go to Insert, Recommended Charts.
I can go to All Charts, and now I'll choose the tree map, and I'll click OK. Okay, so here is a better representation of the overall data. This is the entire pie, and this gives you a visual representation of January, February, March, and April, the first four months.
Now let's take a look at a funnel. Funnel is very similar. You just want to select the titles and the totals, then go to Insert.
I'll click on Recommended Charts. I'll go to All Charts, and I'm looking for the funnel chart, which is down here. I'll click OK.
The largest value starts on top, and then you can clearly see the information funnel down to the lowest value. So for these types of charts, they're just like a pie chart. You just want the values and the totals, excluding any of the intermediate information.
This is an interesting version of a donut chart that incorporates all the information. This is one way to look at it. It might not be as clear as if you had created maybe four different charts that represent each plant for units produced and payroll hours.
If I go over here, I'll select these first two. I'll go to Insert, and then I'll go over to the pie chart. I'll choose the first one.
There we go. That is plant one. I'll go to Quick Layout.
I'll choose the first one. I'll go to Add Chart Element. I'll choose Data Labels, and I'll choose Outside End, and that is a clear display of the values for plant one, units produced versus payroll hours.
So I can now resize this, and if I want to bring in plant two, all I need to do is hold down Control, click on the first chart, and drag it. I'll let go of the mouse first, and now I have a duplicate. The only thing I need to do with this is change the range that's being selected.
So I'm going to go over here. I'm going to move this down over to plant two, and there's plant two. I'll do the same thing here.
Copy. Let go of the mouse first. Still holding down Control, click and drag.
Let go of the mouse, and then now all I have to do is change the rows. I'm going to drag this over to three. I'm going to go over here, and I'm going to drag this over to four.
This may be a better way to look at the information. It's not as cluttered. You're still seeing the percentages, but for the individual plants.
So this is a small example of how you can visualize your data. There are many more charts you can pick from. Earlier on, we looked at the Box and Whisker, which is a relatively new chart for analyzing our data back when we were working with measures of position, and here are just some others.
All right. So this covers charts for visualizing your data. Thank you for watching.