Skip to Content

How to Use the SPARKLINE Function in Google Sheets

The SPARKLINE function in Google Sheets allows you to quickly create a miniature chart inside of a single cell.

This can be useful for making a quick visualization of data in your spreadsheet to show information in a visually appealing way.

In this tutorial, I will show how to use the SPARKLINE function in Google Sheets.

Table of Contents

Types of Sparklines

With the SPARKLINE function, there are four types of charts that you can create in your sheet. They are:

  • line – line graph (this is the default chart used by SPARKLINE)
  • bar – creates a stacked bar chart
  • column – creates a column chart
  • winloss – creates a unique kind of chart that shows 2 possible outcomes for your data (positive and negative)

By default, SPARKLINE will create a line graph, but optionally you can specify a different kind of chart in the formula.

Sparkline Syntax

The syntax of the SPARKLINE function is:

=SPARKLINE(data, [options])

  • data – the range of array that contains the data to plot in the chart
  • options – this is an optional argument where you can place additional values to customize the chart. By default, this function will create a line chart

There are a ton of options that you can apply to your chart using this function:

  • charttype – this option controls which kind of chart to display. The options include: line, bar, column, winloss (example: =SPARKLINE($A$2:$B$50,{“charttype”,“bar”})

When adding option arguments to your formula, all of your options should be wrapped in curly brackets {}.

Options occur in pairs, an option, and its corresponding value. Each option pair should be separated by semi-colons.

Example formulas:

  • =SPARKLINE($A$2:$B$50,{“charttype”,”bar”;”max”,100}))
  • =SPARKLINE(A1:A10, {“charttype”,”column”; “axis”, false; “axiscolor”, “blue”})

You can see in the example formula above that all of the option arguments are wrapped in curly brackets, and each option occurs in a pair, with a semi-colon in-between each pair. This is an important part of understanding the syntax.

The available options depend on the “charttype” that you specify. In the next section, I will cover each type of chart and the different options that you can set for each one.

Line Sparklines

You can create a customize a basic line graph with the SPARKLINE function. A line graph is the default chart type, so if you do not specify one in your formula this is the type of sparkline that will be created.

Here is an example of a line sparkline:

SPARKLINE line example

The formula used in this example is:

=SPARKLINE($B$2:$B$9)

With line sparklines, you have a few different options for customizing your chart:

  • xmin – this will set a minimum value along the horizontal axis of your chart
  • xmax – this will set a maximum value along the horizontal axis of your chart
  • ymin – this will set a minimum value along the vertical axis of your chart
  • ymax – this will set a maximum value along the vertical axis of your chart
  • color – the controls the color of the line
  • empty – here you input how you want to handle empty cells. You can choose between “zero” or “ignore”
  • nan – this controls how to treat data that is not a number. You can choose between “convert” or “ignore”
  • rtl – this will control whether your chart is rendered right to left. You can choose between true or false.
  • linewidth – this determines how thick your lines are in your chart. Larger numbers are thicker lines

Bar Sparklines

Bar sparklines are useful for creating small charts that can make data easier to understand. You can show values in a more visually appealing way by adding single-cell bar charts into your spreadsheet.

Here is an example:

SPARKLINE bar example

The formula used in this example is:

=SPARKLINE(B2,{“charttype”,“bar”;“max”,150})

In the example above each entry has its own bar and I have set a MAX value of 150 for displaying the data. It is now easier to see the trends in the data.

With bar sparklines you have a few different options for customizing your chart:

  • max – this control the maximum value along the horizontal axis
  • color1 – the first color used for the bars in the chart
  • color2 – the second color used for bars in the chart
  • empty – here you input how you want to handle empty cells. You can choose between “zero” or “ignore”
  • nan – this controls how to treat data that is not a number. You can choose between “convert” or “ignore”
  • rtl – this will control whether your chart is rendered right to left. You can choose between true or false.

Column Sparklines

Using the column chart type option you can create a small column chart inside a cell. This can be very useful for quickly summarizing a range of data with a visual.

Here is an example of a basic column chart where I have also customized the color of the chart:

SPARKLINE column example

The formula I have used in this example is:

  • =SPARKLINE(B2:B9,{“charttype”,“column”;“color”,“blue”})

With column chart types you  have these options for customization:

  • color – this is where you can set the color of the chart columns
  • lowcolor – you can choose a color for the lowest value in the chart here
  • highcolor – this is where you can set the color for the highest value in the chart
  • firstcolor – you can set the first column of the chart to a certain color here
  • lastcolor –  you can set the last column of the chart to a certain color here
  • negcolor – you can set negative columns to be a certain color here
  • empty – here you input how you want to handle empty cells. You can choose between “zero” or “ignore”
  • nan – this controls how to treat data that is not a number. You can choose between “convert” or “ignore”
  • axis – this will control whether an axis needs to be drawn. You can set this to either true or false
  • axiscolor – this will set the color of the axis
  • ymin – this will set a minimum value along the vertical axis of your chart
  • ymax – this will set a maximum value along the vertical axis of your chart
  • rtl – this will control whether your chart is rendered right to left. You can choose between true or false.

Winloss Sparklines

The last type of chart you can choose is a winloss chart. All of the columns in this kind of chart will end displaying the same height no matter what the values of the data are.

This kind of chart is only concerned if the data is positive or negative.

Here is an example of this one will look:

SPARKLINE winloss example

The formula used in this example is:

=SPARKLINE($B$2:$B$9,{“charttype”,“winloss”;“lowcolor”,“red”})

You can see in the example that no matter how large or small the value is, each value in the chart still displays the same. Negative values appear at the bottom of the chart, and positive values appear at the top. I have also added customization to display the lowest value in the chart as the color red.

With winloss sparklines you have the same customization options as for column charts:

  • color – this is where you can set the color of the chart columns
  • lowcolor – you can choose a color for the lowest value in the chart here
  • highcolor – this is where you can set the color for the highest value in the chart
  • firstcolor – you can set the first column of the chart to a certain color here
  • lastcolor –  you can set the last column of the chart to a certain color here
  • negcolor – you can set negative columns to be a certain color here
  • empty – here you input how you want to handle empty cells. You can choose between “zero” or “ignore”
  • nan – this controls how to treat data that is not a number. You can choose between “convert” or “ignore”
  • axis – this will control whether an axis needs to be drawn. You can set this to either true or false
  • axiscolor – this will set the color of the axis
  • ymin – this will set a minimum value along the vertical axis of your chart
  • ymax – this will set a maximum value along the vertical axis of your chart
  • rtl – this will control whether your chart is rendered right to left. You can choose between true or false.

Closing Thoughts

The SPARKLINE function can be very useful for adding in quick visual representations of data.

It seems like a hard-to-use formula at first, but after a little bit of learning and using it, you will find it is actually a pretty simple formula to use.

Try using it with your own data to create each of the four chart types. If you are able to create each kind of chart in your own spreadsheet, you are well on your way to understanding how to use this for future projects.

More Google Sheets Tutorials:
How to Put a Diagonal Line in Your Spreadsheet