Sparklines in Google Sheets: A Simple Guide to Rich Data Visualisation

Welcome to the dynamic world of sparklines in Google Sheets. These minimalistic yet powerful charts, nestled neatly within a single cell, offer a straightforward way to present data trends with rich data visualisation. Let me guide you on a journey to understand and effectively use sparklines, turning your data into compelling visual stories.

Introduction

In the realm of data visualisation, less can indeed be more. Google Sheets offers a plethora of tools to bring data to life, one of which is the often-underutilized sparkline. Sparklines, small yet mighty, provide a unique way to display data trends and progress directly within a cell. This article aims to demystify sparklines, showing you how to harness their simplicity and effectiveness in Google Sheets.

Line Sparklines

Creating sparklines is a breeze. Begin by choosing the data you want to represent with a sparkline. For instance, let’s say you have monthly sales data in cells B2 to B13.

Start with a basic line sparkline by typing =SPARKLINE(B2:B13) in a cell adjacent to your data range. This simple formula transforms your data into an insightful line chart within a cell. 

Line Sparklines are ideal for showing trends over time, like monthly sales data. You can smarten these up by introducing some options to customise the line. Here are a few examples. 

This formula explicitly states that we want the chart type to be a line chart with a red colour.

=SPARKLINE(B2:B11,{"charttype","Line";"color","Red"}) 

This line chart includes an option to make the line with 2 points rather than the standard 1 point. 

=SPARKLINE(B13:B24,{"charttype","Line";"color","0b5394";"linewidth",2})

To explore more, try customising your sparkline to a bar chart with =SPARKLINE(B2:B13, {"charttype", "bar"}). This changes the sparkline from a line to a series of bars, each representing a data point.

This article is worth reading for more details about bar sparkline charts. Use the Sparkline function in Google Sheets to create a mini Progress Bar Chart

It's possible to create some amazing things with SPARKLINES, and the bar, column and line charts are definitely a good place to start.

Below is a Task List Analytics Dashboard to visualise the status of tasks using a progress bar chart, which was made with, you guessed it, the SPARKLINE function in Google Sheets!

Check out this post here for more details on this: Task List in Google Sheets with Kanban & Automated Status Tracking

Advanced Sparkline Features

To refine your sparklines further, explore using Option/Value pairs in the sparkline formula. These pairs allow you to customise chart type, colour, and other settings. 

For example, check the formula below:

=SPARKLINE(B2:B13,{"charttype", "column"; "color","blue"; "lowcolor", "red"; "highcolor", "green"})

The {"charttype", "column"}, {"color", "blue"}, {"lowcolor", "red"} and {"highcolor", "green"} are Option/Value pairs. 

Here, "charttype", "color", “lowcolor” and “highcolor” are the options, and "column", "blue", “red” and “green” are the corresponding values, specifying the type of the sparkline and its colours, respectively. You can add multiple Option/Value pairs, separated by semicolons, to customise your sparkline to your exact needs.

The Option and Value are separated by a comma, and a semicolon separates the Option/Value pairs. It’s a small detail but an important one. Lastly, these options must be wrapped up between these curly braces { }; otherwise, the formula won’t work. 

Colour Coding

Add more context by colour-coding the sparkline. Taking the example just mentioned above, this formula highlights the highest and lowest points in different colours. The primary colour is also set to Blue. 

=SPARKLINE(B2:B13,{"charttype", "column"; "color","blue"; "lowcolor", "red"; "highcolor", "green"})

Note the way that colour is spelt in the formula. I’m from the UK, so I spell this with a ‘u’, but when used in the formula, you should spell this the American way! 

There is a whole load of options for sparkline charts, way too many for the scope of this article, but if you’re interested take a look at the Google Help Page on SPARKLINES here. You’ll find everything from ‘xmin’ and ‘max’ to ‘nan’ and everything in between. 

Column Sparklines

Use Column Sparklines to compare values, like product performance, across different regions. The example below holds regional sales data.

=SPARKLINE(A2:A25,{"charttype","column";"color","0b5394"})

The column chart type lends itself to a few different options to showcase the data in various ways. 

Using the lowcolor option highlights the single lowest data point in the range: 

=SPARKLINE(C13:C24,{"charttype","column";"lowcolor","ff1b1b";"color","0b5394"})

Similarly, the highcolor option highlights the single highest data point in the range: 

=SPARKLINE(A2:A25,{"charttype","column";"highcolor","ea4335";"color","0b5394"})

If you’re looking to highlight all the negative data points, then the “negcolor” is the perfect option to use.

=SPARKLINE(C13:C24,{"charttype","column";"negcolor","ff1b1b";"color","34a853"})

If you’re looking for more clarity, you can define the axis line and its colour using these options: { “axis”, true; “axiscolor”,”ff1b1b”}

=SPARKLINE(C13:C24,{"charttype","column";"axis",true;"axiscolor","ff1b1b";"negcolor","0b5394";"color","34a853"})

Winloss Sparklines 

Perfect for displaying wins and losses, or the presence and absence of a condition, like website up/down status. Winloss sparklines are a unique form of column chart representing data in terms of two distinct outcomes: positive or negative, akin to the binary result of flipping a coin (heads or tails). 

It's important to note that winloss sparklines focus solely on the nature of the data points (positive or negative) without considering their magnitude. Essentially, they function as a column chart where the values are simplified to -1 for negative and 1 for positive outcomes.

If your data is 25 and -15, for example, this will translate to 1 and -1 for a winloss chart. The example below uses the “winloss” chart type and the “negcolor” options. 

=SPARKLINE(C13:C24,{"charttype","winloss";"negcolor","ff1b1b";"color","0b5394"})

While the winloss chart uniformly displays the columns' height, it can still emphasise your dataset's highest and lowest values. For instance, in the formula provided, the lowest value is highlighted in red, offering a clear visual distinction despite the consistent column height.

Lowest Value

=SPARKLINE(C13:C24,{"charttype","winloss";"lowcolor","ff1b1b";"color","0b5394"})

Highest Value

=SPARKLINE(C13:C24,{"charttype","winloss";"highcolor","ff1b1b";"color","0b5394"})

Conclusion

Sparklines in Google Sheets are a powerful tool for succinctly presenting data trends and insights. With their simplicity and versatility, they can enhance your data analysis, making your spreadsheets not only more informative but visually appealing. 

Incorporating sparklines into your Google Sheets is a smart move towards enhanced data storytelling. Embrace the power of sparklines and let your data tell its story in the most impactful way.