top of page

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

Updated: Nov 25, 2023


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.



Examples of Sparkline charts in Google Sheets, including Line, Bar, Column and Winloss charts



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. 



Basic example of a Line SPARKLINE chart in Google Sheets


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"}) 


Example of a Red Line SPARKLINE chart in Google Sheets


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})


Example of a Blue Line SPARKLINE chart with increased line width in Google Sheets


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.



Example of a Bar SPARKLINE chart in Google Sheets



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



Example of a Bar SPARKLINE chart in Google Sheets with multiple colours

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


Example of charts in Google Sheets including a SPARKLINE bar chart to track task progress



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! 



Example of a Column SPARKLINE chart in Google Sheets with highcolor and lowcolor options


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"})


Example of a Column SPARKLINE chart in Google Sheets


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"})


Example of a Column SPARKLINE chart in Google Sheets with lowcolor option


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


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


Example of a Column SPARKLINE chart in Google Sheets with highcolor option


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"})


Example of a Column SPARKLINE chart in Google Sheets with negcolor option


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"})


Example of a Column SPARKLINE chart in Google Sheets with negcolor, axis and axiscolor options



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"})


Example of a Winloss SPARKLINE chart in Google Sheets with negcolor option


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"})


Example of a Winloss SPARKLINE chart in Google Sheets with lowcolor option


Highest Value


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


Example of a Winloss SPARKLINE chart in Google Sheets with highcolor option



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.

4,752 views0 comments

Related Posts

See All

No product

SpreadsheetWise Logo
  • Facebook
  • Pinterest

© 2024 by Spreadsheetwise.com

Spreadsheet Wise | Westbrook, Kent, CT9, United Kingdom

bottom of page