Excel Charts: How to Choose the Right Chart Type

Use a line chart to show change over time, a column chart to compare categories, a pie chart only when you have a few parts that add up to a whole, and a scatter chart to show whether two numbers move together. Most unreadable charts come from picking the wrong type for the question, not from poor formatting.

I once presented a pie chart with fourteen slices to a management meeting, each slice a different product line. I thought it looked colourful and impressive. Within about thirty seconds, one of the directors asked, politely, what exactly he was looking at, and I realised I couldn't read it properly myself from the back of the room. I rebuilt it as a sorted bar chart that evening, and nobody asked a single question about how to read it the next time. That was more or less the end of my pie chart phase.

How do I create a chart in Excel?

1. Select the data you want to chart, including the column and row headings
2. Go to Insert and choose a chart type from the Charts group, or click Recommended Charts to see what Excel suggests for your data
3. Click the chart, and use the Chart Design tab that appears to change the style, swap rows and columns, or change the chart type

The data layout matters more than people expect. For most charts, put your categories (months, regions, products) down the first column and your values in the columns beside them, with a clear heading at the top of each column. If a chart looks wrong straight away, the quickest fix is often Chart Design > Switch Row/Column.

Which chart type should I use for which job?

What you want to show	Best chart	Why
Change over time (months, years)	Line chart	The eye follows the line and spots trends easily
Compare amounts between categories	Column or bar chart	Lengths are easy to compare side by side
Parts of a whole (3 to 5 parts)	Pie or doughnut chart	Works only when there are few slices
Relationship between two numbers	Scatter chart	Shows clusters and outliers
Two measures with different scales	Combo chart	Lets you plot, say, revenue and margin together

A bar chart (horizontal) is usually better than a column chart (vertical) when your category names are long, since the labels have room to sit on the left without being squashed or rotated.

Why is my pie chart so hard to read?

Because pie charts are only good at one thing: showing a handful of parts that add up to 100%. Once you pass five or six slices, or the slices are close in size, the human eye can't compare angles accurately. My own rule of thumb is that if I need to add data labels to every slice just to make the chart understandable, a sorted bar chart would have done the job better.

If your boss insists on a pie chart, at least sort the slices from largest to smallest, start with the biggest at the 12 o'clock position, and group the small ones into a single "Other" slice.

How do I make a line chart show the right dates?

If your dates are stored as real dates (not text), Excel will usually place them on a proper time axis automatically, spacing the points according to the actual gaps between dates. If they're stored as text, the axis treats them as plain labels and spaces them evenly, which can distort the picture. If you've read my post on "cleaning messy data", text-formatted dates are one of the usual suspects there, and fixing them before you chart saves a lot of fiddling afterwards.

To check the axis behaviour, right-click the horizontal axis, choose Format Axis, and look at the Axis Type options (Automatic, Text axis, Date axis).

How do I put two different measures on one chart?

This is what a combo chart is for. Say you have monthly revenue in dollars and gross margin as a percentage. Plotted on the same axis, the percentage line would sit flat along the bottom, because the numbers are on completely different scales.

1. Select your data and go to Insert > Recommended Charts > All Charts > Combo
2. Set revenue as Clustered Column, and margin as Line
3. Tick Secondary Axis next to the margin series

The secondary axis appears on the right-hand side with its own scale, so both series are readable. One caution: charts with two axes can mislead if the scales are chosen carelessly, so label both axes clearly.

How do I make a chart update automatically when I add new data?

Charts built from a plain range need their data range extended by hand when you add rows. Charts built from an Excel Table expand on their own. If you've read my post on "Excel Tables", this is the same benefit applied to charts: convert the source data to a Table with Ctrl+T before you build the chart, and new rows flow into it without any manual adjustment.

What are the common chart mistakes worth avoiding?

1. Truncated axes on column charts. Starting the vertical axis at, say, 90 instead of 0 makes small differences look enormous. For column and bar charts, start at zero.
2. Too many colours. Use one colour for the main series and a contrasting colour only for the thing you want people to notice.
3. 3D effects. They distort the visual comparison and add nothing. Stick to flat charts.
4. Missing titles and axis labels. A chart should make sense when someone sees it without you standing beside it. Put the unit (for example, "Sales, $000") in the axis title.
5. Default legend taking up space. If you only have one series, delete the legend. If you have several, consider labelling the lines or bars directly instead.

FAQ

What's the difference between a column chart and a bar chart? Column charts run vertically and bar charts run horizontally. They show the same information. Bar charts suit long category names, and column charts suit time-based categories like months.

Can I chart only part of my data without deleting the rest? Yes. Select only the cells you want, or right-click the chart, choose Select Data, and adjust which ranges feed it. You can also filter a series using the small funnel icon beside the chart.

How do I change the colours of a chart to match my company's branding? Click the chart, go to Chart Design > Change Colors, or click an individual series and use Format > Shape Fill to set an exact colour. The exact menu names can vary slightly between Excel versions.

How do I copy a chart into PowerPoint or Word without it breaking? Copy the chart and use Paste Special in the other program. Pasting as a picture keeps it looking exactly the same but stops it updating, while pasting with linked data keeps it live but depends on the source file staying in the same place.

Why does my chart show a gap or a zero for a blank cell? By default, Excel treats blanks as gaps. You can change this under Select Data > Hidden and Empty Cells, where you can choose to show blanks as gaps, as zero, or to connect the data points with a line.