Sparkline Graphs in Google Sheets

Click image to enlarge

Click image to enlarge

There is a fairly comprehensive and capable tool in Google Sheets for creating graphs and charts, which we’ll have a look at in a future post. However, when you just need a quick-and-dirty graphical representation of some data with a minimum of coding and fuss, consider using single-cell sparklines.

Sparklines base their y-value on a range of data somewhere within your sheet. However, they take no x-value, and therefore are best used or viewing simple trends or comparisons.

You can make a sparkline within a single cell. To do so, use the sparkline() function and give it a data range as a parameter:

=sparkline([data range])

where [data range] is the range of your table that you want displayed. The sparkline for the FTSE100 index shown in the figure was simply created using the formula:

=sparkline(B3:B7)

[To see how to get historic stocks and shares data see this post].

It’s very easy to add some color to your sparklines – just use the foreground and background color buttons from the toolbar:

Colored sparklines

Click image to enlarge

UPDATE December 2014

Google has added further options to the sparkline function, via an optional parameter taking the form of a JSON object:

SPARKLINE(data, [options])

The charttype option defines the type of chart to plot, which includes:

line for a line graph (the default)
bar for a stacked bar chart
column for a column chart
winloss for a special type of chart that plots 2 possible outcomes, win or lose

Click image to enlarge

Click image to enlarge

For line graphs:

xmin sets the min value along the horizontal axis.
xmax sets the max value along the horizontal axis.
ymin sets the min value along the vertical axis.
ymax sets the max value along the vertical axis.
color sets the color of the line.
empty sets how to treat empty cells: “zero” or “ignore”.
nan sets how to treat cells with non-numeric data: “convert” or “ignore”.
rtl sets whether or not the chart is rendered right to left: true or false.

For column and winloss graphs:

color sets the color of chart columns.
lowcolor sets the color for the lowest chart value
highcolor sets the color for the highest chart value
firstcolor sets the color of the first column
lastcolor sets the color of the last column
negcolor sets the color of all negative columns
empty sets how to treat empty cells: “zero” or “ignore”.
nan sets how to treat cells with non-numeric data: “convert” or “ignore”.
axis sets if an axis needs to be drawn: true or false
axiscolor sets the color of the axis
ymin sets the custom minimum data value that should be used for scaling the height of columns (not for winloss)
ymax sets the custom maximum data value that should be used for scaling the height of columns (not for winloss)
rtl determines whether or not the chart is rendered right to left: true or false.

Click image to enlarge

Click image to enlarge

For bar charts:

max sets the max value along the horizontal axis.
color1 sets the first color used for bars in the chart.
color2 sets the second color used for bars in the chart.
empty sets how to treat empty cells: “zero” or “ignore”.
nan sets how to treat cells with non-numeric data: “convert” or “ignore”.
rtl determines whether or not the chart is rendered right to left: true or false.

Note that colors can be written using their names (e.g., red) or as a hex code (e.g., #ff0000).