SPARKLINE function in Google Sheets

SPARKLINE draws a tiny chart inside a single cell from a range of numbers.

A sparkline is a chart small enough to live in one cell. Put one at the end of each row of a table and the reader sees at a glance which lines are rising and which are falling, without a single full-size chart. In Sheets it is a function, which means it can be copied down like any formula.

The examples on this page have no result to print, because the result is a picture inside the cell.

Syntax

=SPARKLINE(data, [options])

Argument	What it is
`data`	The range of numbers to draw.
`options`	Settings in curly brackets, as pairs of name and value, such as {"charttype","column"}.

Examples

Sample sheet: Sales and targets laid out across the sheet, with the months along the top.

	A	B	C	D	E	F	G
1	Month	Jan	Feb	Mar	Apr	May	Jun
2	Sales	4700	2700	5250	2300	3750	4100
3	Target	4000	3000	5000	3000	3500	4000

=SPARKLINE(B2:G2)
Result: depends on live data or changes each time
A small line chart of the six monthly sales figures, drawn inside the cell.
=SPARKLINE(B2:G2,{"charttype","column"})
Result: depends on live data or changes each time
The same figures as tiny columns.
=SPARKLINE(B2:G2,{"charttype","column";"color","green";"highcolor","blue"})
Result: depends on live data or changes each time
Several options, separated by semicolons: green columns with the highest one in blue.
=SPARKLINE(B2,{"charttype","bar";"max",5000})
Result: depends on live data or changes each time
A single number as a bar. With max set to 5000, the bar fills the cell in proportion to the value: a progress bar.
=SPARKLINE(B2:G2,{"charttype","line";"linewidth",2;"color","red"})
Result: depends on live data or changes each time
A thicker red line.
=SPARKLINE(B2:G2,{"charttype","winloss"})
Result: depends on live data or changes each time
A win and loss chart: one block up for each positive number and one down for each negative.

How it works

Give SPARKLINE a row or column of numbers and it draws a line through them, scaled to fit the cell. Make the cell wider or the row taller and the chart grows with it.

Chart types

charttype	Draws	Good for
`"line"` (the default)	a line	a trend over time
`"column"`	small upright columns	comparing periods
`"bar"`	one horizontal stacked bar	progress towards a target, or parts of a whole
`"winloss"`	blocks above and below a middle line	wins and losses, profit and loss months

Writing options

Options go in curly brackets as pairs: a name in quotation marks, a comma, then the value. Separate the pairs with semicolons:

{"charttype","column"; "color","green"; "max",100}

The options can also be kept in two columns of cells, names in one and values in the next, and the range given as the second argument. That makes them easy to change without editing the formula.

Useful options

Option	Applies to	Does
`"color"`	all	the main colour, as a name or a code such as "#0b8043"
`"linewidth"`	line	thickness of the line
`"max"`, `"min"`	bar	the value that fills the cell; for other types use `"ymax"` and `"ymin"`
`"highcolor"`, `"lowcolor"`	column, winloss	colour of the highest and lowest columns
`"negcolor"`	column, winloss	colour of negative values
`"firstcolor"`, `"lastcolor"`	column, winloss	colour of the first and last columns
`"empty"`	line, column	how to treat empty cells: "zero" or "ignore"
`"rtl"`	all	TRUE draws from right to left

A progress bar

A bar sparkline with one number and a max makes a neat progress bar:

=SPARKLINE(C2, {"charttype","bar"; "max",100; "color1","green"})

With a percentage complete in C2, the bar fills that share of the cell. Bars use color1, color2 and so on, one for each segment.

To change colour with the value, put an IF inside: "color1", IF(C2>=75,"green",IF(C2>=40,"orange","red")).

One per row

Write the formula in the first row and copy it down, as with any formula. Each row gets its own little chart of that row's numbers. Set "ymin" and "ymax" to the same values in every row if the rows should be comparable with one another. Without them, each chart is scaled to its own data, so a tiny wobble and a huge swing can look the same.

Limits

A sparkline shows shape, not figures. There are no axes or labels. Keep the numbers beside it.
It cannot be clicked or hovered for values.
For anything that needs a title, a legend or several series, insert a real chart.

Common mistakes

Commas where semicolons are needed between option pairs, or the reverse.
Option names misspelt or without quotation marks. An unknown option is ignored without an error.
Text or blank cells in the data. Use "empty","ignore".
Comparing rows whose charts are on different scales.

In Excel

Excel has sparklines too, but as a feature, not a function: Insert, Sparklines places them in cells and a ribbon tab sets their options. A SPARKLINE formula does not survive conversion to an Excel file.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.