Hi Reader,

Welcome to the Google Sheets Tips newsletter #237. Reaching your inbox on a Tuesday this week, since yesterday was a public holiday here in the U.S..

I hope 2023 is off to a flying start for you!

I'm still getting used to writing 2023, and not 2022, in my dates 😂. (The years seem to fly by these days!)

_______

➜ Google Sheets Tip #237: Mini Bullet Charts With The Sparkline Function

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

​Bullet charts are a variation of bar charts that are used to show progress of some variable within a range. They're useful because they don't take up much space. In fact, the ones we build today exist inside a single cell!

Today, we're building a simplified bullet chart using the sparkline formula. It's a nice, compact way to add progress indicators to your data in Sheets.

To keep things simple, our data lies between 0 and 100.

This will be our output, generated by formulas:

Step 1: Basic Sparkline Formula

The SPARKLINE function might just be my favorite function in Google Sheets. It's a function that outputs a chart! How amazing is that!

Suppose we're 40% the way through a task. How can we show that with a simple bullet chart?

To begin, put the values 39, 2, and 59 into cells A1, B1, and C1 respectively.

Then add this sparkline formula, where we specify the chart type as a bar chart:

The sparkline formula creates a bar chart with one block 39 units wide, then one block that is 2 wide, and finally, one block 59 wide (making 100 in total).

The 2-width block is the indicator bar. I found that using a width of 2 worked better than 1, which was too faint.

This gives the effect we're after:

(Please note, if you're based in Europe, you may require a backslash "quot; instead of a comma "," inside the curly array brackets {...} . Read more about location based syntax differences.)

Step 2: Change The Colors

This is an easy step.

Within the sparkline bar chart, you can specify a color1 and a color2. It alternates between them in the sparkline output.

I've chosen a light blue/red combination, but feel free to experiment. You can specify colors by name (for standard ones) or by hex color.

​

Step 3: Use Array Literals

Next, we use an array literal formula to generate the data rather than typing it into 3 separate cells.

Clear the contents of cells A1, B1, and C1.

Then, add this formula into cell A1:

This generates a row of data for you:

(As mentioned in Step 1, some users based in Europe will require a "quot; instead of a "," inside the curly brackets.)

Step 4: Change To A Variable Input

So far the formula is completely static. Let's change that.

Add a new column to the left of your work, and add a value into cell A1.

Then, modify your array formula (now in cell B1) to this:

​

Step 5: Nest Inside Sparkline Formula

Finally, nest this array formula inside the sparkline formula, as the data argument:

And there we go!

You can copy this formula down your column to create the output we saw at the top of the page.

One last thing to mention...

Step 6 (optional): Deal With Edge Cases

If you play around with the formula and try values 0 or 100, you'll notice it doesn't quite work for these extreme values.

Consider 0: the output of the data array is {-1,2,99}.

The sparkline formula, in bar chart mode, interprets this as {1,2,99} since it only recognizes the "size" of the number, not the sign.

It's hardly noticeable though, so you could ignore it.

But, if you're a purist, here's a way to rectify it, using two IF functions to handle the edge cases:

---

As always, I invite you to share your thoughts and improvements to this formula...

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben