Hi Reader,

Greetings from sunny Harpers Ferry! ☀️

The rain has stopped (until tomorrow at least!) and I'm finally feeling better so I'm optimistic for the week ahead!

Welcome to this issue of the Google Sheets Tips newsletter, #303, your Monday morning espresso, in spreadsheet form!

Wow, that's a lot of exclamation points in the first few sentences! Oops, that was another. It's the optimism I feel today ;)

Today I want to share a data visualization technique for showing change between two periods. It's an effective way to show the change in variables before and after an event happens (e.g. employee effectiveness before and after training).

By now many of you will already have access to the new Table feature in Google Sheets, and if not, you'll get it soon. Let me know what you think. (More details in the News section below.)

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

➜ News

I.
​More details are emerging on the new Tables feature coming to Google Sheets. This powerful feature not only formats your data, but also adds column types (which should improve your data quality) and structured table references.

There's lots to like here so you'll definitely want to look out for this as it rolls out over the next few weeks. Find it under the Format menu of a Sheet near you...

​Read more on the Workspace blog >>​

II.
​Here's another new, handy feature in Google Sheets: Convert to dropdown chips. This helps you quickly convert ranges to dropdown chips in bulk.

​Read more about bulk dropdown chips >>​

III.
​Earlier this year, Google tightened data security in Sheets for formulas that connect to external data sources (e.g. IMPORTHTML, IMAGE, etc.). Users must now explicitly consent before Sheets can connect to external sources.

Martin Hawksey provides an update for developers on how to handle these changes, in this article:

​Allowing access on behalf of the user >>​

_______

➜ Google Sheets Tip #303: Show changes with a Slopegraph

Let's look at slopegraphs in today's tip.

Slopegraphs are a special type of line graph that shows the change between only two points.

They work well to show change because they're easy to interpret. We can understand the changes because the chart is simple.

Consider this chart that shows the change in energy source for electricity generation in the US, between 2000 and 2024:

It's easy to see how reliance on coal has fallen dramatically and natural gas and renewable energy sources have filled that gap.

Let's see how to create this chart in Google Sheets.

The dataset for this exercise is available in this template:

How to create a slopegraph in Google Sheets

1. Highlight the data (in the range A1:C6 in this example)
2. Go to the menu Insert > Chart
3. Under chart setup, select a "Line chart"
4. Check the "Switch rows/columns" checkbox, so that years are along the x-axis
5. Check the "Aggregate" checkbox under the x-axis to only show the two years

And voila! We now have a slopegraph:

This is OK, but we can make a few tweaks to improve it.

Under the customize section of the chart sidebar, consider making the following changes:

• Series > Add Data labels on the right
• Change the chart heading to something meaningful, e.g. "US Electricity Generation By Energy Source"
• Increase the size of the lines, labels, and legend so the chart is more readable
• Set the text color of the y-axis labels to white to hide them (since we added data labels)

Nice work!

​Here's the template for this exercise​

_______

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

Have a great week!

Cheers,
Ben

P.S. What level of coffee are you today?​