Charts
A good chart makes the point of a table obvious at a glance. Excel can draw one in seconds; the skill is choosing the right one.
Choosing a chart
| You want to show | Use |
|---|---|
| a comparison between categories | a column chart, or a bar chart for long labels |
| a change over time | a line chart |
| parts of one whole, with few parts | a pie or doughnut chart |
| a relationship between two measures | a scatter chart |
| two measures on different scales | a combo chart, with a second axis |
| how values are spread out | a histogram |
Creating a chart
| A | B | C | |
|---|---|---|---|
| 1 | Month | Revenue | Costs |
| 2 | Jan | 12.0 | 9.5 |
| 3 | Feb | 13.5 | 10.1 |
| 4 | Mar | 15.2 | 10.8 |
| 5 | Apr | 14.8 | 11.0 |
| 6 | May | 17.1 | 11.6 |
| 7 | Jun | 19.4 | 12.3 |
- Select the data, including the headers:
A1:C7. - Choose Insert → Recommended Charts to see suggestions, or pick a type from the Charts group. Pressing Alt+F1 creates a default chart instantly.
- Excel uses the first column for the category labels and the headers for the series names.
For this data, a line chart shows revenue pulling away from costs over the six months.
Chart elements
Click the chart, then the + button beside it, to switch elements on and off:
| Element | Tip |
|---|---|
| Chart title | say what the chart shows: "Revenue grew 62% in six months", not just "Revenue" |
| Axis titles | always say the units: "lakh rupees", "visitors per day" |
| Data labels | values printed on the bars or points, instead of making readers estimate from the axis |
| Legend | needed only when there's more than one series |
| Gridlines | keep them light, or remove them |
Double-click any part of the chart to open its formatting pane: colours, fonts, axis limits, gaps between bars.
Combo charts: two scales
Revenue in lakhs and margin in percent don't fit on one axis. Select the data, choose Insert → Combo Chart, show revenue as columns and margin as a line, and tick Secondary Axis for the margin. Each series gets its own scale.
Sparklines
A sparkline is a tiny chart inside a single cell, ideal for showing a trend beside each row of a table:
- Select the cells where the sparklines should go, for example a column next to the data.
- Choose Insert → Sparklines → Line, and select the data range.
- Use the Sparkline tab to mark the high and low points.
Charts that update
A chart drawn from an Excel Table grows automatically when you add rows: add July's figures, and the chart includes them. Charts drawn from a plain range need their data range edited.
Common mistakes
- 3D effects. They distort sizes and make values harder to read. Stick to flat charts.
- Too many pie slices. Past four or five, the slices are impossible to compare. Use a bar chart instead.
- A cut-off value axis on a column chart. Starting the axis at 90 instead of 0 makes a difference of 2% look like a doubling. Column and bar charts should start at zero.
- Rainbow colours. Use one colour for one series, and a highlight colour only for the point that matters.
- Unlabelled units. A number without units means nothing to the reader.