Hi friend,

Happy Monday! Welcome to Google Sheets tip email #175.

As a dad to two young boys, one question I think about a lot is, "how can I find more time in my day?"

Here's one piece of advice, from mom and ultrarunner Katie Arnold (NYT article), that's always stuck with me:

"You have to steal time from the edges of your day"

For example, I'll take a short walk when I need to think through a thorny work problem. That way, I get to move and make meaningful progress on a project. Another example might be listening to an audio course whilst folding laundry.

Here's to stealing back time!


➜ Announcements

I.
There are still a few spots left in my live training program, Pro Sheets Accelerator:

Join us in Pro Sheets

We have 30 students so far for this second cohort, which starts at the end of this month. I can't wait to get started!

Enrollment closes soon so don't hang around if you want to join us...

_______

II.
Yesterday, October 17th, was Spreadsheet Day, a day to celebrate the joys of spreadsheets! It was coined by Excel consultant Debra Dagleish to honor the launch day of VisiCalc, the world's first spreadsheet program, on October 17th, 1979.

_______

III.
This session, from last week's Google Next 21 conference, is worth watching if you're interested in Apps Script and how it fits into the Workspace ecosystem:

How Google Workspace is empowering workforces with Apps Script
 

➜ Google Sheets Tip #175: "Starts with..." filter in the QUERY Function

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form. 

I had a really interesting question from a reader last week about filtering inside of the QUERY function select statement.

When he emailed me, he was using an extra column with an IF statement to classify rows, which he then used in his QUERY statement. He wondered if it could be replicated directly in the QUERY function to save a step.

In this example, he had marketing data where rows had a social media source, e.g. "instagram", "facebook", "instagram_feed", and "instagram_stories" etc., and he wanted to filter data by the social media group.

Can we do that inside a QUERY function? Yes, we can! 

Use the "starts with" clause in the select statement to filter anything that begins with a certain phrase, e.g. "instagram".

Here's an example formula:

=QUERY(A1:B,"select A, B where B starts with 'instagram'",1)

And here's an example:

(click to open larger image)

What's going on here?

The select statement in the QUERY function selects columns A and B and then applies a filter with the "where" clause and the "starts with" keywords.

The filter looks at each row of data and includes it whenever it starts with "instagram", regardless of what characters follow. So it includes things like "instagram", "instragram_feed", "instragram2" but excludes "facebook", "whatsapp" etc.

Pretty cool, huh?

The QUERY function is an amazing function. There is so much depth to it and even after using it for years, I'm still learning new techniques.

And that's why I plan to create a dedicated QUERY function course in early 2022...
 

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

Have a great week!

Cheers,
Ben

P.S. The world map according to fish

P.P.S. An easy way to steal back time in your day is to delete social media apps off your phone...

I've done this a few times (and added them back a while later, because I enjoy them too) and every time it makes me realize how habitual it is to grab my phone and procrastinate! 😂