Create a Conditional Sparkline that Changes Color based on Data

The Sparkline function is one of my favorite functions in Google Sheets, so I’m excited to share this tip.  Create a dynamic chart in google sheets. Forget about using Charts & table in google sheets.

Sparklines are small charts that exist inside a single cell. They’re created with the SPARKLINE function in Google Sheets.

Let’s see how to create a sparkline that changes color based on the underlying data.

In this scenario, the values in column A vary between 1 and 10 and I want values greater than a threshold value of 6 to turn green:

The formula in column B to create these charts is:

=SPARKLINE( A1 , {"charttype","bar" ; "max",10 ; "color1", IF(A1<7,"red","green") })

You can see the IF function inserted as the option for the “color1” setting of the sparkline bar chart.

Feel free to modify this IF statement with different threshold values and/or colors (you may need to adjust the “max” setting as well).

If you want to have multiple tiers of colors, you can nest an IFS function instead of a single IF, for example:

=SPARKLINE(A1,{"charttype","bar" ; "max",100 ; "color1", IFS(A1<25 , "red" , A1<75 , "black" , A1<=100 , "green")})

This formula, applied to numbers between 1 and 100, gives an output like this:

Using multiple bar sparklines to create charts:

Two series:

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

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})

Try to use them & let me know the feedback twitter / email.

Leave a Reply

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