With the help of the below examples that include images, you can learn all the available Sparkline Bar chart formula options in Google Sheets.
In the below SPARKLINE formula syntax, the “options” inside the square brackets are optional. I am going to elaborate on that “options” in this post.
SPARKLINE(data, [options])By default, the Sparkline formula will plot/return the Line chart. So you must use the option “
Each option in the formula must be separated by a semicolon. Also, the Sparkline bar chart formula options must put inside curly brackets. Refer the below two formulas to understand this.
In the first formula, the max value which is set to 100 will help to render the bar in the full cell.
That means if the value in cell A2 is 100 you can see the bar filled the cell else proportionate to the value in cell A2. It’s a must to use the “max” option in a single series bar chart.
In the second formula, since there are multiple values (stacked Sparkline bar chart), you can omit the max option. If omitted the formula will take the Sum of all the values in the series as the max value.
All the Sparkline Bar Chart formula options are detailed below under separate subtitles.
Sparkline Bar Chart Formula Options as Cell Reference
In the first few examples, instead of directly using the Sparkline options within the formula, I am using them as cell references. Don’t worry, I will show you how to use the options within the formula too.
I have all the Sparkline Bar chart formula options entered in the range A3
I have the below Sparkline formula in cell E4 which is dragged to down until cell E7.
=sparkline(D4,{$A$3:$B$9})This is the simplest way of using all the options in the Sparkline formula in Google Docs Sheets.
If you manually type the options, the formula in cell E4 will be as follows.
=sparkline(D4,{"charttype","bar";"rtl",FALSE;"empty","zero";"nan","convert";"color1","red";"color2","blue";"max",MAX($D$4:$D$9)})Now time to learn the options used in the formula leaving the “charttype” which is obviously the “bar”.
“rtl” Bar Chart Option in Sparkline in Google Sheets
Use the Boolean FALSE or TRUE to change chart rendering from right to left.
rtl set to TRUE means render the bar right to left.
“empty” Sparkline Bar Chart Option
The “empty” bar chart option in Sparkline function controls empty cells using the values “zero” or “ignore”.
For this example, I have deleted the value in cell D6. You can set empty to “zero” to remove #N/A error in the formula applied cell.
Let’s move to the next chart option which is “nan”.
“nan” Option in Sparkline Bar Chart Formula
In a Sparkline Bar chart, this option determines what to do with the non-numeric data. If a cell that referred in the formula contains text, you can use the convert “nan” option.
“convert” and “ignore” are the supporting options in “nan”.
We will discuss the color options separately. Before that let me clarify what is the option “max” does in a Sparkline Bar chart.
“max” Bar Chart Option
This option sets the max value in the horizontal axis in the chart.
As an example, consider a mini single series bar chart that shows the marks of a set of students out of 50. In this case, you can set the “max” option to 50.
Note: Formula in cell C2 copied down.
The bar will be rendered in the full cell wherever the students’ marks are 50. If the mark is 25, then the bar will be rendered in half of the cell.
If the data (see the syntax on the top) contains multiple values, by default, the max value will be set to the sum of the values.
For example, in the below formula you can exclude the max option which is underlined.
In my example, see the second screenshot in this tutorial above, you can see the value 64000 in cell B9.
I have used the below Max formula (not the “max” chart option) in cell B9 to render the chart proportionately to the max value in the range D4:D7.
=MAX(D4:D7)What you need to do is change the above formula as below in cell B9 and see the difference.
=MAX(D4:D7)“color” Bar Sparkline Chart Option in Sheets
Color is one of the options that you may want to pay much attention in Sparkline charts. There are the options “color1” and “color2” which determines the color of bars.
That means, in a bar chart using Sparkline, you can customize the color of the stacked bars (bar 1 and bar 2).
color1 – To set the first color used for bars in the chart.
color2 – To set the second color used for bars in the chart.
In the Sparkline Bar charts, we can use color hex codes instead of color names. For example we can replace “red” with “#FF0000”.
You can do a Google search to find the hex color codes of your choice. Also, you can use tiny free tools like Instant Eyedropper to identify any pixel on the screen and copy the code.
Conditional Coloring of Bars in Sparkline Using IF Statement
You can dynamically change the color of bars in a stacked Sparkline bar chart. I have used the IF logical function to conditionally change colors of a Sparkline bar chart.
=SPARKLINE(D16:E16,{"charttype","bar";"color1",if(D16>E16,"red","green");"color2",if(E16>D16,"red","green")})The value in D16 is 50 and E16 is 10. See how the coloring changes based on the values.
By effectively using the “color” option, we can create a GANTT chart in Sheets. Here is that must to read tutorial – Create a Gantt Chart Using Sparkline in Google Sheets.
Related Reading:
Hello!
I have a formula:
=SPARKLINE (G30;{"charttype";"column";"color1";IF(G30>75%;"Blue")})However, I’m encountering a #REF error. Can you help me correct my formula? Thank you.
Hi Ica,
For the correct syntax, please refer to this guide: Sparkline Column Chart Options in Google Sheets.
In that syntax, you need to replace
,with\.Hello,
I am a teacher working on creating a data sheet for report cards.
We grade off a competency level with the options ME, AE, and EU.
I was wondering if it is possible to create a progress chart with sparkline that uses the values “ME, AE, or EU” from a dropdown menu in cells to determine progress.
For now I have switched to numeric values but hope to switch to values mentioned above.
Thank you so much for your help!
Hi, Amy M,
That seems possible using a SWITCH formula that replaces the SPARKLINE data.
Here is an example.
Current Formula:
=sparkline(D5:G5,{"charttype","bar";"max",10})Replace it with the below one once you replace the numerical values with the said strings.
=sparkline(switch(D5:G5,"ME",1,"AE",2,"EU",3),{"charttype","bar";"max",10})
Feel free to adjust the SWITCH formula based on grades.
Hello,
I have made a Sparkline in comparison to the max and I would like to change the color if the value is above or below the average.
=SPARKLINE((RECHERCHEV($A$1;MUFILTRE!$A$3:$B$100;2));{"charttype"\"bar";"max"\max(MUFILTRE!$A$3:$B$51);
"color"\SI(RECHERCHEV($A$1;MUFILTRE!$A$3:$B$100;2))>
MUFILTRE!$B$52\"#e00000"})
Hi, Anthony A,
I could find two issues.
1. Replace
colorwithcolor1.2. It seems the logical test is not correct.
=SPARKLINE(VLOOKUP($A$1;MUFILTRE!$A$3:$B$100;2;false);{"charttype"\"bar";
"color1"\if(VLOOKUP($A$1;MUFILTRE!$A$3:$B$100;2;
false)>MUFILTRE!$B$52;"red";"blue");
"max"\max(MUFILTRE!$A$3:$B$51)})
Hello Prasanth,
I keep getting ERROR!
The only result I get is when I input =Sparkline(AH4:AJ4), which is the line chart, but I want the bar chart…… I´ve tried everything.
Hi, Romina,
Consider sharing your Sheet URL below after removing personal, sensitive, and confidential info, if any.
N.B.:- I won’t publish it (the link).
Hello Prasanth,
Column I = start time, Column J = end time and Column K = Duration (max 3 hours).
What I intend that:
When duration is above 3 hours in column K, the sparkline filled with a color (say yellow).
Less than 3 hours, a combination of two colors showing complete (yellow) Vs Rest (red).
I didn’t expect I would be receiving a reply.
Thanks for your prompt reply.
Hi, Rajkumar Rishikesh Sinha,
I think we were very close. Let’s include one more “option” which is “Max”.
Instead of my previous SPARKLINE formula, try this new one.
=sparkline({K3,3/24},{"charttype","bar";"color1","yellow";"color2","red";"max",3/24})Hello Prasanth,
It is working perfectly.
If possible, can you please explain what ‘3/24’ is doing. I see it is converting to a percentage.
[I invested complete 2 days working on it. Read a lot of articles written by you.]
Thanks again.
Hi, Rajkumar Rishikesh Sinha,
Sorry for not explaining.
If you directly use 3, it will be treated as 3 days, not 3 hours. So dividing 3 by 24, we are actually formatting it to hours.
Note: Just format the cell that you get the percentage value to duration (Format > Number > Duration) and see what it returns.
Hi,
Start time | End time | Duration
13/08/2020 10:21:04 | 13/08/2020 13:06:42 | 02:45:38
The allotted time period is 3 hours of the Start time. How to show a sparkline (if possible with arrayformula):
Duration Vs Allotted time (Start time + 3 hours).
Thanks.
Hi, Rajkumar Rishikesh Sinha,
Here is my sparkline bar chart formula which should be used in cell D2.
=sparkline({C2,3/24},{"charttype","bar";"color1","red";"color2","green"})I assume the timestamp 13/08/2020 10:21:04 (start time) is in cell A2, 13/08/2020 13:06:42 (end time) is in cell B2, and the duration 2:45:38 is in cell C2.
If you have further values down the rows, you may copy-paste the formula down (won’t expand using ArrayFormula).