Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 29 ยท Working with Data

Charts and Sparklines

Build charts in Google Sheets: pick the right chart type, customize axes and colors, draw SPARKLINE mini charts in cells, and publish or embed a chart.

A table of numbers hides its story. A chart shows it: sales climbing, one region lagging, a budget split into slices. Google Sheets can build a chart from any range in a few clicks, and it can also draw tiny charts inside a single cell with the SPARKLINE function.

This lesson covers choosing the right chart type, setting up and customizing a chart, writing SPARKLINE formulas, and sharing a chart outside the spreadsheet. Charts are made through menus, so those steps are described in text. JavaScript stand-ins show how data turns into bars and mini charts.

From numbers to a picture

Data range Headers on top, one row per item.
Chart type Match the shape to the question.
Customize Titles, axes, colors.
Share Publish, embed or download.
1 / 2

Creating a chart

Select the data, including the header row, then choose Insert โ†’ Chart. Sheets places a chart on the sheet and opens the Chart editor on the right. It guesses a type under Setup โ†’ Chart type (its "Suggested" charts), and you can change it from the dropdown.

For this table in A1:C5:

TEXT
Month    Sales   Costs
Jan       1200     900
Feb       1500    1000
Mar       1100    1050
Apr       1800    1100

Sheets uses the first column as the horizontal axis (the X-axis) and each other column as a Series. In the editor you can add or remove series, and the options Switch rows / columns, Use row 1 as headers and Use column A as labels fix the cases where Sheets guessed wrong. Because a chart reads its range live, editing a cell moves the chart at once. Include blank rows below the data (for example A1:C20) to let a growing table appear without editing the chart.

Google Sheets cannot run on this page, so menu steps and formulas are read-only. The JavaScript blocks are stand-ins that reproduce the idea: scaling values into bars and building a sparkline.

Choosing a chart type

Question Chart Notes
Trend over time Line or Area Time on the X-axis, in order
Compare categories Column (vertical) or Bar (horizontal) Bars suit long labels
Parts of a whole Pie or Donut Keep to 5 slices or fewer
Totals made of parts Stacked column Shows total and composition
Relationship of two numbers Scatter One dot per item
Distribution of values Histogram Counts values per bucket
Progress on a target Gauge or Scorecard One headline number

Some habits keep charts honest. Start a column or bar chart's axis at zero, because a cut axis makes small differences look huge. Do not use a pie for many slices or for values that do not add to one whole. Prefer one idea per chart.

The core of every column or bar chart is scaling: the largest value gets the full length and the others are proportional.

JS Stand-in: scale values into bars

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In the second output March looks almost empty although it is 61% of April's value. That is why zero is the safe baseline.

Customizing a chart

Open the Customize tab in the editor. The main groups are:

  • Chart style: background color, font, and whether the chart is 3D (usually avoid it).
  • Chart & axis titles: set the title, subtitle and axis titles. A good title states the finding ("Sales passed costs in every month").
  • Series: color per series, line thickness, point shapes, data labels, and an optional trendline (linear, exponential, polynomial) with an equation label.
  • Legend: position or none; remove it if there is a single series.
  • Horizontal / vertical axis: min and max values, number format, label rotation, and a log scale.
  • Gridlines and ticks: fewer lines look cleaner.

You can drag a chart anywhere, resize it, or use the three-dot menu to Move to own sheet, Copy chart or Download as PNG, PDF or SVG. A combo chart puts a line and columns together: choose Combo chart and set each series to Line or Columns, useful for sales (columns) with a margin percentage (line, on the right axis).

Sparklines

A sparkline is a chart that lives inside a cell. It is made with a formula and has no editor, which makes it ideal for a dashboard row per product.

TEXT
=SPARKLINE(B2:G2)

By default it draws a line through the values of the range. The second argument is an options table written with curly braces: each pair is "option", value, pairs are separated by semicolons.

TEXT
=SPARKLINE(B2:G2, {"charttype","column"; "color","#1a73e8"})
=SPARKLINE(B2:G2, {"charttype","winloss"})
=SPARKLINE(C2, {"charttype","bar"; "max",100; "color1","green"})

Common options:

Option Meaning
charttype line (default), column, bar or winloss
color Line or column color (name or hex)
linewidth Thickness of a line
ymin, ymax Fix the vertical range so rows are comparable
max Full-length value for a bar sparkline (progress bars)
highcolor, lowcolor Mark the highest and lowest column
negcolor Color of negative columns

A bar sparkline with max is a neat progress bar: =SPARKLINE(C2, {"charttype","bar"; "max",100}) fills C2 / 100 of the cell. Widen the column and row to give the sparkline room. If you build the option table from cells you can drive the colors with formulas, for example IF(C2>=100,"green","orange").

JS Stand-in: a text sparkline

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice how Tape looks jumpy with its own scale but flat with a shared one. That is the reason for ymin and ymax: set them when you want rows compared fairly.

Charts from formulas and live data

A chart can use a range that a formula fills. If E2:F10 holds the output of QUERY or SORT, the chart updates when the source changes. A common dashboard pattern is: raw data on one sheet, a summary table of formulas on another, and charts on a third built only from the summary. Pivot tables (see Pivot Tables) can also feed charts, and Sheets will suggest a chart when you select a pivot.

Publishing and sharing

Open the chart's three-dot menu and choose Publish chart. The dialog offers a Link or an Embed code. An embedded chart appears on a web page as an image or interactive chart, and it refreshes automatically when the sheet data changes. Publishing makes that chart visible to anyone with the link, so do not publish charts of confidential data. Stop publishing later removes access. To place a chart in Google Docs or Slides, copy it and choose Paste โ†’ Link to spreadsheet, so you can update the picture when numbers change. For people who should only see the chart, share the file with view access (see Sharing, Comments and Version History).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You want to show how monthly sales change across a year. Which chart type fits best?

    A line chart puts time on the horizontal axis and shows the trend. A pie shows parts of a whole.
  2. 2. What does =SPARKLINE(C2, {"charttype","bar"; "max",100}) draw?

    A bar sparkline fills a share of the cell, and max sets the value that counts as full.
  3. 3. Why should a column chart normally start its vertical axis at zero?

    Column length is read as size, so a cut axis makes small differences look large.
  4. 4. What happens to an embedded published chart when the sheet data changes?

    A published chart reads the sheet live, so the embedded version refreshes by itself.

Summary

  • Select the data with headers and use Insert โ†’ Chart; the editor has a Setup and a Customize tab.
  • Choose the type by the question: line for trends, column or bar for comparisons, pie for a few parts of a whole, scatter for relationships.
  • Start column and bar axes at zero; give the chart a clear title and few gridlines.
  • SPARKLINE(range, {"option", value; ...}) draws a line, column, bar or win/loss chart in one cell; use ymin and ymax to compare rows fairly.
  • Publishing a chart makes it public by link, and the embed stays up to date.

Next: Array Formulas and ARRAYFORMULA