Google Sheets – How to use Sparklines (mini-graphs)

We generally use data to give us information on something and to help us make more informed decisions. However, human beings aren’t very good at looking at a set of numbers and working out whether there is a trend or whether something looks unusual. This is where charts are invaluable. The old adage of a picture paints a thousand words, well it certainly paints a thousand numbers. One chart can instantaneously tell you what’s going on with the data.

There are two ways to create charts in Google Sheets, one is the more traditional approach, i.e. making a big chart from the data and either putting it on your sheet or as a separate sheet  (I will cover how to do this in a future post) or the other is inserting mini-graphs right next to your data to show what’s going on. These mini-graphs are called sparklines and that’s what I’m going to show you how to make in this post.

Here we have the monthly sales of 5 products (A to E). The sales manager wants to know how well each product is doing. Looking at just the data we may have some ideas but charts would make analyzing it so much easier.

Inserting basic sparklines (line graphs)

First, let’s add a sparkline in cell B16 which will show the trend of product A’s across the year.

Adding sparklines is the same as adding a function, type the following into cell B16:

=SPARKLINE(B3:B14)

As you can see this produces a little line graph in the cell, which displays the Product A data.

Now we want to do the same for the other products but instead of typing in the SPARKLINE function, we can just copy it across like any other function and formula.

Click on the cell and grab the little blue square (now a circle) in the bottom right-hand corner of the cell and drag it to cell G16.

Now we have trends for each of the products (and the total) and we can easily see which product’s sales are falling and the sales manager can take some action.

If we want to increase the x or y axis of the sparklines, maybe to see the chart in more detail, we just increase the row height or column width.

Sparklines are therefore excellent for giving you a quick insight into what’s happening with your data and are really easy to set up. Also, as they are small they are great for using with dashboards, where you may have lots of different data sets all on one sheet.

---

ADDING EXTRA OPTIONS TO YOUR SPARKLINES

Line graphs

We can control how the sparkline looks by adding some options to our function above.

The initial graphs we’ve made are fine for seeing the trends of the sales, but what happens if we want to compare the products with each other. There’s a problem, the maximum sales for each graph is different, so for example, product B’s maximum is 150 whereas, C’s is only 90, yet the graphs look like they have sold the same amount in December. This is because the y axis is different for each graph, so to be able to compare the sales of each product with each other, we need the same y axis for each of the graphs.

So let’s set the minimum and maximum value of the y axis, so all of them are the same. This is where we add set the options of the graphs within the function.

In B17 type the same function as before except without the last bracket:

=SPARKLINE(B3:B14

Now let’s add the options. First let’s add the maximum for the y axis, let’s make it 150:

=SPARKLINE(B3:B14,{“ymax”,150})

Usually the options are in pairs, the name of the setting and then the value.

Note, the syntax. After the range, there’s a comma, then open curly brackets, then ymax in speech marks, comma, then 150, then closed curly brackets, and finally a normal closed bracket. There are no speech marks needed for 150 as it’s a number.

Now let’s add the minimum for the y axis, and let’s make it 30:

=SPARKLINE(B3:B14,{“ymax”,150;”ymin”, 30})

Here, we’ve added another pair of settings, ymin along with the value 30. Note, a semi-colon separates the ymax setting and the ymin setting.

On its own the new graph doesn’t tell us anything different. So, let’s copy and paste it as before along the row.

Now we can compare the graphs with each other. If we look at the second and third one, we can now see, for example, that product C’s sales are lower than product B’s.

We can also change the way the line looks in terms of line colour and line thickness.

In B18 type:

=SPARKLINE(B3:B14,{“color”, “red”})

Now we have a red line.

There is another way to change the colour of the line. Just change the font colour of that cell.

Here, I’ve changed the font colour of the second graph to pink. Note, you can’t change a colour in this way, if you’ve already set it in the function.

In B19 type:

=SPARKLINE(B3:B14,{“linewidth”, 3})

And this time we have a thicker line. The higher the number, the thicker the line.

So, as you can see we can quickly edit the way the graphs look, just by adding more options.

---

Changing sparkline chart type

By default, the SPARKLINE function will create a line graph, but there are times when other types of chart are more suitable. There are four types of chart to choose from:

• Line
• Column
• Stacked Bar
• Winloss

To change the chart type, we just add a pair of settings like we did above.

Let’s add a column chart. In cell B20 type:

=SPRAKLINE(B3:B14,{“charttype”,”column”})

This time we have the data represented in a column chart.

There are lots of other options you can use. Let’s add red to the lowest sales figure and green to the highest one. Type in the function below in cell B21.

=SPARKLINE(B3:B14,{“charttype”, “column”;”lowcolor”,”red”;”highcolor”,”green”})

In this limited data, it’s pretty obvious which is the lowest and which is the highest, but you can see how it can be used.

The sales manager has decided to focus on product C and wants to see the month to month differences. He sets up the chart and adds a sparkline at the bottom. This time he’s colouring the times there was a negative movement in sales in red using this function:

=SPARKLINE(K5:K15,{“charttype”, “column”;”negcolor”, “red”})

As you can see it’s very clear when there was negative movement in the sales.

Alternatively, if he just wanted to know in which months there were either negative or positive movements without worrying about the actual sales figures, he could have used a winloss graph:

=SPARKLINE(K5:K15,{“charttype”, “winloss”;”negcolor”, “red”})

As you can see in the bottom graph, you can see when there was either a positive or negative movement in the sales.

---

The last chart type we have is a stacked bar graph. The complete bar is the total of all the data and then it’s divided into the parts.

This time the sales manager wants to compare the sales of Product B and C. Type in:

=SPARKLINE(N4:O4,{“charttype”,”bar”})

Then copy the function down the cells. We can see that in January, the sales were the same, but then C’s increased while B’s decreased, then the trend reversed and B’s increased and C’s decreased. So, maybe there’s some kind a connection. It’s something the sales manager needs to investigate.

You can change the colours in the graph by using “color1” and “color2” for the first and second colours respectively.

You see this type of graph a lot in sports game, when they are talking about things like possession of the two teams.

---

Here’s the Google support page on sparklines, which gives you more options for each of the graphs.

As you type in the sparkline function (as with all functions) a box pops up giving you some useful information on how to complete the function. It gives type of information needed, an example, then what the function does.

 You can find more Google Sheets tutorials here.

---