Possible to add a third color to a 'bar' sparkline?

Question

I have three columns of data that I want to represent with sparklines as a stacked bar chart.

For instance, a row like

12  5   1

with this formula

=SPARKLINE(C2:E2,{"charttype","bar"})

shows an image like this:

As you can see, the first and third colors are the same. I would like to specify all three colors, so that the first and third colors are different.

Google's SPARKLINE Help docs indicate bar charts set two colors by default; these can be user specified as color1 and color2. They do not provide any instructions for specifying additional colors.

I have tried specifying color1, color2, AND color3, and I have tried specifying lastcolor; neither option alters the third color. I also tried setting just color, then specifying color1 and color2 as a default, to see if Sheets would default to the setting for color for the third color. That did not work either.

Is there a way to set the 3rd color to be different from the first color?

Answer 1

Although it is not documented in SPARKLINE function (Google Docs support) you can now specify more than 2 colors using the same color[#] syntax that is used for the first two colors.

For example, the following formula specifies a different color for each of the 5 bars:

=SPARKLINE(
   {1,1,1,1,1},
   {"charttype","bar";
    "color1","blue";  "color2","red"; "color3","pink";
    "color4","green"; "color5","yellow"})

Answer 2

From the question:

Is there a way to set the 3rd color to be different from the first color?

No, there isn't.

According the the SPARKLINE official help article, the options for a SPARKLINE bar chart are

For bar charts:

• "max" sets the maximum value along the horizontal axis.
• "color1" sets the first color used for bars in the chart.
• "color2" sets the second color used for bars in the chart.
• "empty" sets how to treat empty cells. Possible corresponding values include: "zero" or "ignore".
• "nan" sets how to treat cells with non-numeric data. Options are: "convert" and "ignore".
• "rtl" determines whether or not the chart is rendered right to left. Options are true or false.

As you can see, ther only two possible options for setting bar colors, color1 and color2

Answer 3

You may switch the color by add "a3bc9f" (the color code in double citation mark) to change the color.

Like this: =SPARKLINE(B4:C4,{"charttype","bar";"color1",if(B4>C4,"#dd7e6b","a3bc9f");"color2",if(C4>B4,"#dd7e6b","a3bc9f")})

Answer 4

You can't add more than two colors to the bar chart. HOWEVER, you can get a similar result with this waterfall of bar charts. Just make the first color white, and make a table of the sum of the previous values. The formula for second bar chart looks like this:

=ArrayFormula(SPARKLINE($M3:$N3,{"charttype","bar";"max",MAX($D$9:$D$16);"color1","#ffffff";"color2","#93c47d"}))