Hi Reader,

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

In the northern hemisphere, the weather has finally cooled down as we enter fall 🍁🍂. It's my favorite time of year with perfect temperatures, fewer bugs, and beautiful colors! Ideal for spending time outdoors.

My children are now back at school too, so I have more time to focus on work. Speaking of which, I'm in the midst of creating a new course!

It's called: The AI Playbook for Google Sheets

And it's all about how to use AI tools to work more effectively in Sheets.

It's designed to get you up to speed with AI technology as quickly as possible. You'll gain a set of practical techniques to apply to Sheets that will save you time and make your life easier.

This new course will be available in early October.

_______

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

SET&FORGET

Transform your Google Workspace with custom Apps Script solutions

Do you dream of writing Apps Script to automate tasks like Ben shows you below? But writing that script fills you with terror... We're here to write that code for you!

Unlock automation today!

POLYMER BI

See your Google Sheets data in a whole new way

Connect a Google Sheet and let Polymer automatically generate a beautiful interface to explore, filter and visualize your data. Seeing is believing.

Elevate Your Data Today!

_______

➜ News

I.
Google recently announced increased row limits in Connected Sheets for BigQuery, which gives you even more power to work with big datasets within Google Sheets.

Read the announcement here >>

And here's a primer on Connected Sheets >>

_______

➜ Google Sheets Tip #272: Dynamic chart heading in Sheets

Today we'll see how to link a chart title to a cell, so that the chart title automatically reflects whatever value is in the cell:

In Excel, you can use a cell value for a chart heading.

I.e. instead of typing a static chart title like "My chart", you can instead type =Sheet1!A1 and whatever is in cell A1 will be your chart heading.

This lets you create dynamic chart titles that always stay consistent with the data (if the data changes, your chart title updates automatically).

But that's in Excel.

Sadly we can't do that quite so easily in Sheets... 😥

But I get asked A LOT how we can do this in Sheets!

Fortunately, there is another way.

Unfortunately, it's not easy so you have to really want to use this technique.

Dynamic Chart Titles in Google Sheets

In your Sheet with your chart, open the Apps Script editor from the menu:

Extensions > Apps Script

Clear out the example code in the editor.

Add this code and press save (the disk icon in the toolbar):

/**

 * Function to automatically change chart title

 */

function changeChartTitle() {

  

  // get the chart

  // assumes only one chart, adjust the index [0] if needed

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

  const chart = sheet.getCharts()[0];

  

  // get the chart title from the cell

  const newTitle = sheet.getRange('A1').getValue();

  

  // update the chart title to the new title

  const chartBuilder = chart.modify();

  chartBuilder.setOption('title', newTitle);

  const updatedChart = chartBuilder.build();

  sheet.updateChart(updatedChart);

}

This code assumes there is a single chart in the sheet called Sheet1.

It takes the value from cell A1 as the chart title.

Feel free to modify "Sheet1" and "A1" in the code to match your specific situation (e.g. if your sheet is called "Data" change the "Sheet1" to "Data" in the code above).

The final step is to set a trigger so that this code runs every time the sheet changes.

Create a new trigger by clicking the Triggers section in the left menu:

In the Triggers section click "+ Add Trigger".

Then set up your trigger to look like this:

The only change you need to make is to ensure that the final box for "Select event type" is set to "On edit".

Press Save.

Then you're prompted to give the script permission to access your Sheet. (More info on that in this article.)

And that's all.

Back in your Sheet, your chart will display whatever text is shown in cell A1. And if that text changes, the chart title will automatically update to reflect it.

_______

➜ Sheets + AI tip #5

In this new section of the newsletter, I'll be sharing a quick, practical tip to help you leverage the power of AI tools with Sheets.

Today, let's look at pivot tables through an AI lens.

Google Sheets has had AI-assisted pivot tables for a couple of years, under the Explore tab.

Highlight your data range and click on the menu:

Tools > Explore

In the Explore sidebar, you'll have an option to insert a pre-built pivot table.

You can also take this a step further, by using an AI-powered add-on (e.g. the one from Coefficient*) that has an AI-assisted pivot table builder.

You can now build pivot tables by describing what you want to show, rather than manually dragging in the fields and setting it all up.

It's early days, so the tooling has some way to go, but I think this is a glimpse into the future of how we'll interact with our data.

* Disclosure: This link is an affiliate link, which means I get a small commission (at no extra cost to you) if you sign up.

_______

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

Have a great week!

Cheers,
Ben

P.S. You're either an optimist, a pessimist, or a spreadsheet.

Google Sheets Tips

Sheets Tip 272: Dynamic chart heading in Sheets 📊

SET&FORGET

Transform your Google Workspace with custom Apps Script solutions

POLYMER BI

See your Google Sheets data in a whole new way

➜ News

➜ Google Sheets Tip #272: Dynamic chart heading in Sheets

Dynamic Chart Titles in Google Sheets

➜ Sheets + AI tip #5

Sheets Tip 351: You know CHOOSECOLS secret trick, right?

Sheets Tip 350: Where does my score rank?

Sheets Tip 349: Out of breath