How to use the Google Sheets SPARKLINE function

If you want a quick way to analyze your spreadsheet data visually without building full charts, the SPARKLINE function in Google Sheets is a great solution. It allows you to create mini charts inside individual cells, so you can quickly visualize trends.

Take a look at the example below. Imagine I’m a teacher and I want to analyze my students’ test scores:

As you can see, the results don’t exactly jump out at you from a sea of numbers. And when the data extends further across the spreadsheet, it becomes increasingly difficult to see how your students are doing over time. If I have 100 students who have all taken 15 tests, there’s no way I can analyze the trends with my bare eyes.

It isn’t feasible, time-wise, to build a chart for every single student, but that’s where SPARKLINE function in Google Sheets comes to the rescue. The function allows you to create a mini sparkline chart in a single cell. There are four main types:

• Line sparkline — this is the default sparkline in Google Sheets
• Column sparkline  — like a mini column chart in one cell
• Bar sparkline — a mini bar chart in one cell
• Winloss sparkline — a mini column chart for yes/no or positive/negative results

Syntax

• data – this is the reference to the range of cells that you want to plot as a sparkline
• options – these are used to customize the chart. Google has a full list here. These can be applied in two ways:
  • As an array of option key and option value pairs, following the data parameter.
  • As an address reference to a two column range, where the first column cells hold the option keys, and the second column cells hold the corresponding option values.

How to use SPARKLINE function

SPARKLINE(data_range)

To accommodate the sparklines, I will insert a blank column after the Name column and give it a header name ‘Trend’. Now, in cell B2, I’ll type in the function =SPARKLINE(C2:F2):

Hit the Enter key, and the Sparkline appears in cell B2. As a result, the miniature version of a chart is nicely embedded within the area of the cell.

To create a sparklines for each and every student, just drag the formula all the way down (or copy paste):

This sparkline make it way easier for you to quickly interpret large datasets. In this example, I can spot upward or downward trends for my students much more quickly than analyzing each student’s scores individually.

SPARKLINE(data_range, color_option as key value pair)

Next I’ll show you how to change the color of the lines in the sparkline. Notice that both the key (“color”) and value (“red”) are within double quotes, and that they are within curly braces/brackets that indicate an array of key value pairs:

The formula here is =Sparkline(C2:F2,{“color”,”red”}).

SPARKLINE(data_range, [color_option and line_thickness as key value pairs])

If the lines are too thin, you can simply increase the width. To do that you need to include an additional option key-value pair, as shown below. Notice that the two key-value pairs are separated by a semi-colon.

The formula here is =Sparkline(C2:F2,{“color”,”red”;”linewidth”,3}).

SPARKLINE(data_range, [column chart_type, other color_options as key value pairs])

What if you don’t want line graphs at all? You can change the sparkline to other types of chart. Here I’ll try a column chart and different colors. As you can see, it’s probably not the most attractive or readable sparkline, but it gives you an idea of what you can do.

You can also use Hex codes to indicate the colors, like this:

Win/Loss SPARKLINE

At this point you’ve seen examples of both the line and column types of sparklines. What about the win/loss chart type? This is useful for binary data – items that are either ‘yes’ or ‘no’, 1 or 0, heads or tails, etc.

In the example below, I’ve changed the data to a binary value of 1 (if the student passed the test) or 0 (if they failed). By using the win/loss format, you can quickly analyze this type of data.

Barchart for quick data visualization

In this example, I’ll show how to use the bar chart sparkline type to quickly visualize data in a simple way. This is a basic example, but a great way to put simple one-dimensional data into an easy-to-view format.

This is done by typing =Sparkline(B2,{“charttype”,”bar”;”max”,100}).

Chart options referenced in other cells

In this example, instead of hardcoding the chart options into the formula, I’ll put the options into a table that will be referenced in the function. This allows for easy changing of the options. It’s especially useful if you want to play around with different options to see what looks best.

That’s it, that’s how you use the SPARKLINE function in Google Sheets. It has a ton of customization options and it’s a really handy visualization tool to make your spreadsheets more user-friendly and make the data easier to read.

Want to know how to insert charts in Google Sheets or make larger charts with your data however? Read our blog posts on how do I insert a chart in Google Sheets and how to create a dashboard in Google Sheets.