Hi Reader,

Happy End-of-the-Summer-Holidays! ☀️

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

Today I'm sharing three conditional formatting techniques that will help you really understand how conditional formatting works. Conditional Formatting is formatting applied to your data that changes as certain conditions are met. It's a super useful concept to understand. Check it out below.

Recently in my household, we've been focussed on preparing for today: my boys went back to school this morning, both at new schools. For my youngest, it was also his first day riding the bus. They were both nervous but did great.

For the parents left behind at the bus stop—at least 30 of us—there were mixed emotions. A few wry claps and cheers, an outpouring of relief that we have childcare again, but also some tears and sadness that another summer has come and gone. And with it the sobering realization that your children are only young for a short time. A reminder to savor every day with them.

Have a great week ahead!

➜ News

I.
Sheets Canvas officially launches for Google Sheets.

This is a really impressive tool for building interactive dashboards and apps inside Google Sheets, with your existing data.

​Read official post here >>​

​And a tutorial from me here >>​

(Note, this feature is only available to customers on Business or Enterprise Standard and Plus plans, Google AI Pro for Education add-on subscribers, or consumers on AI Pro or Ultra plans.)

II.
New usability features for Connected Sheets.

​Connected Sheets is a feature that connects Sheets to BigQuery for working with really big datasets. These new features allow you to list parameters and use column aliasing.

​Read more here >>​

III.
Improved file importing in Google Sheets with tables and linked pivot tables.

​Read more here >>​

➜ Sheets Tip #397: Conditional Formatting Must Knows

Conditional formatting is a super useful technique for formatting cells in Google Sheets based on whether they meet certain conditions.

(If you're new to it, or need a refresher, this video has you covered.)

The conditional formatting article on my website is the most popular post I've ever written, by a large margin.

Everyone loves the idea of Conditional Formatting, but it's quite finicky to get right.

Here are three must-know techniques, once you get past the basics:

1. Multiple Rule Precedence

If you have multiple conditional formatting rules, Sheets checks rules in descending order and stops at the first match for each cell in the range.

To ensure a new rule takes priority, you need to drag it above the others in the Conditional Formatting sidebar using the three dots to the left of the rule.

2. Apply Rule to Whole Row/Column Based on Single Cell

Highlight your data range you want to apply conditional formatting to, then open the menu: Format > Conditional formatting

Selects the rule "Custom formula is".

When you enter a formula, reference the first row of your range and lock only the column with a "$" so the row is checked against that one column's value.

For example:

Google Sheets then applies the formatting to the entire row wherever that condition evaluates true.

Find more examples of applying complex conditional formatting in this article: How To Apply Conditional Formatting Across An Entire Row In Google Sheets​

3. Fixing Broken Range Rules

One of the most common, and frustrating, issues is when the ranges to which the conditional formatting is applied break down as you add more information.

The range breaks into smaller "range chunks" and no longer covers the whole data set.

See this example, where I drag down the numbering in the first column:

Unfortunately, the conditional formatting rule is only applied to the new cell in column A, not the whole of the new row. The yellow highlighting is only applied to column A in row 4, not the entire row.

If you look closely at the range that the formatting is being applied to, it is now

It has broken into two chunks and no longer includes the whole range.

Now take a look at following example where a short script automatically fixes these "range chunks" by recombining them into a single range:

Look closely and you can see that the conditional formatting range is automatically converted to the correct range:

Magic!

In reality, there is a short script running in the background that checks the conditional formatting ranges and updates them to the full data range when required.

Before the advent of AI tools, this solution would have been out of reach for many people.

But with the availability of Gemini, anyone can now build a quick automation like this to solve an annoying problem.

In this specific example, I used the following prompt in Gemini to generate the script:

From your Sheet, go to the toolbar menu to open your Apps Script file and paste in the code that Gemini generated:

Extensions > Apps script

Save the file, run it and allow the permissions when prompted. Then test it on a simple conditional formatting dataset like this example.

Nice!

For more information on getting started with Apps Script, check out this post: Google Apps Script: A Beginner's Guide​

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

Have a great week!

Cheers,
Ben

P.S. A novel approach to ensuring you'll have a job in the AI future.​