Charts can simplify complex data, allowing us to quickly identify trends and make informed decisions. Sparklines in Google Sheets are a powerful tool for visualising data within cells. These mini-graphs are easy to create and customise, making them perfect for dashboards or reports. Let’s explore how to use sparklines effectively.
How to insert sparklines to show trends
To insert a sparkline, use the SPARKLINE function. For example, in a dataset of monthly sales for four products, type the formula =SPARKLINE(B3:B8) into a cell B9 to chart the sales of product A from January to June. This creates a line graph within the cell, showing the data trend for product A.
How to copy sparklines to other cells
If we want to see the trend for products B to D, we can copy our formula across, just like any other formula. To apply sparklines to other columns, drag the blue circle at the corner of the first sparkline cell across to the last column.
How to resize sparklines
You can resize the rows or columns to adjust the graph’s size.
How to customise sparklines with options
Customise sparklines by adding options within the formula. These options are in curly brackets and are written in key-value pairs. This just means you state what you want to set and you add the value you’re going to set it too.
For instance, in the example above, the graphs are using different Y-axes so the values can’t be compared with each other. Standardising the Y-axis across multiple sparklines ensures comparability. Let’s set the minimum and maximum Y-axis values:
=SPARKLINE(B3:B8, {“ymax”, 150; “ymin”, 30})
The keys are always in quotation marks followed by a comma and a value. Each key-value pair is separated by a semi-colon.
How to change line colour and thickness
We can also control the line colour and thickness. Note as “red” is text you also have to put it in quotation marks.
=SPARKLINE(range, {“color”, “red”; “linewidth”, 3})
Alternatively, to change the colour we can just change the cell’s font colour.
How to set a sparkline chart type
Sparklines can represent data in various forms. There are four types: line, column, stacked bar, and win-loss charts. To change the chart type, use the “charttype” option. Here, let’s set it to a column chart:
=SPARKLINE(B3:B8, {“charttype”, “column”})
We can also compare two sets of data with a stacked bar graph. For example, let’s compare two products’ sales using a stacked bar graph. Plus, let’s set the colours of each of the two products in the chart. In cell D2 type the following formula and then copy it down to cell D7.
=SPARKLINE(B2:C2, {“charttype”, “stacked”; “color1”, “red”; “color2”, “blue”})
This visualisation provides clarity on their contributions to total sales.
Sparklines in Sheets offer a concise, flexible way to visualise data trends and comparisons. Start using sparklines to gain quick insights and elevate your spreadsheets.
Check out a deeper look at sparklines in this video:
Learn more about charts here.
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).
a
