Brought to you by:

Hi Reader,

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

* arriving on a Tuesday because yesterday was a public holiday in the US (Labor Day)

In case you missed it, last week I sent an email about a new membership program I'm launching soon. Over the next few weeks I'm going to send some behind-the-scenes emails with all the details about this program.

​If you're interested, click here to get them​

(Does not commit you to a purchase.)

Also, to reassure you:

1) This free newsletter is not changing. You can still enjoy this every week regardless of whether or not you're interested in the membership.

2) The existing online courses are not changing either, so you still have 24/7 access to anything you've enrolled in.

If you have any other questions or concerns, just hit reply.

In today's newsletter we're going to work through an interesting conditional formatting problem that a reader shared recently.

Brought to you by: Set & Forget Don’t hire us if you love manual tasks ​Set & Forget isn't for everyone. If you enjoy spending hours doing repetitive tasks, we're probably not a good fit. But if you're ready to automate your operations and streamline your workflows within Google Workspace, let's talk about turning your daily grind into effortless automation. Book a call today

➜ News

I.
​When you create a new Google Sheet from the Google Form responses, it will automatically open in the new Tables format.

One huge benefit is that if you add columns with formulas, these will now automatically expand down a column to include new data.

​Read more here >>​

II.
​The Google AI team have launched Gems for Gemini AI. Gems are customized AI experts designed to solve a specific problem or work in a specific niche.

I haven't tried them yet myself, but I'll report back when I do.

Gems are available today to Workspace customers.

​Read more here >>​

➜ Sheets Tip 314: An interesting conditional formatting example

This week's tip is based on a recent question from reader Nicole. It shows a super interesting technique with ranges in conditional format rules.

Let's begin with a list of 20 names, in column A:

We want to highlight names that occur 5 times or more.

Let's build the rule in our Sheet first, before considering conditional formatting.

We need to count how many times a name shows up in the list. To do that we'll use this COUNTIF formula in cell B2:

=COUNTIF($A$2:$A$21,A2)

Notice how we've put $ signs around the range reference $A$2:$A$21 so that it's locked (called an absolute reference). This is important for conditional formatting.

Next we want to check if this count is greater than or equal to 5, because that would indicate a name that occurs 5 times or more.

Add a simple test to make it a conditional and put this formula into cell C2:

=COUNTIF($A$2:$A$21,A2)>4

In the Sheet, we can see the name "Sue" occurs 5 times, so the formula shows TRUE for those instances:

Now that we have a formula that outputs a TRUE/FALSE, we are ready to create our conditional formatting.

(Watch this video for a primer on conditional formatting. For more advanced techniques, check out this video.)

Add the conditional formatting rule as follows:

1. Highlight the range containing the 20 names in column A
2. Go to the menu: Format > Conditional Formatting
3. Check that the "Apply to Range" is set to A2:A21
4. For the "Format cells if..." format rule, select the last option in the dropdown: Custom formula is
5. Copy and paste in our formula:
   ​​
   =COUNTIF($A$2:$A$21,A2)>4
   ​
6. Change the formatting styles to suit
7. Click Done

This will highlight any names (or values) that occur 5 times or more in the list:

Job done! 👏

Advanced Range References

But what if we wanted to highlight only the 5th or higher occurrences of the names? (This was the original reader's question.)

Initially I thought it might require a complex formula to identify the 5th name count or the bottom position. But then I remembered a property of conditional formatting that makes this possible with a slight tweak to the range reference in the formula.

Going back to the list in our Sheet, change the formula in B2 to this:

=COUNTIF($A$2:$A2,A2)

Notice that the last part of range reference is now A2 instead of A21. And crucially, we OMIT the dollar sign before that final 2.

I.e. $A$2:$A2 and not $A$2:$A$2

This means that when I drag the formula down my column, the range expands to include up to the row where I drag it to. For example, the second formula is:

=COUNTIF($A$2:$A3,A3)

and the third:

=COUNTIF($A$2:$A4,A4)

This means that the COUNTIF function checks a different range on each row.

The result is that it counts how many times a name occurs up to that point, not across the whole range.

For example, in our Sheet:

The orange border shows the formula is only checking rows A2 to A6. And "Jeff" is only found once in that range.

This is the rule for the conditional formatting:

=COUNTIF($A$2:$A2,A2)>4

So now, only the 5th occurrence of "Sue" is highlighted:

Good stuff!

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

Have a great week!

Cheers,
Ben

P.S. Don't know how to earn that 5th star​