Create a Conditional Sparkline that Changes Color based on Data

Sarmad Gardezi

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.

More great articles

Use Formula to Copy Row Dynamically in Google Sheet

This tip is one of the most challenging Tip I've shared but it's worth the effort to learn. The index…

Read Story

Create a Popup Window in Website using CSS

Use the HTML <form> tag add a form for user input. Then, create your <input> fields for your form. Give a class to your form to add a style for…

Read Story

Number the Headings in Google Docs using Google App Script

Is it possible to number the headings in a Google Docs/Drive document? Yes you can automatically place the numbers in…

Read Story
Arrow-up