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 the Google Sheets Tips newsletter #269, your Monday morning espresso, in spreadsheet form!
You'll also find the new Sheets + AI Tip section below (tip #2 this week).
A big thank you to this week's sponsors who help keep this newsletter free for you to read:
I.
Google Cloud Next '23 Conference is happening next week in San Francisco. Although in-person tickets are sold out, you can still watch the sessions online with a Digital Access Pass. I'm not attending live, so I'm planning to watch the Workspace sessions online. I'll report any significant announcements in this newsletter.
II.
Speaking of Google Cloud Next, my friend and fellow GDE Martin Hawksey has put together a handy playlist of the Workspace-themed sessions happening at the conference.
III.
Version History is coming to Apps Script! 🤩
Soon you'll be able to view previously deployed script versions and compare them to the current version. This will be available to all Workspace and personal account users and it's rolling out over the coming weeks. Woohoo!
_______
Today's email took me longer to write than any previous newsletter.
The idea evolved substantially as I wrote the email, proving that the best way to understand something is to teach it to someone else.
Today we look at a special use case for named functions.
Bear with me, this tip is a little more involved but showcases a unique use case for Named Functions and IMPORTRANGE.
I'm curious to hear if you think it's useful or not.
Suppose you want to include a standardized piece of text on the top of your Sheets, e.g. a legal disclaimer or perhaps your company contact details. Sort of like an email signature, but for your Sheet.
There's a clever trick you can do with Named Functions and IMPORTRANGE to automate this across all your Sheets.
Best of all, if the information in the parent sheet is updated, these changes will flow through to any child sheet.
In this GIF, when we update the address in the parent sheet, the change automatically flows through to the child sheet:
Of course, you could skip this named function and just add the plain IMPORTRANGE function, but this requires you to write out that function and know the range reference.
I think this named function method simplifies that, especially for non-technical users.
Start in the Parent Sheet, i.e. the Sheet that will be the source of truth for your information.
Create a new named function: Data > Named functions
Then select: Add new function at the bottom of the sidebar.
Call the new Named Function COMPANY_DETAILS and define it as follows:
=IMPORTRANGE( "https://docs.google.com/spreadsheets/d/..." ,"Sheet1!B3:B5")
The URL inside the IMPORTRANGE should be the URL of the Parent Sheet you're in, i.e. the Sheet where you're defining the function.
The range reference (Sheet1!B3:B5 in my case) should be the range containing your company information (e.g. contact details).
Click Next
On the following page, click Create
Back in your Sheet, you can now insert this snippet of text anywhere you like using this function:
=COMPANY_DETAILS()
This is cool but not particularly useful yet.
It becomes super useful when you import this Named Function into other Sheets.
Then, if you change the text in the parent Sheet, it will propagate through to the child Sheet, because of the IMPORTRANGE function inside the Named Function.
This is a great boon for ensuring consistency between your Sheets. You only ever need to change the data in the parent Sheet.
In the child sheet where you want to add the information (e.g. a client report):
As always, I welcome your comments. 😀
(I read every email and do my best to reply to them all, although it sometimes takes me a while depending on the week and childcare situation!)
_______
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.
Last week (tip 268), we talked about using AI tools to explain formulas.
Well, these AI tools can also create formulas for you too.
It's important to be as specific as possible with your prompt.
For example, instead of “Create a formula that finds the average value of my range” say this “Create a Google Sheets formula to calculate the average value of data in the range D5:D20”.
They work very well for simple formulas.
For more complex formulas, think of the AI tool as an assistant that can generate ideas. The tools rarely get a complex formula correct the first time. But they can be super helpful in giving you ideas.
📌 In my experience so far, I've had better results from ChatGPT than Google Bard.
📌 Also, be aware that the tools can confidently suggest answers that are wrong. Make sure you test any suggestions carefully!
_______
If you enjoyed this newsletter, please forward it to a friend who might enjoy it.
Have a great week!
Cheers,
Ben
P.S. Race to a location
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 the Google Sheets Tips newsletter #301, your Monday morning espresso, in spreadsheet form. Earlier this year I sat down to record a new course but progress stalled before I got far. I've been creating courses for 7 years and realized that I need to freshen things up. One change I want to make is to inject more meaning into my work. I'm deeply interested in our natural world and protecting it for future generations. I want to use the skills I have and apply them towards...
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...