Category: Spreadsheets

Automatic Data Sharing Across Sheets With IMPORTRANGE and Named Functions

This automatic data sharing technique first appeared in my weekly Google Sheets Tip newsletter #269, August 2023.

(Don’t miss out! Join today to get future editions.)

This post showcases a unique use case for Named Functions and IMPORTRANGE.

Automatic Data Sharing Scenario

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.

Automatic Data Sharing in Sheets
Continue reading Automatic Data Sharing Across Sheets With IMPORTRANGE and Named Functions

What AI Can Do For You As A Google Sheets User. Is The Hype Justified?

In this post, we’re going to take a 40,000 ft view of the AI landscape and see how you can use it alongside Google Sheets to boost your productivity.

And, at the end of the post, I’ll answer the question about whether I think the AI hype is justified.

Before looking at what AI can do with Google Sheets though, let’s start with a quick AI 101 to ensure we’re up to speed with the lingo.

Green Tea Image generated by Dall-E
Image generated by Dall-E, a deep learning model, using the prompt “a cup of tea with a green potion inside with thin swirling vapors rising out of it”

Continue reading What AI Can Do For You As A Google Sheets User. Is The Hype Justified?

Recursion in Google Sheets With Formulas

Recursion in Google Sheets is now possible with the introduction of Named functions, LAMBDA functions, and LET functions. In this post, we’ll explore the concept of recursion and look at how to implement recursion in Google Sheets.

Prime Number Distribution
Discovering prime numbers using recursion in Google Sheets
Continue reading Recursion in Google Sheets With Formulas

Bullet Chart in Google Sheets with Sparklines and Named Functions

In this post, we’ll build a named function that creates a miniature bullet chart in Google Sheets, as shown in this GIF:

Bullet Chart Sparkline In Google Sheets

Bullet charts are variations on bar charts that show a primary measure compared to some target value. They’re highly effective because they capture a lot of information in a small, neat design.

To begin with — a warm-up if you like — let’s create a simple version of a bullet chart using a standard sparkline. (This formula featured as Tip 237 in my weekly Google Sheets newsletter. Sign up to get a weekly actionable Google Sheets tip!)

⚡ A template is available at the end of this post.
Continue reading Bullet Chart in Google Sheets with Sparklines and Named Functions