If you use charts in your spreadsheets, you might not like the idea of a chart covering cells that contain important information.
To solve this issue, Google Sheets offers the SPARKLINE function, which inserts a mini-chart inside a selected cell! The SPARKLINE function does not offer the full set of features offered by the Chart widget, but it’s nonetheless a powerful tool to spice up how your spreadsheet looks. It is also an important formula in creating a powerful Google Sheets live dashboard!
In this tutorial, we will study how they work with various examples, plus a sample sheet at the end of this tutorial that you can tinker with!
By default, the SPARKLINE function produces a line chart. As a simple example, we consider a table containing the total sales per annum. We want to plot the per annum total from the sheet below:
We note that the range of values is on the array C3:C9, and we use it as the source array for the formula:
=sparkline(C3:C9)
Producing the following chart:
The SPARKLINE function outputs the plot of the given values without annotations such as the title of the plot or the legend (especially used in charts and charts that use multiple sets of similar data).
By default, the SPARKLINE function sets the maximum value as the upper limit and the minimum value as the lower limit of the plot. We can add attributes to the function to set a different maximum and minimum value.
For the same example, we want to set the maximum value to $4,000,000 and the minimum value to $1,000,000. To do so, we write the following code:
=sparkline(C3:C9,{"ymax",4000000; "ymin",1000000})
Producing the following chart:
The ymax and ymin attributes adjusted the plot to make it look more aesthetic.
Before we go to the next section, take note of the syntax of the attributes:
{"ymax",4000000;"ymin",1000000}
Keep in mind:
You can choose another color if black is too boring. Use the attribute color and set the color of the plot. Basic color words can be used or a specific color in hex notation. For example, we want to change the plot color to blue:
=sparkline(C3:C9,{"ymax",4000000; "ymin",1000000;"color","blue"})
Producing the following chart:
If the plot is too thin, you can specify line thickness in the attributes of the SPARKLINE function by using the linewidth attribute. By default, it is specified as 1.
We can make it thicker by selecting a higher number. The number does not have to be a whole number. For our example, we select 1.5 as line thickness:
=sparkline(C3:C9,{"ymax",4000000;"ymin", 1000000;"color","blue";"linewidth",1.5})
Producing the following chart:
Another type of chart that the SPARKLINE function can create is the column chart. To specify the column chart as the chart type for plotting the same dataset, add the attribute charttype with the value column:
=sparkline(C3:C9,{"charttype","column"})
Producing the following chart:
To adjust the heights of the columns, use the ymax and ymin functions as specified in the previous section:
=sparkline(C3:C9,{"charttype","column"; "ymax",4000000;"ymin",1000000})
Producing the following chart:
We can also specify the color of the columns. For our example we will specify a specific shade of green (#338833):
=sparkline(C3:C9,{"charttype","column";"color", "#338833";"ymax",4000000;"ymin",1000000})
Producing the following chart:
If you want the tallest and/or shortest column to stand out, you can specify their color. To do so, we use the highcolor and lowcolor attributes for the color of the tallest and shortest column, respectively. For our example, we wish to color the tallest column blue and the shortest column red:
=sparkline(C3:C9,{"charttype","column";"color", "#338833";"highcolor","blue";"lowcolor","red"})
Producing the following chart:
Just a reminder: the ymax and ymin attributes interfere with the highcolor and lowcolor attributes, so do not combine them in the same sparkline chart!
You can add a horizontal axis and choose its color by using the axis and axiscolor attributes, respectively. To add a horizontal axis, set the value of the axis to true. The axiscolor attributes accept both basic color names and hex notation as values. For our example, we set the following:
=sparkline(C3:C9,{"charttype","column";"color","#338833";"highcolor", "blue";"lowcolor","red";"axis",true;"axiscolor","black"})
Producing the following chart:
For the sake of displaying the axis, we flip one value from the input array to a negative sign. Else the axis will not be displayed in the sparkline chart.
Bar charts work differently from line charts and column charts. Bar charts combine a set of values into a single bar of varying length within a cell, so we will add bar charts not to our merged array of cells but to a single cell beside the source cells.
We use the same dataset but transpose them, with the total sales listed in a single column:
We cannot simply add the following formula to the cells beside the target cells:
=sparkline(F9,{"charttype","bar"})
As it produces the following result, which is not what we want:
Here, we can see that the chart does not help us, at the very least, identify which has the highest value. In order to make the differences in the values appear, we will specify the maximum value by using the max attribute. For our example, we specified the maximum value as $4,000,000:
=sparkline(F9,{"charttype","bar";"max",4000000})
Producing the following result:
One note: the max value can be individually specified for each bar chart, so you can have varying lengths for each bar chart. This is not recommended, however, as this will confuse you when you check the bar charts. A technique for setting the same settings to all sparkline charts will be outlined below (under "A trick for managing SPARKLINE charts").
We can use the bar chart option to visualize what percentage of something came from a certain source. For our example, there are four branches contributing to the total annual sales. The bar chart can combine the four sources into a single bar chart. For our example, we use the following formula:
=sparkline(B9:E9,{"charttype","bar";"max",4000000})
The only difference is with the input array. The results look like this:
Now, how do we visualize the distribution by percentage contribution? We simply change the value of the max attribute. The max attribute accepts the resulting value from a basic formula such as the SUM, which we will use. We will specify the SUM of the array of cells as the max.
For our example, our formula will look like this:
=sparkline(B9:E9,{"charttype","bar";"max",SUM(B9:E9)})
Producing the following result:
You can specify a different pair of colors for the bar chart covering multiple cells. We will use the color1 and color2 for the first and second color that will appear. For our example, we will specify gold and a certain shade of dark blue as color1 and color2, respectively:
=sparkline(B9:E9,{"charttype","bar";"color1","gold"; "color2","#3333CC";"max",SUM(B9:E9)})
And the bar chart will look more appealing:
Another type of chart, relevant to businesses, is the win-loss chart. A win-loss chart tracks whether the value inside a cell is positive or negative. We will have a different example of percentages of stocks visually displayed using a win-loss chart. The formula will look like this:
=sparkline(B2:E2,{"charttype","winloss"})
And the resulting chart will look like this:
It makes more sense if we choose the color for the positive values and another color for the negative values. To do so, we use the color and negcolor attributes, respectively. For our example above, we see the color of positive values to green and the negative colors to red:
=sparkline(B2:E2,{"charttype","winloss"; "color","blue";"negcolor","red"})
And our win-loss chart will look like this:
There is a technique to easily control the attributes of the SPARKLINE charts. This involves dedicating an array of cells containing the attributes and their values.
This involves adding drop-down cells to the spreadsheet. If you do not know how to do so, review our tutorial here: How to Add Yes/No Drop-Down Lists in Google Sheets
Here are the steps in adding a “control panel” to our sparkline chart:
Step 1: Identify what kind of Sparkline chart you will use. This will help you determine what attributes shall be included in the “control panel”
Step 2: List the attributes in a column. Set the next column for the value of the attribute. If the value is a number, leave it blank. If the value is chosen from a list, add a drop-down list. You can check the tutorial linked above.
At this point, your sheet should look similar to this:
Step 3: Edit the formula by replacing the attributes with cell references. In our example, the change is from
=sparkline(C3:C9,{"ymax",4000000;"ymin", 1000000;"color","blue";"linewidth",1.5})
To... (the exact cell references will differ depending on your sheet)
=sparkline(C3:C9,{I3,J3;I4,J4;I5,J5;I6,J6})
Your sheet should now look like this:
The Sparkline chart worked exactly the same! If we change the values in our control panel, we will see that the Sparkline chart changed as well:
While the SPARKLINE function cannot match the Chart tool of Google Sheets in its features, it is still a powerful function that will help you construct a dashboard in Google Sheets. Head over to our next article on creating a dashboard in Google Sheets to apply what you learned here (coming soon)! You can also just search for our other blogs here.
Meanwhile, click here to access the sample sheet with examples from the article.
