Google Sheets sparkline bar graph horizontal

user_2714670 0
Viewed 1698

I'm trying to create a sparkline bar graph where the bars appear horizontally, like so:

enter image description here

But I can only find the option to display the graph bars vertically.

Any suggestions on how I would be able to achieve this, where negative values appear to the left of the positive values?

google-sheets-api google-sheets
1 Answers

No, sadly, there is no vertical version of the "winloss" Sparkline.

However, you can simulate this with a bunch of "bar" Sparklines. E.g. in B1, you can put:

=SPARKLINE(
{
  IF(A1>0,
    ABS(MIN(A:A)),
    A1-MIN(A:A)),
  ABS(A1)
},
{
  "charttype","bar";
  "max",MAX(A:A)-MIN(A:A);
  "color1","white";
  "color2",IF(A1>0,"green","red")
})

The downside is that if you increase the number of entries, you have to drag the formula some more. However, it does interpret blank values as 0, so if you know you will never exceed a certain number of entries, you can drag it to that number of rows. (No, SPARKLINE() does not work with ArrayFormula().)

How it works:

Basically, it uses a two-color bar chart where white is the first color to give the appearance of empty space.

  • For positive values, it draws a white bar of width ABS(MIN(A:A)) then the green bar of width A1.
  • For negative values, it draws a white bar of width A1-MIN(A:A), then draws the red bar of length of width ABS(A1).
  • It automatically normalizes and adjusts to the proper width with MAX(A:A)-MIN(A:A).
user_42943990
Related