Hi Reader,

Welcome to the Google Sheets Tips newsletter #277, your Monday morning espresso, in spreadsheet form!

Fall is in full swing here in the Mid-Atlantic region of the US, where I'm based. The colors are fantastic right now and I am here for it! 🍁

I hope you're enjoying the cooler weather and vibrant colors if you're in the Northern hemisphere, or the welcome return of some warmth if you're in the Southern hemisphere.

A big thank you to this week's sponsors who help keep this newsletter free to read:

SUPERMETRICS Last call to join us at SuperSummit this Wednesday, October 25! Leading brands like Google, Meta, LinkedIn, and Tambourine are joining us for a virtual event to discuss the megatrends impacting marketing measurement in 2024: Gen AI, Big Data, and Privacy. You don’t want to miss this! Register for free

DERRICK APP Import Lists Of Qualified Leads from LinkedIn Into Google Sheets With 2 clicks, Derrick can create qualified lists of prospects for your business. Derrick imports companies, search results, profiles, and more from LinkedIn into Google Sheets without any technical knowledge. We even find business emails for you! Claim your 200 free credits today!

➜ News

I.
​Here's an interesting read from Hjalmar Gislason at Grid, who posits that domain experts use spreadsheets to “encode” their unique expertise and monetize it.

​Monetizing expertise through spreadsheet models​

_______

➜ Google Sheets Tip #277: Create pyramid charts with bar sparklines

A reader recently asked me how to draw a right-aligned pyramid chart in Google Sheets.

What's that, I hear you ask?

Well, here's an example:

You see them used frequently for population pyramids.

So let's create it, one formula at a time (what I call the Onion Method!).

The first step is to calculate the "gap", i.e. the difference between 100% and our number.

Use this formula in cell B2:

We then use the value in A2 and this new value in B2 as the data inputs to the SPARKLINE function (e.g. 40% in A2 and 60% in B2). That's fine, but ultimately we want the pyramid chart to show in cell B2.

So, we use an array literal formula to capture both the original value and the "gap" value. This formula spills across cells B2 and C2:

(Note, if you're European-based you use a backslash "quot; instead of a comma "," inside this array literal formula.)

Next, wrap this array with the SPARKLINE function and specify the "bar" as the chart type.

The output at this stage is:

We have two tasks left: 1) only highlight the value part of the chart (orange), not the gap part (blue), and 2) flip it to be right-aligned.

To hide the gap part, we can set the value of "color2" to be white. Note, adjust "color1" to change the orange color.

Here is the updated formula:

which gives an output:

Looking good!

Of course, we could stop here but let's see how to right align it.

There are two methods:

1. Set the chart right-to-left ("rtl") value
2. Flip the data inputs around

For method 1, set the "rtl" value to true, as shown in this formula:

This gives us this nice result:

However, the eagle-eyed amongst you might spot the orange border around the sparkline chart.

(Don't worry if you can't see it, it's very faint on these images, but you can see it more clearly in the Sheet.)

So, to mitigate this, we can flip the data around instead of using the "rtl" property.

Use this formula with the data values switched around:

And now the output is:

Homework (optional of course! 😜)

See if you can turn this into an array formula, so it works for any rows with values. There are a few different ways to do this: with the traditional ArrayFormula function or the newer LAMBDA functions like BYROW.

Have fun!

As always, I welcome any comments you want to share.

_______

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

Have a great week!

Cheers,
Ben

P.S. An incredible timelapse of the recent annular eclipse.​