Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets Tip 272: Dynamic chart heading in Sheets π
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:
_______
β 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
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #344, your Monday Tuesday* morning espresso, in spreadsheet form! * coming to your inbox on a Tuesday because many folks have a long weekend for Easter. In today's reader-inspired example, we're going to look at a common, but tricky, conditional formatting example: How to highlight values in one range based on activity in a different range? Today we'll look at how to create a conditional formatting rule to highlight...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #343, your Monday morning espresso, in spreadsheet form. Thanks for all the well wishes I received to last week's email! I'm two weeks into the recovery from sinus surgery and things are slowly but surely returning to (a new) normal. I'm taking daily walks around my neighborhood, going slightly further each day. For the first couple of days I needed my wife as a support to hold on to, as I shuffled up and down the...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #342, your Monday morning espresso, in spreadsheet form! Whew, it's been quite the week in my household. Last week I had sinus surgery to alleviate some long-standing sinus issues I've had. It was a nearly 6-hour surgery so I was completely out-of-it for a day or two afterwards. When I came round they asked if I wanted a drink. I said "some water" but I couldn't speak clearly because of the bandages etc. The nurse...