What can you change to coordinate sparklines with other charts and graphic objects on a worksheet?

Sparklines are small, lightweight charts, typically without axes, which exist inside a single cell in your spreadsheets. They’re a wonderful, quick way to visualize your data, without needing the complexity of a full-blown chart.

1. Introduction
2. Sparkline Examples
3. Sparkline Syntax
4. Line Sparklines
5. Column Sparklines
6. Bar Sparklines
7. Winloss Sparklines
8. Option/value pairs in cells
9. More advanced examples
10. Further reading

Introduction

Sparklines were first created by interface designer Peter Zelchenko around 1998. The term “sparkline” was coined by statistician and data visualization legend Edward Tufte.

Sparkline examples

I’ve been building a lot of dashboards in Google Sheets recently, making heavy use of sparklines to show data trends.

For example, here’s a column sparkline to show website users in the past 30 days:

Then, a combination of line sparklines and bar sparklines for social media metrics:

And finally, I’ve used a winloss sparkline chart to show web outages over the past 24 hour period:

So let’s explore how to create these charts and customize them to meet your needs. Links to all of the examples below are available for viewing in Google Sheets.

Sparkline Syntax

The most basic sparkline in Google Sheets looks like this:

Assuming your data is in range A1 to A8, the formula would be:

=sparkline(A1:A8)

More generally, the sparkline formula syntax is:

=sparkline(data,[options])

data
This refers to the dataset (the range of values) you want to plot as a sparkline.

[options]
This is an optional argument, used to specify things like chart type (e.g. column), color and other specific settings:

• Conditions are placed inside a set of curly braces { ... }
• Options are further enclosed in double quotes, e.g. "charttype"
• Option values are usually enclosed in double quotes, e.g. "column" but numbers or true/false settings do not need the double quotes
• Options and values are separated by commas
• Multiple options/value pairs are separated by semi-colons

Example:

{"charttype","column" ; "axis",true ; "axiscolor","red"}

We’ll explore all of this below.

There are four types of sparkline charts available in Google Sheets: line charts (the default), column charts, bar charts, and winloss charts.

Let’s take a look at each in turn, through a series of examples.

Line sparklines

This is the default choice for sparklines in Google Sheets, meaning that Google will default to showing a line if you don’t specify anything in your options.

Assume I have the following data in columns A and B of my Google Sheet. The idea here is to create a sparkline that displays the sales data from column B in a single cell, as a simple, lightweight chart without any additional details.

You’ll notice there are some blank values, some negative values and some text values in column B. That’s intentional and we’ll explore how to deal with each of those with the sparkline formula.

Default:

Since line charts are the default sparkline option, we don’t need to explicitly specify the chart type. So we can create a sparkline line chart with this simple formula:

=SPARKLINE(B2:B21)

Color option:

Change the color of your sparkline by adding the color option/value pair, as follows:

=SPARKLINE(B2:B21,{"color","red"})

or using hex notation:

=SPARKLINE(B2:B21,{"color","#FFA500"})

Line-width option:

The higher the number you specify, the thicker the line.

=SPARKLINE(B2:B21,{"linewidth",3})

Xmax and Xmin options:

If our data includes a column of x-values, then we can then specify a minimum or maximum for that axis. So from our data above, we now include column A in our spakrline formula argument, then specify a minimum and/or maximum in our sparkline options, for example:

=SPARKLINE(A2:B21,{"xmin",5;"xmax",15})

This would only include values from column B, where the values in column A are between 5 and 15.

Note: I would advise a degree of caution with the xmin and xmax criteria. They are somewhat volatile and caused one of my Google Sheets to repeatedly crash.

Ymax and Ymin options:

Similarly we can specify bounds on the y values we plot in our sparkline, by using the ymax or ymin criteria. For example to only include values zero or above we would use:

=SPARKLINE(B2:B21,{"ymin",0})

To set a ymax and ymin:

=SPARKLINE(B2:B21,{"ymin",10;"ymax",20})

Empty option:

Use the “empty” option to determine whether blank cells in your dataset are rendered as 0 in your sparkline, or just ignored (the datapoint is not included in your sparkline). The formulas are respectively:

=SPARKLINE(B2:B21,{"empty","zero"})

=SPARKLINE(B2:B21,{"empty","ignore"})

Nan option:

Similar to the empty option above, use the “nan” option to determine how non-numeric cells (text cells) in your dataset are rendered in your sparkline. The options here are to convert to 0 or to ignore, as above, and the formulas are respectively:

=SPARKLINE(B2:B21,{"nan","convert"})

=SPARKLINE(B2:B21,{"nan","ignore"})

Rtl option:

Want your chart to show from right-to-left? Use the option “rtl”, which can be set to true or false, to specify the direction of your sparkline.

=SPARKLINE(B2:B21,{"rtl",true})

Compare this sparkline to the first line chart and you’ll see the reversed direction:

Column sparklines

As the name suggests, column sparklines are small column charts that exist inside a single cell. For these examples, I’m using data set up as per the line chart example.

Default option:

=SPARKLINE(B2:B21,{"charttype","column"})

Color option:

=SPARKLINE(B2:B21,{"charttype","column";"color","#FF0000"})

Lowcolor option:

=SPARKLINE(B2:B21,{"charttype","column";"lowcolor","red"})

Highcolor option:

=SPARKLINE(B2:B21,{"charttype","column";"highcolor","red"})

Firstcolor and lastcolor options:

=SPARKLINE(B2:B21,{"charttype","column";"firstcolor","red"})

Similarly, you can see the lastcolor option.

Negcolor option:

=SPARKLINE(B2:B21,{"charttype","column";"negcolor","red"})

Axis and Axiscolor options:

=SPARKLINE(B2:B21,{"charttype","column";"axis",true;"axiscolor","red"})

Ymax and Ymin options:

Works the same as the line chart examples above. For example, setting a max value of 0 creates a sparkline with only the negative columns showing:

=SPARKLINE(B2:B21,{"charttype","column";"ymax",0})

Empty option:

Works the same as the line chart examples above.

Nan option:

Works the same as the line chart examples above.

Rtl option:

Works the same as the line chart examples above.

Trick to add a reference line at a set point

A reader emailed in to ask whether you could add a reference line at a set point on a column sparkline. Unfortunately you can’t; the only thing you can do is add a y-axis at 0.

However, we can use a trick and split the data in two, and create two sparklines with a border between them to achieve this effect:

In this case, the values lie between 10 and 30, with a reference line at 20. An IF formula is used to create the split data (columns B and C):

=IF(A2<20,{A2,0},{20,A2-20})

This formula is copied into cell B2 and copied down to B21 only. It outputs an array which will populate column C. The top sparkline formulas is:

=SPARKLINE(C2:C11,{"charttype","column"})

and the lower sparkline formula is:

=SPARKLINE(B2:B11,{"charttype","column";"ymin",0})

Bar sparklines

You guessed it, these are small bar charts that exist inside a single cell. The usage is a little different than the line and column charts we've looked at so far though because these are stacked bar charts. So you can point to a single cell of data and create charts based on that, for example.

Using multiple bar sparklines to create charts:

This method works by setting a max value that applies to all your bar sparklines, using the "max" option. So step 1 is to determine a suitable maximum that works for your whole data range (consider using the MAX() formula to determine). In this example, 30 worked as my max option.

The formulas to create these sparklines are:

=SPARKLINE(A3,{"charttype","bar";"max",30})

=SPARKLINE(A4,{"charttype","bar";"max",30})

=SPARKLINE(A5,{"charttype","bar";"max",30})

etc...

Two series:

=SPARKLINE(A16:B16,{"charttype","bar";"max",40})

Two series with as stacked percentage chart:

=SPARKLINE(A27:B27,{"charttype","bar";"max",1})

Two series with as stacked bar chart:

Using the sum of the two values as the "max" option ensures that the bars will always be 100% of your cell width.

=SPARKLINE(A33:B33,{"charttype","bar";"max",sum(A33,B33)})

Changing colors:

You can specify up to two colors in sparkline bar charts. The colors alternate for each new value.

=SPARKLINE(B40:C40,{"charttype","bar";"max",SUM(B40,C40);"color1","red";"color2","black"})

Other options:

The formulas to create these bar sparklines, in the order they appear above, are:

=sparkline(B45:D45,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"})

=sparkline(B46:D46,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "empty","zero"})

=sparkline(B47:D47,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "empty","ignore"})

=sparkline(B48:D48,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "nan","convert"})

=sparkline(B49:D49,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "nan","ignore"})

=sparkline(B50:D50,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "rtl",true})

Winloss charts

These are a special type of column chart that plots only 2 possible outcomes: positive and negative (e.g. like a coin toss, heads or tails).

Here's the dataset for the following examples. The winloss sparkline is only concerned with whether a datapoint is positive or negative, it doesn't take relative sizes into account. So, in effect, it's really like a column chart of -1's and 1's.

Default winloss sparkline:

=SPARKLINE(B2:B21,{"charttype","winloss"})

Color option:

=SPARKLINE(B2:B21,{"charttype","winloss";"color","red"})

Lowcolor/Highcolor option:

Even though the winloss chart displays all the "columns" the same height, it is able to highlight the highest and lowest values in your original dataset. For example, the following formula colors the lowest values red (in the case of this dataset, that's the three -10 values):

=SPARKLINE(B2:B21,{"charttype","winloss";"lowcolor","red"})

Firstcolor option:

Highlights the first value of the dataset:

=SPARKLINE(B2:B21,{"charttype","winloss";"firstcolor","red"})

Lastcolor option:

=SPARKLINE(B2:B21,{"charttype","winloss";"lastcolor","red"})

Negcolor option:

Highlights all the negative values:

=SPARKLINE(B2:B21,{"charttype","winloss";"negcolor","red"})

Axis and Axiscolor options:

=SPARKLINE(B2:B21,{"charttype","winloss";"axis",true; "axiscolor","red"})

Empty option:

Works the same as the line chart examples above.

Nan option:

Works the same as the line chart examples above.

Rtl option:

Works the same as the line chart examples above.

Option/value pairs in cells

So far, we've encoded the options as an array of text strings inside of the sparkline formula, between the curly braces, e.g.:

{"charttype","column";"axis",true;"axiscolor","red"}

However, we can instead list our option/value pairs in a range of cells in our spreadsheet and then reference them in the sparkline formula.

Consider a dataset and option/value pairs setup as follows:

Then enter this formula into cell F2:

=sparkline(A2:A21,{C2,D2;C3,D3})

to create your sparkline. We still need to enclose the options in the curly braces, { ... }, and separate the options and values with commas and semi-colons, but notice how we've used cell references instead of text strings this time.

The output of this formula is the following sparkline, in cell F2:

We can take this even further by putting all our options into two columns and then referencing those ranges:

The formula in this case is then:

=sparkline(A2:A21,{C2:C9,D2:D9})

where my range of option/value pairs extends down to row 9 (adjust as needed to accommodate a different number of options).

Lastly, we could get fancy by adding a data validation drop down menu to pick from the option/value pairs, e.g. for charttype, as shown in the following GIF:

Advanced Sparkline Examples

You can nest other formulas inside of sparklines, for example, you can quickly and easily plot stock prices using the sparkline formula.

Let's create sparklines for the big tech stocks for the past 60 days, like so:

We create these charts by first using the GOOGLEFINANCE() function to get the stock prices for the past 60 days up to today (using the TODAY() formula):

=GOOGLEFINANCE($B3,"price",TODAY()-60,TODAY(),"DAILY")

Then we nest this inside the sparkline formula to chart this data:

=SPARKLINE(GOOGLEFINANCE($B3,"price",TODAY()-60,TODAY(),"DAILY"),{"charttype","line";"linewidth",2;"color","#5f88cc"})

Cool huh!

We can also turn this same data into column charts, but we need to finesse the GOOGLEFINANCE data since we only want the second column of data. We do this with the QUERY() function:

=QUERY(GOOGLEFINANCE($B13,"price",TODAY()-60,TODAY(),"DAILY"),"select Col2",-1)

Then we nest this inside the sparkline formula:

=SPARKLINE(QUERY(GOOGLEFINANCE($B13,"price",TODAY()-60,TODAY(),"DAILY"),"select Col2",-1),{"charttype","column";"highcolor","red"})

I've also highlighted the highest stock price in our period, using red shading. The output is as follows:

Crazy Sparkline Examples

Your imagination is the only limit with the sparkline function.

How about an Etch-A-Sketch clone built using a sparkline formula?

Etch A Sheet in Google Sheets

Or what about a working analog clock built with a single sparkline formula:

See also this post on recreating Visualize Value's design work in a Google Sheet using SPARKLINEs (opens in Twitter).

Further reading

Official Google Documentation.

Sparklines on Wikipedia.

Edward Tufte's detailed take on sparklines.