Charts

Excel Fundamentals

Chapter 8 · Charts: Choosing the Right Chart Type and Building One That Communicates

Chapter 6's data bars and colour scales gave a single column a lightweight visual read at a glance. A chart is the same underlying idea — translating numbers into something the eye can compare instantly — scaled up to an entire data set, drawn as its own object rather than squeezed inside individual cells. The hard part of charting was never really "how do I insert one" — it's choosing the chart type that actually matches the question you're asking, and this chapter is built around that decision first.

Choosing the Right Chart Type

Every chart type is built to answer a different kind of question. Picking the wrong one doesn't just look worse — it can genuinely make an accurate data set communicate the wrong conclusion.

Question being asked	Best chart type	Avoid when
Compare a value across categories	Bar / Column chart	There are so many categories the bars become too thin to read
Show a trend over time	Line chart	The x-axis isn't actually a continuous sequence (categories aren't ordered)
Show how parts make up a whole	Pie chart	There are more than ~5-6 categories, or you need to compare two different points in time
Show the relationship between two numeric variables	Scatter chart	One of the two variables is actually a category, not a number
Show how values are distributed across a range	Histogram	The data set is very small (a handful of points) — a histogram needs enough data to reveal a real shape

Building a Chart

Select your data — including its headers, since Excel uses them as series names and category labels — then go to Insert → Charts and pick a type, or press Alt+F1 for an instant default chart on the active worksheet. Excel's own Recommended Charts button (right next to the chart type icons) suggests chart types based on the shape of your selected data, and it's a genuinely useful starting point — but it's a suggestion, not a rule, and it can't tell whether you're trying to show a trend, a comparison, or a composition. The decision framework above should always win over whatever Excel guesses first.

The Anatomy of a Chart

Click a chart, then use the Chart Elements button (the small + icon beside a selected chart) to toggle each of these on or off:

Chart Title — should describe the actual finding, not just repeat the data's column names (e.g. "Sales Grew 40% in Q3," not just "Sales").
Axis Titles — near-mandatory on any chart with numeric axes; a chart with unlabeled axes forces the reader to guess what the numbers even represent.
Legend — necessary when a chart has multiple data series, but genuinely unnecessary clutter on a chart with only one — hide it rather than leaving a legend labelled with a single, redundant entry.
Data Labels — showing the exact value on or near each data point; useful when precise numbers matter, often skipped in favour of a cleaner look when the shape of the trend is the actual point.
Gridlines — help estimate a bar or line's value against the axis; easy to overdo, since too many gridlines compete visually with the data itself.

The Pie Chart Problem

Pie charts are simultaneously the most recognisable chart type and the most frequently misused. The human eye is measurably worse at comparing angles and areas precisely than it is at comparing the length of bars lined up against a shared axis — which is exactly what a bar chart offers instead. Once a pie chart has more than five or six slices, or several slices are close in size, readers genuinely cannot reliably tell which is bigger just by looking. "Exploded" or 3D pie variants make this worse, not better — the added visual depth distorts the perceived size of slices nearer the "front" of the illusion.

A pie chart's one genuine strength is showing a single snapshot's part-to-whole composition with very few categories (three or four is comfortable). The moment the real goal is comparing values precisely, or comparing composition across more than one snapshot (e.g. this quarter vs. last quarter), a bar chart communicates the same data far more reliably.

A chart is Chapter 6's data bar, scaled up to compare a whole data set at once

Chapter 6's in-cell data bars work because length is an easy, precise thing for the eye to compare — a full bar chart uses exactly the same principle across many categories at once. Whenever you're tempted to reach for a pie chart, ask whether a bar chart's length-based comparison would actually communicate the same finding more reliably — it almost always will.

Accidentally including a Table's Total Row in a chart selection

If Chapter 7's Total Row is switched on and you select the whole table (including that row) before inserting a chart, the total appears as its own bar or slice alongside the individual categories — visually dwarfing every real data point and making the actual category-to-category comparison the chart was meant to show almost unreadable. Always select just the category and value columns themselves, excluding any totals row, before building a chart.

Hands-On Exercises

Exercise 1

You have monthly revenue figures for the last 24 months, and you want to show whether revenue is trending upward, downward, or staying flat over that period. Which chart type from this chapter's decision table fits this question, and which chart type would you specifically avoid, and why?

📄 View solution

Exercise 2

A pie chart shows a company's expenses split across 9 categories, with several slices visually close in size. A colleague asks you to help them figure out which of the close-in-size categories is actually the largest. Explain why a pie chart makes this specific task difficult, and what chart type you'd rebuild it as to answer the question more reliably.

📄 View solution

Exercise 3

An Excel Table of quarterly sales by region has its Total Row switched on. Describe exactly what would go wrong if you selected the entire table (Total Row included) and inserted a column chart, and describe the correct selection to make instead.

📄 View solution

Chapter 8 Quick Reference

Comparison across categories → Bar/Column; trend over time → Line; part-to-whole → Pie (few categories only); two numeric variables → Scatter; distribution → Histogram
Alt+F1 inserts an instant default chart from the current selection
Recommended Charts is a useful starting suggestion, not a substitute for matching the chart type to the actual question
Always title a chart with its finding, not just its data's column name; always label numeric axes
Pie charts become unreliable past ~5-6 slices, or when slices are close in size — the eye compares length far more precisely than angle or area
Exclude a Table's Total Row from any chart selection — it visually dwarfs the real category values