Google Sheets has native support for sparklines. This is more versatile than the Excel implementation, which has me torn – plotting in Excel is MUCH better than plotting in Sheets, but the sparklines in Sheets is better than Excel. I thought I would take you through an example of how I’m leveraging sparklines in Google Sheets in this post.

Note, sparklines was a concept first championed by Edward Tufte. His wonderful lineup of books on data visualizations are worth the read.

Also, my intention with this post is not to provide you with comprehensive instructions on how to use sparklines (there are plenty of other how-tos out there); my goal is to challenge you to think about how you can leverage this powerful tool to enhance your audience’s understanding of your data. This requires that you give thought to characteristics like color, data ranges, max values, and when to use them.

Column sparklines

This set of examples will focus on a simple implementation of sparklines using COVID19 case data from our local school district. I’m using a simple table with case arrivals per-day looking at cases relative to several views.

This first example is case arrivals per high school in our district. You can see the sparkline implementation in the column labeled “case arrivals by day”.

These sparklines show the cases per day for the 2021-2022 school year, starting in August of 2021 and moving through mid-January of 2022 (the time of this writing). This simple implementation allows you to quickly view:

  • the relative arrival rates by high school
  • the total cases per high school
  • patterns in the arrival rates per high school
  • the impact of delta at the start of the school year
  • the impact of omicron in January.

The change in arrival rates can be rapidly assessed with this simple infographic.

Another view that I have been tracking is by grade:

Again we can quickly see the relative distribution of total cases with the heat map, but the sparklines show us details for the entire year. You can see delta (September peak early in this plot) impacted 5th and 6th graders, while omicron impacted all grade levels, but was skewed more heavily toward the older kids.

Here is a screen shot of the spreadsheet view that the grade level graphic with one of the sparkline cells highlighted to reveal the equation that created the graphic:

Here is the equation:

=SPARKLINE(H4:DT4, {"charttype","column"; "color","gray"; "ymax",max($H$5:$DT$18)})

You can see this implementation is pretty straight forward. Here are a few key design choices I made:

  • H4:DT4 – designates the horizontal range of data the plot is representing. This is a wide table with a column per school day.
  • “charttype”,”column” – I’m using a simple column chart. I prefer the distinct bars to help the viewer understand these are individual data points.
  • “color”,”gray” – Most of my graphics use gray scales to help focus on the data, rather than random distracting colors.
  • “ymax”,max($H$5:$DT$18) – This is perhaps the most unique trick I prefer to use when using sparklines across multiple series – set the max to include all the data, so we can see the relative distribution of cases across grade levels. Using an equation here allows the sparklines to dynamically determine the max across multiple rows (5 through 18). If you keep the max set to a single row, the relative size of incoming cases across grades would not be obvious.

That is really all there is to it. Line sparklines are very similar, but in most cases I prefer the look of column sparklines over lines. The visual weight of the color fill within the column is more impactful, so most of my visualizations use columns instead of lines.

Bar sparklines

I chose to use a gradient within the cells of the grade level plot from above. Let’s add another column for a simple bar sparkline to evaluate a different approach for representing our dataset.

This provides another way to rapidly assess the relative size of the total infection counts per grade. Let’s remove the gradient for a cleaner look:

This looks cleaner, and may provide a more readily assessed size difference for the viewer. However, I think using “gray” for the color is creating too strong of a block of color. Let’s lighten this up:

I would like to see additional controls on the width of the bars (vertical height or thickness). I think controlling the gap width would allow for more useful customizations, but this is a quite powerful method for rapidly embedding micro plots. The final equation that I used for these plots was:

=SPARKLINE(D4, {"charttype","bar"; "color1","gainsboro"; "max",max($D$4:$D$18)})

Grayscale values I would experiment with:

gainsboro#DCDCDCrgb(220,220,220)
lightgray#D3D3D3rgb(211,211,211)
silver#C0C0C0rgb(192,192,192)
darkgray#A9A9A9rgb(169,169,169)
gray#808080rgb(128,128,128)
dimgray#696969rgb(105,105,105)

Hopefully you found this write up useful, and different from the usual how to!