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.

Sparklinesare 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

The Best Websites to Learn Coding Online in Pakistan

Pakistan is now playing an impotent role in the developing field of IT. Many students starts their work by becoming…

Read Story

How to Capture ID in CSS Selector in Html

When I tried to build the table and tried to add colors, I encountered some problems. In Wiz Wiz I…

Read Story

Free Programming Books for Pakistani Developers

In this article i'll share a collection of interesting books from different programming spheres like web and mobile app development.…

Read Story
Arrow-up