Spreadsheet Practice

Google Sheets lesson 7 of 14, Data tools

Charts that fit the data

Pick the chart that answers the question, and give it exactly the right range.

FreeSheets mode10 minutes, 4 exercisesLineColumnBarPieScatter

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

Select the data, header row included, and choose Insert > Chart. Sheets suggests a chart type and opens the chart editor. On its Setup tab you can change the Chart type and the Data range, and check how the X-axis and series were read. The header row becomes the series names, and the first column usually becomes the labels.

Choose the chart by the question it answers. Change over time: a line chart, with time running left to right. Comparing categories: a column chart, or a bar chart when the labels are long or there are many of them. Parts of one whole: a pie, and only when a few slices add up to a meaningful total. Whether two numbers move together: a scatter chart, with one point per row.

Get the range right before styling anything. Leave out total rows, or the total becomes the tallest bar and squashes the rest. To compare two series, such as revenue and target, include both columns next to the labels, as in A1:C6, and Sheets draws them side by side. For a scatter chart, the range is the two number columns, with the X-axis column first.

Syntax

Line, A1:B13A trend across twelve months
Column, A1:C6Two series compared region by region
BarA column chart turned sideways, for long labels
Pie, A1:B5Shares of one total, with few slices
Scatter, B1:C13One point per row: X column first, then Y

Watch out for

  • A Total row in the range adds a bar that dwarfs the rest. Stop the range above it.
  • A pie with many slices, or with numbers that don't add up to a whole, is hard to read. Use bars.
  • A line drawn between categories with no order, like regions, suggests a trend that isn't there.
  • For a scatter chart, put the X-axis column first in the range.

The exercises

  1. Sign-ups over the year. Chart the monthly sign-ups from January to December so the trend over the year is easy to see. Pick the chart type and enter the data range, with the header row. Leave out the Total row.
  2. Revenue against target. Compare each region's revenue with its target, side by side. Chart both series with the region names as labels, and leave the Reps column out.
  3. Where the budget goes. Show what share of the monthly budget each category takes. Chart the four categories with their amounts and the header row, without the Total row.
  4. Ad spend and sign-ups. Does spending more on ads bring more sign-ups? Chart sign-ups against ad spend with one point per week: ad spend on the X-axis, sign-ups on the Y-axis. Leave the Week column out.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

Sign-ups over the year

Chart the monthly sign-ups from January to December so the trend over the year is easy to see. Pick the chart type and enter the data range, with the header row. Leave out the Total row.

Chart editor: Setup

Chart type

Select cells in the sheet and press Use, or type the range. Include the header row.

Pick a chart type and a data range to see the chart.