Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #348, your Monday morning espresso, in spreadsheet form!

Every year I support my sister-in-law's fundraiser for Takoma Elementary School by donating a bundle of all my courses.

The full retail price is $999 and at the time of sending this email the current bid is $250.

It's a great opportunity to grab all my courses for a reduced price and support a good cause.

​​Place your bid here!​​

➜ News

I.
Gemini in Google Sheets just got a big upgrade. It can now modify elements of your Sheet to create Pivot Tables, add dropdowns, apply conditional formatting etc.

I've had a quick look and it's a big step forward.

​Read more here >>​

II.
​Google Workspace apps for Gmail, Google Drive, Google Docs, Calendar, Keep, and Tasks are now generally available for the Gemini app

​Read more here >>​

➜ Membership

I'm working hard behind the scenes to move the membership to a bigger, better platform, where the content and community can really shine.

Over the coming months, I'll be improving the membership by adding learning pathways and a Q&A forum.

We've also seen some killer demos recently in the membership.

Last week, we looked at using the new AI function, Gemini, and Apps Script to create an automated feedback tool for teachers.

Starting with educational data, this AI tool turns student exam scores into draft email responses in Gmail, ready to send. A massive time saver!

Join today to get access to these exclusive tutorials, live workshops, full archives and much more.

​Check out Sheets Insiders here​

Brought to you by: Bricks Bricks AI Spreadsheet: Create Dashboards & Reports in 1-Click Bricks makes creating reports and dashboards from your spreadsheet data a breeze. Just import your data and Bricks creates interactive dashboards for you in seconds - complete with charts, KPIs, insights, and tables you can customize and share. Get Instant Insights: Bricks analyzes your data, surfacing patterns, trends, and key insights Generate AI-Powered Summaries and Tables: Bricks organizes and summarizes your data, complete with neatly formatted tables Automatically Create Charts: Bricks builds charts for you and updates them when your spreadsheet data changes Join 10,000+ professionals from organizations like Amazon, Nvidia, and Stanford who use Bricks to get their reporting done 10x faster. Create your first dashboard

➜ Sheets Tip #348:

In this tip, we look at an elegant formula that generates all possible pairings from two lists. In technical lingo, this is called a crossjoin.

One obvious application of this is for SEO work, generating lists of keywords to focus on.

Suppose we have our first list in column A and our second list in column B. Row one is a header row.

Step 1

Use the TRANSPOSE function to turn list 2 into a row format:

which looks like this in our Sheet:

Step 2

Use an Array Formula to combine all variations of list 1 and 2:

The &" " adds a space between the two words.

The output is an array of all possible combinations (called a crossjoin):

Step 3

Wrap this with TOCOL to convert the array into a single column:

which looks like this in our Sheet:

Step 4

Sort the data with the SORT function.

SORT also has the nice property of being an array formula by design, so we can drop the ArrayFormula syntax to make our formula cleaner:

This gives us the list of all possible combinations:

Nice!

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Ever wondered what would happen if you swam in a nuclear storage pool? Well, now you know.​