Excel Charts: Show Your Numbers in a Picture

Numbers in a table are exact, but a chart shows a pattern at a glance. Which region is strongest? Did sales rise during the week? A good chart answers a question in a few seconds, and a bad one confuses people. This lesson shows how to build the common charts in a few clicks, and how to make them clear. We use the regional totals from the earlier lessons: North 7800, South 7750 and East 4600.

Choose the chart for the question

• Column or bar chart. Compares amounts between groups, such as sales by region or by item. Use bars (horizontal) when category names are long. This is the most useful chart.
• Line chart. Shows change over time, such as daily or monthly sales. Dates go along the bottom.
• Pie or doughnut chart. Shows parts of a whole, only when there are a few slices (up to five) and the parts add up to 100%. Pies are hard to read when slices are similar, so a bar chart is often better.
• Scatter chart. Shows the relationship between two numbers, such as advertising spend and sales.
• Combination chart. Columns plus a line, for example sales and target.

Ask first: what do I want the reader to notice? Then pick the simplest chart that shows it.

Build your first chart

1. Make a small summary table on a sheet: regions in one column and totals next to them (use SUMIF from lesson 6, or copy the pivot table values).
2. Select the table including the headings.
3. Go to Insert, Recommended Charts and look at the suggestions, or choose Insert, Column Chart, Clustered Column.
4. The chart appears on the sheet. Drag it to move, and drag a corner to resize.

You get three bars, one for each region, with the North and South bars almost equal and the East bar clearly lower. That is the story of the data, visible in a second.

Make it clear

Click the chart, and use the plus button beside it (Chart Elements) and the brush button (Styles). A clear chart has these parts.

• A title that says something. Instead of Chart Title, write Sales by region, first week of June. Even better, state the message: North and South lead, East is behind.
• Axis labels with units if they are not obvious, such as Amount (rupees), using your own currency.
• Data labels showing the values on the bars, so nobody has to guess. Delete the gridlines if you add labels.
• No legend if there is only one series. Keep it when there are several.
• Sorted bars. Sort the table from largest to smallest before charting, unless the order has a meaning, such as time.
• A starting point of zero on the vertical axis for bar charts. A bar chart that starts at a high number exaggerates differences.
• Few colours. One colour for ordinary bars and one highlight colour for the point you want people to notice.

A line chart for time

To show sales per day, make a small table with the dates down one column and the daily totals next to it. Select it and choose Insert, Line Chart. The dates run along the bottom. A line suits data that is in sequence. If the dates have gaps, such as weekends with no data, Excel may treat the axis as a timeline with empty days. In Format Axis, choose Text axis if you prefer to show only the dates you have.

Pie chart, when it is right

For the three regions, a pie shows North 38.7%, South 38.5% and East 22.8%. Add data labels with percentages. A pie works here because there are only three slices, and the question is about shares of the total. Do not use a pie for values that do not add up to a whole, or for more than five or six groups.

Charts from a pivot table

Click in the pivot table and choose PivotChart (Insert, PivotChart). The chart is linked to the pivot table. Add a slicer, and when you click a staff member, the table and the chart change together. Refresh after the data changes (lesson 11).

Chart mistakes to avoid

• Too many categories or series, so the chart looks crowded.
• 3D effects, shadows and bright backgrounds that distract from the data.
• A truncated axis that makes a small difference look big.
• Colours that are hard to tell apart, or that rely on red and green alone. Some readers cannot tell them apart.
• A title like Sheet1 Chart.
• Copying a chart as a picture and forgetting to update it later.

A simple dashboard

A dashboard puts the most important numbers and charts on one page. Keep it simple.

1. Create a sheet named Dashboard.
2. Add three or four headline numbers at the top, such as Total sales (SUM), Number of orders (COUNT) and Best region. Make them large and bold, in cells with a light background.
3. Place two or three charts underneath: sales by region, sales by item and sales by day.
4. Add slicers for Region and Staff, and connect them to the pivot tables behind the charts (right-click the slicer, Report Connections).
5. Turn off gridlines on that sheet (View, untick Gridlines), align the charts neatly, and set the page to print on one page.

Do not put every possible number on it. Choose what the manager needs for decisions. A page that is tidy and quick to read is more useful than a busy one.

Copy a chart to a report

Select the chart and press Ctrl+C. In Word or PowerPoint, use Paste Special and choose Picture, if you do not want it to change, or paste with the destination theme if you want it to match. You can also right-click the chart and choose Save as Picture to get an image file.

Your task

1. Make a summary table of sales by region and build a clustered column chart. Add a clear title and data labels.
2. Make a line chart of daily total sales for 1 to 8 June.
3. Create a pie chart for the three regions with percentage labels, and decide whether the column chart or the pie tells the story better.
4. Build a one-page dashboard with three headline numbers, two charts and a slicer.

How to check it worked

• The column chart shows North 7800, South 7750 and East 4600, with the title and the labels.
• The vertical axis of the bar chart starts at zero.
• The dashboard fits on one printed page.