Sign in or start a free trial to avail of this feature.
10. Selecting the Correct Chart
The chart you choose to use depends entirely on the data you wish to chart. In this lesson, learn which charts are the best to use for a number of different datasets.
To explore more Kubicle data literacy subjects, please refer to our full library.
Stacked Column Chart (00:04)
The stacked column chart is most commonly used when you want to break some number down into its components. Revenue and profit would be common examples, where a stacked column chart could be used to analyze these figures by region, product or similar fields. Often, the horizontal axis of a chart like this will be time based.
Clustered Column Chart (00:40)
Clustered column charts are used to compare metrics that cannot be added together. For example, this could be used to analyze revenue and profit together. It could also be used to analyze revenue or profit for several different competitors.
100% Stacked Column Chart (01:08)
The 100% stacked column chart is useful when analyzing percentages and proportions, such as for market share. It’s particularly useful when analyzing these percentages over several years. If you have just one year, a pie chart can be a useful alternative.
Bar Chart and Tornado Chart (01:36)
The bar chart is a horizontal version of the column chart, and it’s most effective when neither axis is time based. A variant of the bar chart is the tornado chart, which combines a chart of positive bars, and a chart of negative bars, to create a tornado-like appearance.
Line Chart (02:10)
Line charts are best used on large datasets containing many points, such as plotting stock or commodity prices. Line charts almost always use time as the horizontal axis.
Now that you know how to create charts, it's important that you select the right chart for your data. In this lesson, we'll identify the appropriate chart to use for several different data sets.
Let's start with a stacked column chart, which we've used a lot in this course.
The stacked column chart is ideal when you want to break down a figure such as revenue into its discrete components. I tend to use the stacked column chart a lot for revenue and profit charts, separating these figures by product, geographic region, or even salesperson.
Next up is the clustered column chart.
This chart works well when we want to compare certain metrics that are not typically summed together. For example, the clustered column chart works well if we want to show revenue and gross profit for each year.
The numbers would make no sense if stacked on top of each other.
I also use a clustered column chart when comparing competitors along specific metrics.
Next up is the 100% stacked column chart.
This chart is very useful when we're interested in the relative percentage proportion rather than the absolute numbers.
A classic case for using this chart is when you're trying to understand market share. Pie charts also work well for this type of analysis, but I find the 100% stacked column chart easier to read, particularly if I want to include multiple years on the same chart.
The bar chart, on the other hand, is a simple horizontal version of the column chart. If the axis is not time-based, then I may use a bar chart.
For example, here we're comparing salesperson performance.
Bar charts are particularly useful if you want to create a tornado chart when the positive and negative values can create the tornado effect.
This tornado chart shows newly acquired customers on the positive side and lost customers on the negative side for each salesperson.
Next up is the line chart.
Line charts should be used for large data sets. Common uses of line charts are when plotting stock prices or commodity prices over time. In fact, time is almost always on the horizontal axis on a line chart.
The charts we've covered here should satisfy almost all of your charting needs. It's worth exploring the other charts available in Excel by going to Insert and checking out the scatter, area, surface, bubble, and donut charts. I find that many of these charts, particularly the surface and the radar charts, are quite hard to read. In many cases, sticking to column charts, bar charts, and line charts is often the best option.