Data & Analytics

|

November 20, 2020

A Beginner's Guide to the SPARKLINE Function in Google Sheets

Sparkline Chart example on Google Sheets
SECTIONS

1. Line charts

  1. Adding minimum and maximum value
  2. Changing the chart color
  3. Changing line thickness

2. Column charts

  1. Specify the color of the tallest and shortest column
  2. Add an axis and choose the axis color

3. Bar charts

  1. Use a bar chart to visualize percentages
  2. Specify a different pair of alternating colors

4. Win-loss charts

  1. Specify color of positive and negative values

5. A trick for managing SPARKLINE charts

6. Summary + a sample sheet

SECTIONS

1. Line charts

  1. Adding minimum and maximum value
  2. Changing the chart color
  3. Changing line thickness

2. Column charts

  1. Specify the color of the tallest and shortest column
  2. Add an axis and choose the axis color

3. Bar charts

  1. Use a bar chart to visualize percentages
  2. Specify a different pair of alternating colors

4. Win-loss charts

  1. Specify color of positive and negative values

5. A trick for managing SPARKLINE charts

6. Summary + a sample sheet

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!

Line charts

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:

An array containing annual sales data. Data covers 2013 to 2019.
An array containing annual sales data. Data covers 2013 to 2019.

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:

A simple sparkline chart containing a line chart.
A simple sparkline chart containing a line 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). 


Adding maximum and minimum value

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:

A simple sparkline chart containing a line chart, with the minimum and maximum values adjusted.
A simple sparkline chart containing a line chart, with the minimum and maximum values adjusted.

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:

  • The attributes are enclosed inside a set of curly braces.
  • The name of the attribute is enclosed within a pair of quotation marks, its value following the name, separated by a comma.
  • The value of the attribute may also be enclosed within a pair of quotation marks if it is a word.
  • Each attribute name-value pair is separated from the next pair with a semicolon. 

Changing the chart color

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:


A simple sparkline chart containing a line chart, with the minimum and maximum values adjusted and the line color changed to blue.
A simple sparkline chart containing a line chart, with the minimum and maximum values adjusted and the line color changed to blue.


Changing line thickness

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:

A simple sparkline chart containing a line chart, with the minimum and maximum values adjusted, the line color changed to blue, and the line thickness increased to 1.5 from 1.


Column charts

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:

A simple sparkline chart containing a column chart. 
A simple sparkline chart containing a column 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:

A simple sparkline chart containing a column chart, with the minimum and maximum values adjusted.

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:

A simple sparkline chart containing a column chart, with the minimum and maximum values adjusted, and the column color changed to a medium dark shade of green.
A simple sparkline chart containing a column chart, with the minimum and maximum values adjusted, and the column color changed to a medium-dark shade of green.

Specify the color of the tallest and shortest column

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:

 A simple sparkline chart containing a column chart, with the minimum and maximum values adjusted, and the default column color changed to a medium dark shade of green. To highlight the maximum and minimum values, the tallest column is colored blue while the shortest column is colored red. 
Now the minimum and maximum value can stand out!

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!

Add an axis and choose the axis color

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:

A simple sparkline chart containing a column chart, with the minimum and maximum values adjusted, and the default column color changed to a medium dark shade of green. To highlight the maximum and minimum values, the tallest column is colored blue while the shortest column is colored red. A black horizontal axis is also added.
Our sparkline chart, now with a horizontal axis.

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

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:

the same data used in the examples for line and column charts, but transposed. 
The same data used in the examples for line and column charts, but transposed. 

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:

a simple bar chart of the total values. 
A simple bar chart of the total values, all with the same bar graph.


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:

A simple bar chart of the total values, with the maximum value adjusted.


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

Use a bar chart to visualize percentages

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:

a bar chart with a breakdown of individual contributions to the total sales, displayed by alternating colors, with the same maximum value set.  
A bar chart with a breakdown of individual contributions to the total sales, displayed by alternating colors, with the same maximum value set.  

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:

a bar chart with a breakdown of individual contributions to the total sales, displayed by alternating colors, instead adjusted as percentage of the total sum. 
A bar chart with a breakdown of individual contributions to the total sales, displayed by alternating colors, instead adjusted as a percentage of the total sum. 

Specify a different pair of alternating colors

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:

a bar chart with a breakdown of individual contributions to the total sales, displayed by alternating colors, instead adjusted as percentage of the total sum. The alternating colors specified are gold and dark blue. 
Any UMich fans in the house?

Win-loss charts

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:

A simple win-loss chart displaying the four values. 


Specify the color of positive and negative values

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:

A simple winloss chart, but with blue color for positive values and red color for negative values. 
A simple win-loss chart, but with blue color for positive values and red color for negative values. 

A trick for managing SPARKLINE charts

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:

Original sparkline chart, now with an attribute control panel beside it.


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:

Original sparkline chart, now utilizing the control panel beside it.


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:

Original sparkline chart, now utilizing the control panel beside it.
The control panel allows us to easily change the line color to orange!


Summary plus a sample sheet

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 cm also just search for our other blogs here.

Meanwhile, click here to access the sample sheet with examples from the article.


SECTIONS

1. Line charts

  1. Adding minimum and maximum value
  2. Changing the chart color
  3. Changing line thickness

2. Column charts

  1. Specify the color of the tallest and shortest column
  2. Add an axis and choose the axis color

3. Bar charts

  1. Use a bar chart to visualize percentages
  2. Specify a different pair of alternating colors

4. Win-loss charts

  1. Specify color of positive and negative values

5. A trick for managing SPARKLINE charts

6. Summary + a sample sheet

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.