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
by Ben Collins
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Hi Reader, Welcome to this anniversary edition of the Google Sheets Tips newsletter, your Monday morning espresso, in spreadsheet form. This is the 300th edition of this weekly newsletter! 🎉 I've been sending it out for six years. The first issue — about keyboard shortcuts — was sent on 30 April 2018 to 2,357 of you. (Huge shout-out to anyone who's still with me!) In honor of this milestone, I want to look back at the six-year journey: The newsletter began life as the "Monday Tip" newsletter,...
Hi Reader, Welcome to the Google Sheets Tips newsletter #299, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: SHORTFORM Raising the Bar on Book Summaries With Shortform, you get deep insights that go beyond the typical 1-pager, with chapter breakdowns, well-researched commentary and analysis, connections to other books and counter-arguments, and even interactive exercises. Instead of spending days on a...
Hi Reader, Welcome to the Google Sheets Tips newsletter #298, your Monday morning espresso, in spreadsheet form! I hope you are doing well on this fabulous Monday morning. As an adult, it's rare to experience something completely new in day-to-day life. Days follow a familiar pattern, even if the specifics are different. Last week, however, the New York region was hit by a rare earthquake (thankfully, not too serious). I happened to be in Legoland New York with my family, in the gift shop,...