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.
Stacked column charts
- Used when a metric such as revenue needs to be separated into components
- Common uses include plotting revenue by region or by product type
- The horizontal axis is often a year or a month but not always so
Clustered column charts
- Used when multiple metrics need to be compared
- Comparing revenue or profits against competitors is a common use case for this chart
- If you have more than 3 columns in a cluster, the chart can be become difficult to read
100% stacked column charts
- Used when we want to examine relative proportion rather than absolute values
- Plotting market share is a common use case for this chart
- Pie charts serve a similar purpose but cannot easily show changes over time
- Horizontal charts that can either be clustered or stacked
- Not suited to data which has time on the horizontal axis
- However, can be easier to read than a column chart in other use cases
- Used primarily when we have to plot a large dataset
- Not particularly useful for stacked data but can be used to compare against competitors
- Plotting commodity prices or stock prices is a common use case for this chart
Now that you know how to create charts, it's important that you select the right chart for your data. In this lesson, I'm going to run through a couple of typical datasets that you may need to plot on a chart, and show you which chart options are best. Let's start with the 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, by geographic region, or even by 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, if we wanted to show revenue and gross profit for each year, the clustered column chart works well in this case, because the numbers would make no sense if stacked on top of each other.
I also use the clustered column chart when comparing competitors along particular 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 chart, 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 simply a horizontal version of the column chart. If my axis is not time-based, for example, here, I'm comparing salesperson performance, then I may use a bar chart, which can be easier to read. Bar charts are particularly useful if you want to create a tornado chart, when the positive and negatives can create the tornado effect.
Here, I used the tornado chart to show 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 datasets. Common uses of line charts are when plotting stock prices, or commodity prices over time. In fact, time is almost always the horizontal axis on a line chart. These charts I've shown you should satisfy almost all of your charting needs. It's worth exploring the other charts available in Excel, by going to Insert, checking out the scatter, the area, and the other charts such as the surface, bubble, and donut. I find that many of these charts, particularly the surface and the radar charts, are quite hard to read. And in fact, sticking to the column charts, bar charts, and line charts, is often the best option.