Hi Reader,

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

I have a fun data tip for you today, involving a secret feature of the QUERY function. Find that below.

But first...

Today is a special day because my new Sheets Insiders membership program is officially open for enrollment!

Sheets Insiders is a membership program designed to help you work more effectively with Google Sheets and Apps Script.

As a member of Sheets Insiders, you get exclusive members-only benefits, including a members-only newsletter, new templates, deep-dive tutorials, and more.

If you enjoy this newsletter, you will love the Sheets Insiders membership program.

It’s also an opportunity for you to support my work, so I can continue to write this free weekly newsletter that I’ve been publishing since 2018!

The annual membership is $199, and during this launch week you can enroll for just $149. That’s a whole year's worth of content for less than the price of one of my existing online courses.

I hope you'll join us!

​Learn more about Sheets Insiders​

➜ News

I.
​Two of the newer features in Google Sheets — Conditional Notifications and Google Tables — are now integrated.

Conditional notification rules can now apply to Tables and work with Table names. Some common notification rules will be pre-built into Tables and can be enabled with a single click.

Available for Google Workspace customers only.

​Read more here >>​

II.
​Not Sheets, but Docs gets an option to add a spiffy cover image to the top of your documents. Looks rather nice!

​Read more here >>​

➜ Sheets Tip #318: The "skipping" clause

As data analysts, one of the tasks we might do is to create samples of our data. That means taking a small subset of data from our main dataset that still approximates the full dataset.

It's especially useful if our main dataset is large. Using a sample, we can draw conclusions about the dataset but benefit from the speed and simplicity of working with a smaller dataset.

There are various statistical methods for sampling that we're not considering here.

Instead, I want to show you a simple way to take a sample in Google Sheets using the amazing and versatile QUERY function. We'll use the undocumented "skipping" clause to select every n-th row.

Let's see an example.

We have 20 dates in column A in the range A2:A21. We want to create a sample with only 5 dates.

The formula is:

=QUERY(A2:A21,"skipping 4")

and the result looks like this:

I've highlighted the sampled values yellow to make it easier to see how the formula works.

It selects the first value, then skips over the next 4 rows, then selects the 5th, and so on.

Of course, suppose we had a much bigger dataset with say 100,000 transactions in our Sheet. That's a large dataset for Sheets and probably cumbersome to work with.

We could use this formula to create a sample of 100 rows:

=QUERY(A2:F,"skipping 1000")

where our data is in the range A2:F

Happy skipping!

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

Have a great week!

Cheers,
Ben

P.S. Staying on topic, can you remember how to skip?​