Everything you ever wanted to know about Sparklines in Google Sheets

Sparklines

  1. Introduction
  2. Sparkline Examples
  3. Sparkline Syntax
  4. Line Sparklines
  5. Column Sparklines
  6. Bar Sparklines
  7. Winloss Sparklines
  8. Option/value pairs in cells
  9. More advanced examples
  10. Sparkline issues
  11. Further reading

Introduction

Sparklines are small, lightweight charts, typically without axes, which exist inside a single cell in your spreadsheets. They’re a wonderful, quick way to visualize your data, without needing the complexity of a full-blown chart.

They were first created by interface designer Peter Zelchenko around 1998. The term “sparkline” was coined by statistician and data visualization legend Edward Tufte.

Sparkline examples

I’ve been building a lot of dashboards in Google Sheets recently, making heavy use of sparklines to show data trends.

For example, here’s a column sparkline to show website users in the past 30 days:

Column sparkline example

Then, a combination of line sparklines and bar sparklines for social media metrics:

Line sparkline example

And finally, I’ve used a winloss sparkline chart to show web outages over the past 24 hour period:

Winloss sparkline example

So let’s explore how to create these charts and customize them to meet your needs. Links to all of the examples below are available for viewing in Google Sheets.

Sparkline Syntax

The most basic sparkline in Google Sheets looks like this:

sparklines

Assuming your data is in range A1 to A8, the formula would be:

=sparkline(A1:A8)

See this example live in this Google Sheet.

More generally, the sparkline formula syntax is:

=sparkline(data,[options])

data
This refers to the dataset (the range of values) you want to plot as a sparkline.

[options]
This is an optional argument, used to specify things like chart type (e.g. column), color and other specific settings:

  • Conditions are placed inside a set of curly braces { ... }
  • Options are further enclosed in double quotes, e.g. "charttype"
  • Option values are usually enclosed in double quotes, e.g. "column" but numbers or true/false settings do not need the double quotes
  • Options and values are separated by commas
  • Multiple options/value pairs are separated by semi-colons

Example: {"charttype","column";"axis",true;"axiscolor","red"}

We’ll explore all of this below.

There are four types of sparkline charts available in Google Sheets: line charts (the default), column charts, bar charts and winloss charts.

Let’s take a look at each in turn, through a series of examples.

Line sparklines

Link to live Google Sheet with all of these examples.

This is the default choice for sparklines in Google Sheets, meaning that Google will default to showing a line if you don’t specify anything in your options.

Assume I have the following data in columns A and B of my Google Sheet. The idea here is to create a sparkline that displays the sales data from column B in a single cell, as a simple, lightweight chart without any additional details.

Sparkline line data

You’ll notice there are some blank values, some negative values and some text values in column B. That’s intentional and we’ll explore how to deal with each of those with the sparkline formula.

Default:

Since line charts are the default sparkline option, we don’t need to explicitly specify the chart type. So we can create a sparkline line chart with this simple formula:

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

sparkline line

Color option:

Change the color of your sparkline by adding the color option/value pair, as follows:

=SPARKLINE($B$2:$B$21,{"color","red"})

or using hex notation:

=SPARKLINE($B$2:$B$21,{"color","#FFA500"})

Sparkline color line

Line-width option:

The higher the number you specify, the thicker the line.

=SPARKLINE($B$2:$B$21,{"linewidth",3})

Sparkline line width

Xmax and Xmin options:

If our data includes a column of x-values, then we can then specify a minimum or maximum for that axis. So from our data above, we now include column A in our spakrline formula argument, then specify a minimum and/or maximum in our sparkline options, for example:

=SPARKLINE($A$2:$B$21,{"xmin",5;"xmax",15})

This would only include values from column B, where the values in column A are between 5 and 15.

Note: I would advise a degree of caution with the xmin and xmax criteria. They are somewhat volatile and caused one of my Google Sheets to repeatedly crash.

Ymax and Ymin options:

Similarly we can specify bounds on the y values we plot in our sparkline, by using the ymax or ymin criteria. For example to only include values zero or above we would use:

=SPARKLINE($B$2:$B$21,{"ymin",0})

To set a ymax and ymin:

=SPARKLINE($B$2:$B$21,{"ymin",10;"ymax",20})

Empty option:

Use the “empty” option to determine whether blank cells in your dataset are rendered as 0 in your sparkline, or just ignored (the datapoint is not included in your sparkline). The formulas are respectively:

=SPARKLINE($B$2:$B$21,{"empty","zero"})

=SPARKLINE($B$2:$B$21,{"empty","ignore"})

Nan option:

Similar to the empty option above, use the “nan” option to determine how non-numeric cells (text cells) in your dataset are rendered in your sparkline. The options here are to convert to 0 or to ignore, as above, and the formulas are respectively:

=SPARKLINE($B$2:$B$21,{"nan","convert"})

=SPARKLINE($B$2:$B$21,{"nan","ignore"})

Rtl option:

Want your chart to show from right-to-left? Use the option “rtl”, which can be set to true or false, to specify the direction of your sparkline.

=SPARKLINE($B$2:$B$21,{"rtl",true})

Sparkline rtl

Compare this sparkline to the first line chart and you’ll see the reversed direction:

sparkline line

Column sparklines

Link to live Google Sheet with all of these examples.

As the name suggests, column sparklines are small column charts that exist inside a single cell. For these examples, I’m using data set up as per the line chart example.

Default option:

=SPARKLINE($B$2:$B$21,{"charttype","column"})

Sparkline column chart

Color option:

=SPARKLINE($B$2:$B$21,{"charttype","column";"color","#FF0000"})

Sparkline column chart

Lowcolor option:

=SPARKLINE($B$2:$B$21,{"charttype","column";"lowcolor","red"})

Sparkline column chart

Highcolor option:

=SPARKLINE($B$2:$B$21,{"charttype","column";"highcolor","red"})

Sparkline column chart

Firstcolor and lastcolor options:

=SPARKLINE($B$2:$B$21,{"charttype","column";"firstcolor","red"})

Sparkline column chart

Similarly, you can se the lastcolor option.

Negcolor option:

=SPARKLINE($B$2:$B$21,{"charttype","column";"negcolor","red"})

Sparkline column chart

Axis and Axiscolor options:

=SPARKLINE($B$2:$B$21,{"charttype","column";"axis",true;"axiscolor","red"})

Sparkline column chart

Ymax and Ymin options:

Works the same as the line chart examples above. For example, setting a max value of 0 creates a sparkline with only the negative columns showing:

=SPARKLINE($B$2:$B$21,{"charttype","column";"ymax",0})

Sparkline column chart

Empty option:

Works the same as the line chart examples above.

Nan option:

Works the same as the line chart examples above.

Rtl option:

Works the same as the line chart examples above.

Trick to add a reference line at a set point

A reader emailed in to ask whether you could add a reference line at a set point on a column sparkline. Unfortunately you can’t; the only thing you can do is add a y-axis at 0.

However, we can use a trick and split the data in two, and create two sparklines with a border between them to achieve this effect:

Add reference lines to column sparklines

In this case, the values lie between 10 and 30, with a reference line at 20. The following formulas are used to create the split data (columns B and C):

=IF(A2<20,{A2,0},{20,A2-20})

This formula is copied into cell B2 and copied down to B21 only. It outputs an array which will populate column C. The top sparkline formulas is:

=SPARKLINE(C2:C11,{"charttype","column"})

and the lower sparkline formula is:

=SPARKLINE(B2:B11,{"charttype","column";"ymin",0})

Bar sparklines

Link to live Google Sheet with all of these examples.

You guessed it, these are small bar charts that exist inside a single cell. The usage is a little different than the line and column charts we've looked at so far though, because these are stacked bar charts. So you can point to a single cell of data and create charts based off of that, for example.

Using multiple bar sparklines to create charts:

This method works by setting a max value that applies to all your bar sparklines, using the "max" option. So step 1 is to determine a suitable maximum that works for your whole data range (consider using the MAX() formula to determine). In this example, 30 worked as my max option.

The formulas to create these sparklines are:

=SPARKLINE(A3,{"charttype","bar";"max",30})
=SPARKLINE(A4,{"charttype","bar";"max",30})
=SPARKLINE(A5,{"charttype","bar";"max",30})
etc...

Sparkline bar chart

Two series:

=SPARKLINE(A16:B16,{"charttype","bar";"max",40})

Sparkline bar chart

Two series with as stacked percentage chart:

=SPARKLINE($A27:$B27,{"charttype","bar";"max",1})

Sparkline bar chart

Two series with as stacked bar chart:

Using the sum of the two values as the "max" option ensures that the bars will always be 100% of your cell width.

=SPARKLINE($A33:$B33,{"charttype","bar";"max",sum($A33,$B33)})

Sparkline bar chart

Changing colors:

You can specify up to two colors in sparkline bar charts. The colors alternate for each new value.

=SPARKLINE($B40:$C40,{"charttype","bar";"max",SUM($B40,$C40);"color1","red";"color2","black"})

Sparkline bar chart

Other options:

Sparkline bar chart

The formulas to create these bar sparklines, in the order they appear above, are:

=sparkline($B45:$D45,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"})

=sparkline($B46:$D46,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "empty","zero"})

=sparkline($B47:$D47,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "empty","ignore"})

=sparkline($B48:$D48,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "nan","convert"})

=sparkline($B49:$D49,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "nan","ignore"})

=sparkline($B50:$D50,{"charttype","bar";"max",30;"color1","#009900";"color2","#66ff66"; "rtl",true})

Winloss charts

Link to live Google Sheet with all of these examples.

These are a special type of column chart that plots only 2 possible outcomes: positive and negative (e.g. like a coin toss, heads or tails).

Here's the dataset for the following examples. The winloss sparkline is only concerned with whether a datapoint is positive or negative, it doesn't take relative sizes into account. So, in effect, it's really like a column chart of -1's and 1's.

Winloss sparkline data

Default winloss sparkline:

=SPARKLINE($B$2:$B$21,{"charttype","winloss"})

Sparkline winloss chart

Color option:

=SPARKLINE($B$2:$B$21,{"charttype","winloss";"color","red"})

Sparkline winloss chart

Lowcolor/Highcolor option:

Even though the winloss chart displays all the "columns" the same height, it is able to highlight the highest and lowest values in your original dataset. For example, the following formula colors the lowest values red (in the case of this dataset, that's the three -10 values):

=SPARKLINE($B$2:$B$21,{"charttype","winloss";"lowcolor","red"})

Sparkline winloss chart

Firstcolor option:

Highlights the first value of the dataset:

=SPARKLINE($B$2:$B$21,{"charttype","winloss";"firstcolor","red"})

Sparkline winloss chart

Lastcolor option:

=SPARKLINE($B$2:$B$21,{"charttype","winloss";"lastcolor","red"})

Sparkline winloss chart

Negcolor option:

Highlights all the negative values:

=SPARKLINE($B$2:$B$21,{"charttype","winloss";"negcolor","red"})

Sparkline winloss chart

Axis and Axiscolor options:

=SPARKLINE($B$2:$B$21,{"charttype","winloss";"axis",true; "axiscolor","red"})

Sparkline winloss chart

Empty option:

Works the same as the line chart examples above.

Nan option:

Works the same as the line chart examples above.

Rtl option:

Works the same as the line chart examples above.

Option/value pairs in cells

Link to live Google Sheet with all of these examples.

So far, we've encoded the options as an array of text strings inside of the sparkline formula, between the curly braces, e.g.: {"charttype","column";"axis",true;"axiscolor","red"}

However, we can instead list our option/value pairs in a range of cells in our spreadsheet, and then reference them in the sparkline formula.

Consider a dataset and option/value pairs setup as follows:

Sparkline options in cell

Then enter this formula into cell F2:

=sparkline($A$2:$A$21,{C2,D2;C3,D3})

to create your sparkline. We still need to enclose the options in the curly braces, { ... }, and separate the options and values with commas and semi-colons, but notice how we've used cell references instead of text strings this time.

The output of this formula is the following sparkline, in cell F2:

Sparkline column

We can take this even further by putting all our options into two columns and then referencing those ranges:

Sparkline options in cell

The formula in this case is then:

=sparkline($A$2:$A$21,{C2:C9,D2:D9})

where my range of option/value pairs extend down to row 9 (adjust as needed to accommodate different number of options).

Lastly, we could get fancy by adding a data validation drop down menu to pick from the option/value pairs, e.g. for charttype, as shown in the following GIF:

sparkline data validation

More advanced sparkline examples

Link to live Google Sheet with all of these examples.

You can nest other formulas inside of sparklines, for example you can quickly and easily plot stock prices using the sparkline formula.

Let's create sparklines for the big tech stocks in 2016, like so:

Sparkline stock prices

We create these charts by first using the GOOGLEFINANCE() function to get the stock prices from January 1, 2016 (using the DATE() formula) to today (using the TODAY() formula):

=GOOGLEFINANCE($B3,"price",DATE(2016,1,1),TODAY(),"DAILY")

Then we nest this inside the sparkline formula to chart this data:

=sparkline(GOOGLEFINANCE($B3,"price",DATE(2016,1,1),TODAY(),"DAILY"),{"charttype","line";"linewidth",2;"color","#5f88cc"})

Cool huh!

We can also turn this same data into column charts, but we need to finesse the GOOGLEFINANCE data since we only want the second column of data. We do this with the QUERY() function:

=query(GOOGLEFINANCE($B13,"price",DATE(2016,1,1),TODAY(),"DAILY"),"select Col2",-1)

Then we nest this inside the sparkline formula:

=sparkline(query(GOOGLEFINANCE($B13,"price",DATE(2016,1,1),TODAY(),"DAILY"),"select Col2",-1),{"charttype","column";"highcolor","red"})

I've also highlighted the highest stock price in our period, using red shading. The output is as follows:

Sparkline stock prices

Sparkline Issues:

I've come across two issues using sparklines, one of which I mentioned above.

Issue 1

Using the xmin and xmax options together in a line chart caused Google Sheets to repeatedly crash.

This formula:

=SPARKLINE($A$2:$B$21,{"xmin",5;"xmax",15})

caused this issue:

Sparkline crashing Google Sheets

Issue 2

If you publish your Google Sheet to the web, then your sparklines will disappear unfortunately!

I've reported both these issues to the Google support team so I'll keep you posted if there are any updates.

Further reading

Official Google Documentation.
Sparklines on Wikipedia.
Edward Tufte's detailed take on sparklines.
Sparklines can be implemented in Microsoft Excel too.

69 thoughts on “Everything you ever wanted to know about Sparklines in Google Sheets”

  1. Hi Ben,

    I wonder if sparklines can be used to show the distribution of data. I find myself in many situations in which I’m looking at really basic statistics, but I may be missing out on the real data by looking at the distribution of data.

    If I could easily see where there’s some significant normal or chi distribution, I would save time doing graphs one by one and straigh away see where there are interesting things to dig into!

    Any ideas?

  2. Hi Ben

    Really useful.

    Can a series show “highcolor” and “lowcolor” at the same time? I’m looking to use both with this formula.

    Regards – Nigel.

    sparkline(query(GOOGLEFINANCE($A$5,”price”,DATE(2016,1,1),TODAY(),”DAILY”),”select Col2″,-1),{“charttype”,”column”;”highcolor”,”red”})

    1. Hi Nigel, you can add the lowcolor option after the highcolor, like so:

      =sparkline(query(GOOGLEFINANCE($A$5,”price”,DATE(2016,1,1),TODAY(),”DAILY”),”select Col2″,-1),{“charttype”,”column”;”highcolor”,”red”;"lowcolor","orange"})

      However, you probably won’t be able to see the lowcolor on your sparkline, because it’s so close to the axis as to be invisible. If you try with a smaller date range, you should be able to see the effect, e.g. last week prices for Google ticker:

      =sparkline(query(GOOGLEFINANCE("GOOG","price",today()-7,today(),"DAILY"),"select Col2 offset 1"),{"charttype","column";"highcolor","red";"lowcolor","orange"})

      Hope that helps!

      Ben

  3. =SPARKLINE(I3:K3,{“linewidth”,3})

    I can´t understand why it is not working in my spreadsheet.

    When I create the sparkline above it shows me an #ERRO!

      1. Hi Renato, Not sure why that’s not working for you. I’ve just tried:
        =sparkline(I3:K3,{"linewidth",3})
        and it works for me in my Google Sheet. Feel free to share the sheet if you want.

        Ben

      1. Great suggestion Stefan, thanks! The use of comma’s or semi colons as separators in your formulas depends on your locale. Hopefully this fixes the issue for you.

        1. Hi Ben,
          I experience the same problem as Renato.
          I’ll get a message saying: “Function SPARKLINE parameter 2 has incorrect column size. Expected: 2. Actual: 1.”
          I’ve tried to both change the column size and to merge two cells, but it doesn’t work. Hope you have a suggestion.
          /Ken

          1. Hey Ken,

            Can you share your formula? Where are you based? In some of the European countries you’ll need to use “;” instead of “,” in the formulas.

            So try this:

            =sparkline(range ; { conditions })

            I’ve even seen an example where the back slash was used to separate conditions inside the sparkline, like this:

            =sparkline(range ; { "charttype"\"column" ; "color"\"red" })

            Hope that helps!

            Ben

  4. Hi Ben,
    Is it possible to highlight/color out of range values? Like for a specified max or min value (sort of like highcolor and lowcolor will do, but for as many high or low values outside of a certain range as are in the data)?
    I’m thinking of charting lab results, where a “normal” range is expected and I’d like to highlight higher or lower than normal values. Could be in a line or column sparkline.
    Ideas?
    Thanks,
    Saro

    1. Hi Saro,

      Unfortunately you can’t get to this level of detail with the line or column sparklines.

      The closest I could get was with bar sparklines to highlight values above a threshold: https://docs.google.com/spreadsheets/d/1zKlMFfI6-K9RywMze-emK0VquerUnsHoQ9JpSA-V9IE/edit?usp=sharing

      Sparklines are really designed for “at-a-glance” type reading so they’re not best suited to really detailed charts. You might be better plotting with a regular stacked column chart.

      Thanks,
      Ben

      1. Hi Ben,

        Thanks for responding. I actually found a very clunky workaround that involves plotting the normal range values (max and min) as extreme datapoints, and then using xmin and xmax to hide them from the sparkline view. That way, I get two horizontal lines at the normal maximum and normal minimum, which will work for what I’m doing. Example is here: https://docs.google.com/spreadsheets/d/1IsYuFgQJxeUFQK-ynuQBeHbpifhb8qlCqyLwglKF0RI/edit?usp=sharing

        But I really like the stacked columns/bars idea, and might play with that too if I decide the colors would be easier to see that way. I just want an at a glance indicator if something was out of the expected range.

        Saro

        1. Great work Saro and thanks for sharing your worksheet! I hadn’t thought of using an array as the data for the sparkline, really interesting. I’ll have to update this post to deal with input arrays at some stage…

  5. Is it possible to create a sparkline in googlesheets with multiple ranges? I’m trying to create a cell-based column chart that does not use consecutive data (though all in the same row). If so, is there also a way to filter out certain columns based on the column headers?

    Thanks!

    1. Hey Amy,

      Yes, you can do that. After some experimentation I found using the filter function with an Array Formula will do the trick. The trick is to create an array of TRUE/FALSE corresponding to the columns you want to include/exclude. You can do this with a helper row (which has the advantage of being able to change the column selection easily) or by coding TRUE/FALSE directly in the formula.

      For example, if I had data in columns B to J and I want to include only columns B,C,F,G,H,J then I can use either of these formulas:

      =sparkline(ArrayFormula(FILTER(B13:J13,{TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE})))

      =sparkline(ArrayFormula(FILTER(B5:J5,$B$3:$J$3)))

      Here’s an image of the sheet and formula: Sparklines with array formulas

      Here’s a spreadsheet showing these formulas. Feel free to make a copy.

      Hope that helps.

      Cheers,
      Ben

      1. Ben, thanks so much for your quick and thorough reply! (And for being a genius!)

        The column headers in my spreadsheet are selected from drop-down lists, so I’m using your first suggestion (the helper row) and it’s working perfectly. All I needed was an =IF formula based on the column headers to generate the helper row.

        Greatly appreciated!

        Amy

  6. Hi Ben,

    I just started customizing Eric Bates’ highly recommended project management template (http://eric-bates.com/project-planning-template/) and wanted to find out more about sparklines. I was happy to find your very clear guide on how to use and adapt them — btw, your page is the second search result for “sparklines Google sheets” — First is Google support 🙂

    Happy too to see a compatriot making it in DC. ╭( ・ㅂ・)و ̑̑

  7. Hi
    Do you know how to create a sparkline in Google sheets that automatically updates based on last value in a column? For example if I have a column of data from A10 to AXX, where XX constantly changes, how do I create sparkline for this?

    1. Sure thing! There’s a couple of ways to do this, one easy and one that involves some more advanced formulas but is rather crafty 😉 (there are probably/possibly other ways too).

      1. Simply use the range A10:A in your sparkline formula, so it looks like this:

      Basic sparkline with dynamic range

      2. Use a formula to find the last cell in the column that is non-blank, create a string from that, create a named range for the cell with the string, use the indirect formula to refer to this string range inside your sparkline formula! Oooof, definitely harder!

      Sparkline with dynamic range using indirect formula

      Here’s the sheet with both examples.

      Feel free to make your own copy and then you can try it out.

      Cheers,
      Ben

  8. Hi Ben!

    Tx a lot for this article, it’s really helping me, i just wanna ask you one quesiton, because i was unable to do it in G spreadsheets.

    I have an excel spreadsheet that i want to convert to GSpreadsheet, in the excel i have the following bar chart: (check the screenshot, pls)
    https://www.dropbox.com/s/4339rm2v7a6wm4v/Screenshot%202016-09-23%2021.31.38.png?dl=0

    The thing is that i can’t figure it out how to display the negatives value, as in excel. There is any way to do it, or i need to go with a column chart type?

    tx, Bruno!

    1. Hey Bruno,

      Yes, you can get pretty close (minus the data labels) by using the Right-to-Left feature of bar sparklines. I use an IF formula to see if I have a positive or negative value, then choose the sparkline accordingly. Here’s the formula:

      =if(A1>0,sparkline({100,A1},{"charttype","bar";"color1","white";"color2","green";"max",300}),sparkline({200,abs(A1)},{"charttype","bar";"color1","white";"color2","red";"max",300;"rtl",true}))

      which will create sparklines like this:

      Negative bar sparklines

      Here’s a google sheet example: https://docs.google.com/spreadsheets/d/1lZJBFvjy6zmKQ7FTGioeft28VpPjekzAYg-fJXXq0II/edit?usp=sharing

      Hope that helps.

      Cheers,
      Ben

      1. Awesome. Simplified the formula a little:

        =sparkline({100,abs(A1)},{"charttype","bar";"color1","white";"color2",if(A1>0,"green","red");"max",200; "rtl",A1<0})

  9. Awesome article! Thanks so much!

    Do you know if it’s possible to put labels on the bars in a sparkline? I looked everywhere, and it doesn’t seem like a feature that exists, but I’m keeping my fingers crossed that you know how! Thanks.

    1. Hey Dave – You can’t add labels to bars on sparklines, as they’re really just for showing trends quickly.

      What you can do is add context in the surrounding cells, e.g. putting data labels into adjacent cells like so:

      Sparkline labels

      If you need to add a lot of data labels then a regular chart is the way to go.

  10. Thanks a lot! Very usefull information – I deeply recommand! Only info, I couldn’ found was, how to make sparkline bar with gradient colors, but when I looked into Google Sheet documentation, it seems it is not an option, unfortunatelly.

  11. Is it possible to show a sparkline from a Vlook up?

    The date is on one sheet and would like to show the spark line on another sheet in the same workbook?

    1. Yes, you can have data and sparklines on different sheets. As long as you give the sparkline formula a valid data range or data array, then you’re good to go!

  12. Hi Ben,
    Is it possible to change periods (e.g 6 months trend) in sparkline mini chart by drop down menu. I would like to see the actual period but changing the periods the sparklines are automatically changed.

    1. Hi Krisztina,

      You could create a drop-down menu for user input periods and then just modify the data range based of the user choice. The sparkline, based off this range, would then automatically update. Is that what you meant?

      Cheers,
      Ben

  13. Hi Ben,
    Let me specify my question. I have at least 2 countries and the figures with sparklines in one sheet, I have database on the other sheet, but the first sheet would like to change the periods and the figures and spirklines should be adjusted to the selected period showing the actual (selected period) figures and 6 months trends in sparklines for e.g 2 countries on the first sheet. with drop down menu and unique ID I have solved the figures to be updated based on the selected periods, but I am not able to link this to the sparkline. Hope it helps you.

    1. Hi Krisztina,

      Assuming your sparkline is something like this: =SPARKLINE(range,{"charttype","column"})

      then provided your range is the same range as the dynamic row of figures that updates on your dropdown, it should also update the sparkline.

      Feel free to share your sheet if you want me to take a quick look.

      Cheers,
      Ben

    1. Hey Krisztina,

      I think this is the formula you’re after (this one goes in row 8 of your ‘MPD one page’ tab):

      =ArrayFormula(sparkline(vlookup(B8;Sparklines!B:I;{2\3\4\5\6\7\8};false);{"charttype"\"column";"color"\"#00b050";"empty"\"ignore";"nan"\"convert";"negcolor"\"#ff0000"}))

      This will dynamically extract the data range from the Sparklines tab. Hope that helps!

      Thanks,
      Ben

  14. Ben,

    Is there a way to use a line chart, but it has an axis at 0? So you can quickly see above and below 0? This will help with a point of reference, as to whether the ups and downs are above or below 0.

    Make sense?

    Thanks!

    1. Great question Jeffrey!

      Unfortunately there’s no way to do this with the line sparkline (although you can do it with the column sparkline). However, you could create two sparklines, one for the positive values and one for the negative values, and have them in two cells, like this:

      The formulas to do this make use of the ymin and ymax attributes of the line sparkline:

      =sparkline($E$22:$E$28,{"charttype","line";"ymin",0;"color","green";"linewidth",2})

      and for the negative numbers:

      =sparkline($E$22:$E$28,{"charttype","line";"ymax",0;"color","red";"linewidth",2})

      Cheers,
      Ben

  15. Dear,

    i have data in A2,C2,E2,G2
    the below function didnt work with me
    =sparkline(ArrayFormula(FILTER(B13:J13,{TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE})))

    =sparkline(ArrayFormula(FILTER(B5:J5,$B$3:$J$3)))

    can you help

  16. Ben,

    Thanks for the helpful guide–I find myself referring back to it often. And your responsiveness in the comments! Two-part comment/question for you:

    1. Seemed to me win/loss data could also (better?) be represented with bars take up all of the vertical space, and differ only in color: same info with more salience / less wasted space. If it’s helpful to you/others, this is possible by coding wins as a positive high value, and losses as a positive low value–here, 2 & 1 respectively:
    =SPARKLINE({2,2,1,2,2},{"charttype","column";"lowcolor","red";"ymax",1})

    2. In the process of figuring that out, I came across what seems to be a bug: ymax/highcolor and ymin/lowcolor don’t seem to play well together. A simple way to see this is with this sparkline, which highlights the 2-bar rather than the 3-bar.
    =SPARKLINE({1,2,3},{"charttype","column";"highcolor","red";"ymax",2})
    Is that something you’ve experienced as well? Know any workarounds, or have a direct line to Google?

    Thanks again!
    Pepe

    1. Hey Pepe,

      Thanks for your comments. To illustrate the different options on point 1 for anyone else, this is what they look like:

      where the formulas are, respectively:

      =SPARKLINE({2,2,1,2,2},{"charttype","column";"lowcolor","red";"ymax",1})
      =SPARKLINE({2,2,-1,2,2},{"charttype","winloss";"lowcolor","red"})
      =SPARKLINE({2,2,-1,2,2},{"charttype","winloss";"lowcolor","red";"axis",true;"axiscolor","black"})

      I like it, it stands out more although you lose the impact of losses being negative. I think the winloss with the axis is a good representation, especially if you can widen the row.

      For point 2, I wish I had a direct line into Google! 😜 It does indeed look like the sparkeline formula selects the highcolor after applying the y-axis value of 2, but then recognizes the last value as different, but not larger. Interesting. You could try reporting the issue through the sheet help menu: Help > Report a problem…

      Thanks!

      Ben

  17. Nice–great to see all the options visualized so clearly and concisely.

    I’m kicking myself for commenting slightly too soon, though–I’ve realized since my post that because of the problem in (2), the method in (1) is unreliable–it breaks down if if there are any zeros/blanks:

    –This works: =SPARKLINE({2, 2, 1, 2, 2},{"charttype","column"; "lowcolor","red"; "ymax",1})

    –This doesn’t! =SPARKLINE({2, 0, 1, 2, 2},{"charttype","column"; "lowcolor","red"; "ymax",1})

    The auto-scaling also messing things up if there are all wins or all losses.

    A seemingly-reliable workaround uses different values: “win” = some value (1.00), “loss” = a visually indistinguishable amount more (1.01). The exact “loss” amount gets a highcolor. Adding in ymin solves the autoscaling issue. So for the same win-blank-loss-win-win sequence, it’s:

    –This (always?!) works:
    =SPARKLINE({1, 0, 1.01, 1, 1},{"charttype","column"; "highcolor","red"; "ymin",0; "ymax",1.01})

    Way more complicated than it should be, and of equivocal value, but perhaps that will help someone somewhere!

    Pepe

  18. Is there a formula for a bar graph to show the differences between dates? I’m trying to develop a chart showing dates of different projects but I can’t figure out the sparkline formula to show date calculations.

    1. Hey Christian,

      You can subtract dates to get the days between them, and then use this value in your bar sparkline. Subtracting dates is the same as subtracting ordinary numbers (because dates are in fact stored as numbers in the background).

      If you want to actually use dates in your chart in some way, then you’ll want to use a regular chart since sparklines are very lightweight and deliberately limited.

      Cheers,
      Ben

  19. Hi Ben,

    Very useful article.

    One thing: I would like to integrate my COLUMN-sparklines into a Google-presentation-file (where I can update the chart with one click – like in other cases) – but I can not find a way to do this. It seems, that it works only by integrating the “classic” line-sparkline-chart/diagram. Do you have any ideas how I could get this done?

    Thanks.

    Dan

    1. Hey Dan,

      This is only possible with the regular Sheets charts, not the sparklines unfortunately. You can create a regular chart without axes to look like a sparkline and then embed that maybe?

      Cheers,
      Ben

  20. Is there a way to create a column header array formula for sparklines? I have data being submitted from google forms and would like to use a single formula to create sparklines automatically down a column instead of having to have a formula in each cell

    1. Hey Cory,

      Great question! Unfortunately I’ve not found a way to get the sparkline formula to work this way. I got as far as this, but it simply prints out the formula as text sadly:

      =ArrayFormula(IF(ISBLANK($C:C),"","=sparkline(A"&row(C:C)&":C"&row(C:C)&")"))

      This assumes I have data in columns A, B and C coming from my form.

      I’ve written this article about Forms and Array formulas, if you want to try to take this further. If you get something to work, I’d love to hear how you did it!

      Cheers,
      Ben

  21. Hi there,

    When I use column sparklines and want to ensure my entire worksheet has a comparison of value across all the data (so for e.g. an $18k in the row above should show taller in the column chart than a $9k in the row below it), how do I adjust the axis setting within a Google Sheets doc? Please see video example of what I’m trying to achieve in normal excel (not within Google Sheets) – https://www.screencast.com/t/HShztzJCNY

    1. Hi Shanthini,

      Use the ymax attribute to standardize your axes, like this:

      {..... ;"ymax", 18000; ....}

      Hope that helps!

      Cheers,
      Ben

  22. Is there a way to reverse the y-axis (vertical) so that a higher number is bad?

    For example, for Google search rankings, a #1 position is the best, while a #20 position would not be good. But a sparkline shows 20 as higher = better.

    Workaround would be a bar chart, showing negatives a green, but would be great to see a solution as a Sparkline!

    1. Hey Joe,

      You could try just multiplying your range by -1 with an array formula? Something like this:

      =ArrayFormula(sparkline(A1:A10*-1,{"charttype","column"}))

      Feel free to share your Sheet if you still have a specific question.

      Cheers,
      Ben

  23. Hi, Ben,

    Please refer the google spreadsheet data. Link shared below.

    I want the sparkline bar chart with an in cell of below condition

    1. comparison between LTP, vs S1, S2 and R1, R2; where LTP is a middle point and .S2, S1 as lower band and R1, R2 as the higher band. and the chart colour moves according to LTP price. All the condition in one cell. (Example- if LTP Price increase to words R1, R2 then Green should grow and vice versa.)

    2. comparison between PIVOT and LTP where LTP price is on another sheet and PIVOT also.(Example- sheet1 having LTP Price and sheet2 have pivot price)

    Google Spreadsheet LInk
    =========================
    https://docs.google.com/spreadsheets/d/1uGjZeoyxWcW_qzsBOMw1iQ_EvJUn7Z0tg1sytmrFzfA/edit?usp=sharing

    ScreenShot
    ==================
    https://lh3.googleusercontent.com/-K563yPxNnrQ/WceFZCm4yGI/AAAAAAAAH3g/ynsn4K6WqCUewydnnJgKGCIksm8AB9BlACLcBGAs/s1600/vs.JPG

    Thank you,

Leave a Reply

Your email address will not be published. Required fields are marked *