12 Simple Google Spreadsheets Sparkline Examples

I just finished a project where I was using IPython and Pandas a lot more than spreadsheets. I found that after using Pandas, I started to think about data a little differently. When I returned to Google Spreadsheets, I realized it wasn’t capable of displaying the data in the way that I wanted to. So I took another look at the Google Spreadsheet SPARKLINE function and found that, in many cases, they provide a more informative and much quicker visualization that charts. Once I got started, I figured I would exhaust the possibilities of the feature and share them here so that you can get started with them quickly and easily when you need to.

There are four different types of sparkline charts. I have a section for each:

Horizontal Bar Charts

Single Series Data

Best for:  Comparing every cell in a column


Basic Bar Chart


Google Spreadsheet Bar Chart Sparkline

It’s pretty straight forward. Make sure to set the max option so that the bar does not take up the full column.



Reverse Direction with Max Argument and Color

=sparkline(E42,{"charttype","bar";"color1","teal";"rtl",true; "max",max(C$36:C$44)})

rtl (right to left) changes the direction of the bars. Use the max function for the value of max with the data column as the range to set the width limit to the highest value in the data column.


Stacked Series Data

Best for:  Comparing multiple columns with each row as parts of whole


Stacked “Versus” Chart



Set color to hex codes for to make visually appealing sparklines. No value for max will set the width of the bar to the width of the cell.

Stacked Bar Chart


The Q1 and Q2 values for the 2014 row do not have numerical values. Setting nan to “ignore” will exclude these values from the sparkline bar chart. The max option is set to the highest value of the Total column.



Line Charts

Single Series (Y) Data

Best for:  Displaying time series data where value change over consistent time intervals

Standard Line Chart and the Effects of ymin

=SPARKLINE(B3:G3,{"charttype","line"; "color","indigo"; "linewidth",2})
=SPARKLINE(B3:G3,{"charttype","line";"color","indigo"; "ymin",0})

Sparkline Line Chart Custom Color

This chart uses a custom color and linewidth. It also demonstrates how to change the chart’s background color by changing the cell’s background color. Both charts display the same data, but because it sets the ymin option to 0, the chart looks much different. Be careful when setting limits and cell dimensions. These adjustments can make the data appear to say different things.


Line Chart with Conditional Coloring

=SPARKLINE(C7:H7,{"color",if(H7>C7,"green","red");"ymax",100; "linewidth",2})

Sparkline Line Chart with Conditional Color

The color is set to an if formula. This formula compares the first and last value of the series, and if the last value in the series is greater than the first value, the line color option is set to green; and if not, the color is set to red. The slope formula is also good for this, but it is better with X,Y series data.


Dual Series (X and Y) Data

Best for:  Displaying time series data where value change over inconsistent time intervals

Line Chart with Conditional Coloring

=SPARKLINE(C12:H13,{"color",if(slope(C13:H13,C12:H12)>0,"green","red"); "linewidth",2; "ymax",25; "xmax",15})

Sparkline Line Chart with Two Variables and X Limit

There is a lot going on here, but it builds on the previous example. The slope function is used to set the color for the line. If the slope is greater than 0 the line is green; else it is green. Additionally, ymax and xmax are set to values greater than any data value. This creates a margin around the line chart.


Line Drawing… Because you can

=SPARKLINE(C26:O27,{"color","red"; "linewidth",8})

Sparkline Line Drawing

The lines will follow the X and Y coordinates even if they are not consecutive. Go nuts!



Win/Loss Charts

Best for:  Displaying binary data like wins and losses

Custom Colors and Colored Axis


Sparkline Win Loss with Custom Color

Colors help draw attention to the data more quickly than just shapes.


Custom Colors and Colored Axis


Sparkline Win Loss with First and Last

The firstcolor and lastcolor options are a good chance to use custom coloring. These can be helpful for summaries of a series of data like whether a team was over or under .500.


Custom Colors and Colored Axis


Sparkline Win Loss with Filter

The nan and empty options determine how non-numeric or empty data is treated.  In this case, the first cell is empty, and the fifth cell is text. Both cells are ignored.



Column Charts

Best for:  Comparing a series of values against each other

Dealing with Negative Values


Google Spreadsheet Sparkline Column Chart

Column chart sparklines offer two ways to call out negative values. The first is to set the negcolor value and the second is not to display them altogether by setting the ylim value to 0.


High and Low Value Accent Colors


Google Spreadsheet Column Chart Sparkline

The highcolor and low color options make is easy to identify the high and low values in the data series.


Bonus: Hex Color Options

Google Spreadsheets is great in that it allows you to customize alot of the visual display. Here are a few nice color palettes to make you data a bit more visually pleasing. For more, checkout: http://www.colourlovers.com/palettes

#69D2E7 #A7DBD8 #E0E4CC #F38630 #FA6900

#41F4E3 #73A4D3 #F0D9A8 #70109 #EB4967

#00A0B0 #6A4A3C #CC333F #EB6841 #EDC951

#ECD078 #D95B43 #C02942 #542437 #53777A


I hope that covers any visualization that you might need to use. Let me know in the comments if you are having any trouble. Tomorrow I will publish a post on how to make funnel diagrams using the Sparkline Function.


    1. Trevor Fox

      Hi Everton, thanks for adding to the post. I have not had this type of formula parse error but I have a feeling your comment will help people in the future. Thanks!

  1. jo

    hi thank you for the post. Is there any possibility that we can draw a candlestick chart in a single cell like you explained for line chart. Ofcourse we need OHLC price. we can get it in google spreadsheet. so is there any possibility? Thanks

    1. Trevor Fox

      Hi Jo, Unfortunately, there is no candlestick chart for Google Spreadsheet Sparklines per se. You might have to get created with the options above. Sorry…

  2. howe

    Hi, Thanks for this post!
    I still need to add a tooltip over the sparkline, so do you have a way to achieve that?

  3. John Carver

    Thank you! Quick question – what if you use a sparkline to show progress in times taken to swim a certain distance. As the times get faster (less) the sparkline will appear to fall. This can be confusing for the viewer – is there an easy way to switch the direction of a sparkline so that it will appear to climb as the times get faster?

    Here’s the spreadsheet where I need to configure it.

    I’m sure it’s a really easy formula tweak – thanks if you can help!

    1. Trevor Fox

      It seems that all you should have to do is pick an arbitrary lap time limit (a really slow time) and subtract the swimmer’s time from that limit. As the swimmer’s time improves, the number subtracted from the arbitrary limit will decrease, leaving the remaining number to increase- creating the effect of a line that goes up instead of down.

      1. Guus de Ruyter

        John, building on Trevor’s suggestion, you can use an ARRAYFORMULA in the sparkilne. If you create an array of the same length as your data range and fill it with the “slow time” as Trevor mentioned, you can do the math inside the sparkline :
        Created the array and named it Dummyspeed; Taking your spreadsheet cell CW 5 and changed it to:
        …… sparkline({ARRAYFORMULA(Dummyspeed-BY5:CV5)},{“linewidth”,3;”color”,”green”}),”-“)

        In this way the sparkline drops with reducing times but the actual values you show in the cells can remain unchanged

      2. Tom

        If you want “line goes up” to represent “person gets faster at swimming”, why not express the results in terms of average speed instead? (Distance Swum / Time Taken)

  4. Nilson

    I wonder if, when it comes to sparklines, we can go actually about referring to cells by alternate columns (say, B1, D1, F1, for instance) instead of the usual “B1:F1”.

  5. Stephan

    Hi Trevor,

    Awesome article.
    I’m using the bar on my sheet but would like the bar to change colour depending on the percentage value in the preceding cell.
    In other words, if the percentage value is 10%, the bar colour would be red. If 50%, bar colour is orange and if 100%, green.

    Can you perhaps guide me in the right direction?

    Appreciate the effort.

    1. John Costanzo

      Suppose the percent value you are trying to plot is in cell H1.
      This formula will display the bar in red if 30% or below; orange if 60% or below; yellow if below 100% and green if 100%:

      =IF(H1=””,””,sparkline(H1,{“charttype”,”bar”;”max”,1; “color1”, if(H1<=0.3,"#ff0000",if(H1<=0.6,"#ff8000",if(H1<1,"#ffff00","#00ff40")))}))

  6. Watchara Karakan

    How I can make no bar progress with negative number or number less than zero
    Because -50 it’s counted as 50 and then it shows a half of full bar

  7. Jens

    Nice SPARKLINE article by the way 🙂
    Is it possible to change the direction of the line. Let’s say I have created a spreadsheet where I want to keep track of my keyword positions and in this case ranking 1 is much better than ranking 10. So I want the Sparkline to point upward if the number is 1. Right now the Sparkline will point downwards if we go from position 5 to 1.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.