profile

Google Sheets Tips

Sheets Tip 272: Dynamic chart heading in Sheets πŸ“Š

Published almost 2 years agoΒ β€’Β 3 min read


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!

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.

_______

➜ 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

by Ben Collins

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #356, your Monday morning espresso, in spreadsheet form! I’m delighted to have my friend Justin Keene of EdTech-Training sponsoring this week’s newsletter. His company teaches educators how to use the Canvas LMS through self-paced learning courses, but here’s the cool part: he built the entire training platform itself with Google Apps Script! By combining libraries, web apps, and APIs, Justin powers real-time progress...

5 days agoΒ β€’Β 2 min read

Brought to you by: Hi Reader, After a few weeks break for the summer πŸ–οΈ, we're back with the Google Sheets Tips newsletter #355, your Monday morning espresso, in spreadsheet form! What's new? AI continues to dominate the tech headlines, with new features crammed into every corner of the internet, whether they’re needed or not. But beyond the hype, the tools are improving, and that includes in the spreadsheet domain. These days, when I need to create a complex formula, I often start with a...

12 days agoΒ β€’Β 3 min read

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #354, your Monday morning espresso, in spreadsheet form! In last week's newsletter we compared wide and tall data, and what the pros and cons of each data "shape" were. Today, I want to show you how to move from one format to the other. How to transform tall data into wide data and vice versa. Going in one direction is easy. Going back, in the other direction, is less so. Find out which way round below! ➜ News I. It's...

2 months agoΒ β€’Β 2 min read