Brought to you by:

Hi Reader,

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

* coming to your inbox on a Tuesday because many folks have a long weekend for Easter.

In today's reader-inspired example, we're going to look at a common, but tricky, conditional formatting example:

How to highlight values in one range based on activity in a different range?

Today we'll look at how to create a conditional formatting rule to highlight column A based on checkboxes in column C.

Check it out below.

➜ News

I.
​My friends at GRID are hosting a free webinar to explore the "AI Calculation Problem" a.k.a. how to enable reliable calculations within AI solutions.

They're one of leaders in combining spreadsheets with AI, so this is bound to be an insightful session.

I'm signed up and am looking forward to it!

​Register for free here >>​

II.​
​Connected Sheets lets users analyze giant BigQuery datasets in Google Sheets and last week it got an update. Users can now create custom chart groupings in Connected Sheets so you can create histograms.

​Read more in the update >>​

Brought to you by: Flookup Data Wrangler Intelligent Data Cleaning for Google Sheets Convert data chaos into data clarity without writing a single line of code! Flookup is a lightweight Google Sheets add-on designed for efficient, affordable and fast data cleaning. With powerful tools like fuzzy matching and deduplication, Flookup offers unlimited row cleaning for free. Transform your datasets into actionable insights by merging datasets, highlighting and removing duplicates regardless of any text-based differences. Need human-level understanding? No problem. Clean data with AI by leveraging natural language processing to streamline and refine your datasets effortlessly. Join over 10,000 people who use Flookup on a daily basis! Transform your data today

➜ Sheets Tip #344: Conditional Formatting explained

Let's start with this dataset, with some values in column A and checkboxes in column C:

A reader asked how to highlight the values in column A if the checkbox was checked in column C.

To do this we'll use a custom rule in conditional formatting. It sounds complex but it's not once you understand how it works.

To begin, we highlight the range of values that we want to apply formatting to:

Then go to the menu: Format > Conditional formatting

The highlighted range A2:A11 will be shown in the "Apply to range" box.

Under the "Format rules" section, open the dropdown and choose "Custom rule is"

Then enter the following formula:

This checks whether C2 is checked or not.

(Technically, the "$" is not required in this example, since we're only checking a single column. But I've included it because if want to expand the highlighted range to more than just column A, you'll need the "$".)

If the checkbox is checked then the rule is TRUE and any formatting will be applied to the corresponding cell of the format range (i.e. the first cell in the range A2:A11).

What's important to realize is that the formatting (the background cell colors) is applied to the range we originally highlighted, A2:A11.

But, the TRUE/FALSE rule that decides whether to apply the formatting begins at C2 and works down from there. I.e.

• C2 is checked but formatting is applied to A2
• C3 is checked and formatting is applied to A3
• Etc.

So our "rule cells" can be place anywhere, e.g. starting from D8:

The point is that the dimensions of the range we highlighted matches the dimensions of the range used for the rule checking. We just need to give the top left cell address.

Our final setup is:

What about the whole row?

If you want to apply the formatting to the whole row, like this:

Then change the "Apply to range" from A2:A11 to A1:C11 so that it extends to column C.

Note: it will only work if your rule is "=$C2" with the "$" sign.

Once you understand this dynamic between the format range and the "rules" range, you've cracked conditional formatting!

Conditional Formatting Resources

If you're new to conditional formatting, start with this video:

​

And then watch this one for more advanced examples:

​

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

Have a great week!

Cheers,
Ben

P.S. Every data table from now on​