Introduction
Numbers in a table tell you values; a chart tells you the story: which region is ahead, whether sales are rising, how two measures move together. Excel makes a chart in a few clicks, but a good chart needs the right type and a little tidying. This lesson covers choosing, creating and formatting charts, and the small in-cell charts called sparklines.
How it works
Choose the type from the question:
- Column (vertical) and bar (horizontal) charts compare amounts across categories. Use bars when category names are long.
- Line charts show change over time, with time along the horizontal axis.
- Pie and doughnut charts show the parts of one whole; use them only for a few parts that add up to 100%.
- Scatter charts show how two numeric measures relate, such as advertising spend and sales.
- Combo charts mix types, for example columns for monthly sales and a line for the target, with an optional secondary axis when the scales differ.
- Histograms show how values are distributed, and waterfall charts show how a start value is changed by gains and losses (both in Excel 2016 and later).
Create a chart by selecting the data, headings included, and choosing Insert, then a chart type, or Recommended Charts. Alt+F1 on Windows inserts the default chart on the same sheet, and F11 puts it on a chart sheet of its own. The chart stays linked: change a number and the chart changes. If the data is an Excel Table, new rows are included automatically.
Adjust it with the buttons beside a selected chart (on Windows) or the Chart Design and Format tabs: add or remove the chart title, axis titles, data labels, legend and gridlines. Switch Row/Column swaps which headings become the series. Select Data changes which cells are plotted.
Format any part by selecting it and pressing Ctrl+1 (Command+1 on Mac): axis minimum and maximum, number formats, gap width between columns, colours. Column and bar charts should start their value axis at zero, because the length of a bar is how readers compare it; a cut-off axis exaggerates differences.
Sparklines (Insert, Sparklines: Line, Column or Win/Loss) draw a tiny chart in one cell from a row of numbers, which is ideal next to a table to show each row's trend.
Example
Monthly sales and a target:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Target |
| 2 | Jan | 120 | 100 |
| 3 | Feb | 90 | 100 |
| 4 | Mar | 150 | 110 |
| 5 | Apr | 130 | 110 |
Line by line
- Select A1:C5 and choose Insert, Combo Chart, Clustered Column - Line.
- Make sure Sales is shown as columns and Target as a line; both use the primary axis because their scales are similar.
- Set the chart title to Sales against target, and add data labels to the Sales series.
- In D2 add a Line sparkline for B2:B5 to show the trend in a single cell (select D2, Insert, Sparklines, Line, data range B2:B5).
Expected result
The chart shows four columns (120, 90, 150, 130) with labels, and a line stepping from 100 to 110. February's column falls below the line, so the miss is visible at a glance. D2 holds a small line that dips in February and rises in March. Changing B3 to 105 raises February's column immediately.
In the real world
Reports and dashboards are built from a few well-chosen charts. Many organisations have chart standards: specific colours for actual and target, no 3-D effects, axes starting at zero. A chart pasted into PowerPoint or Word can stay linked to the workbook so it updates when the numbers change.
Common mistakes
- Pie charts with many slices or with parts that do not add up to one whole.
- 3-D charts, which distort sizes and make values hard to read.
- Truncated value axes on column charts that exaggerate small differences.
- Plotting totals together with their parts, which dwarfs everything else.
Best practices
- Give every chart a title that states the message, such as "February missed the target".
- Remove what does not help: heavy gridlines, borders, unnecessary legends.
- Build charts from Tables so that new data is included without editing the chart.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Insert the default chart on the sheet | Alt+F1 | — |
| Insert a chart on its own chart sheet | F11 | — |
| Format the selected chart element | Ctrl+1 | Command+1 |
| Move between chart elements | Arrow keys (with the chart selected) | — |
| Copy the chart | Ctrl+C | Command+C |
Summary
Pick the chart from the question: columns and bars to compare, lines for time, scatter for relationships, pies for a few parts of one whole, combos for actuals against targets. Charts stay linked to their data, are adjusted with chart elements and formatted with Ctrl+1, and should start column axes at zero. Sparklines show each row's trend inside a cell.
Quick check
Key takeaways
- Columns and bars compare, lines show change over time, scatter shows relationships, pie shows parts of one whole.
- A chart is linked to its data; build it from a Table so new rows are included.
- Start value axes at zero for column and bar charts, label clearly, and remove clutter.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.