Every spreadsheet program offers dozens of chart types, and the temptation is to pick the one that looks most appealing. But a chart exists to answer a question, and each type suits different questions. Choosing well means the viewer gets the message in a few seconds; choosing badly means forcing them to decode it or, worse, leading them to the wrong conclusion.
Start from the question
Before inserting a chart, ask yourself what it has to show. The most common questions are four:
| Question | Suitable chart type |
|---|---|
| How has a value changed over time? | lines |
| Which category is largest? | bars or columns |
| What parts make up a total? | stacked bars, sometimes a pie |
| Is there a relationship between two quantities? | scatter |
Trends over time: the line chart
For monthly sales, daily temperatures, weekly visits to a website, the line is the natural choice: the eye follows the direction and immediately picks up growth, decline and seasonality. A few rules:
- the horizontal axis must represent time in order and at regular intervals;
- if the data has gaps (a month with no reading), decide deliberately whether to show a gap or join the points; the options for hidden and empty cells let you choose;
- more than four or five overlapping lines become unreadable: consider separate charts side by side with the same scale.
Columns can work for time when the periods are few and discrete, such as the twelve months of a year, but lines remain more effective at showing a trend.
Comparing categories: bars and columns
To compare agents, products or regions, the bar chart is the most readable, because the eye is very good at comparing lengths aligned on a common baseline.
- Sort the categories by value, from largest to smallest, unless they have a natural order (age bands, days of the week). A sorted chart is read in an instant.
- Horizontal bars when the labels are long: product names stay readable without being rotated.
- The value axis must start at zero. In bars, length represents value: if the axis starts at 90, a difference between 92 and 96 looks like a doubling.
That last point is the most important in this whole article. Spreadsheets sometimes pick a minimum other than zero automatically: always check the axis options.
Composition: stacks and pies
To show how a total is made up, stacked bars display the total and its parts together. They work well with a few components; beyond four or five, the middle segments become hard to compare, because they do not start from a common baseline.
The pie chart has a bad reputation, partly deserved. The human eye is poor at comparing angles and areas, so telling a 22% slice from a 26% one is almost impossible. A pie can be fine when:
- there are few parts, two or three;
- the message is simple, such as "almost half of revenue comes from a single customer";
- the parts really add up to a meaningful total.
Avoid three-dimensional pies: perspective enlarges the slices in front and distorts the comparison.
Relationships: the scatter plot
To see whether two quantities are related, for example price and quantity sold, or hours of training and number of errors, the scatter plot shows each observation as a point. If the points form an elongated cloud, there is a relationship; if they are scattered, probably not.
You can add a trendline from the chart options, but remember that a visible relationship does not prove that one quantity causes the other.
A common mistake is to use a line chart for this kind of data: the line joins the points in row order, which usually means nothing.
Two vertical axes: with caution
A combo chart with a secondary axis, for example revenue as columns and percentage margin as a line, can be useful, but it is easy to misread: viewers compare the height of the line with that of the columns, even though the scales differ. If you use one, label both axes clearly and use colours that tie each series to its own axis.
Clean-up and readability
- A title that states the message, not just the topic: "Sales in the South doubled in 2025" is more useful than "Sales by area".
- Remove what is not needed: dense gridlines, borders, shadow effects, legends when there is only one series.
- Data labels only where they help, for example on the last value of a line.
- Colours with a meaning: a neutral colour for everything and one bright colour for the series you want to draw attention to. Do not rely on red-green contrast alone.
In my work, before handing over a chart, I show it for five seconds to someone who does not know the data and ask what they understood. If the answer does not match the title, the chart needs redoing.
In short
- Choose the chart according to the question: time, comparison, composition or relationship.
- Lines for trends, sorted bars for comparisons, scatter for relationships.
- In bar charts the value axis must start at zero.
- Use a pie only with a few parts and a simple message, never in 3D.
- A title that states the message and a few meaningful colours make the chart readable.
