Cross-column conditional rules in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a cross-column conditional rule in Google Sheets?

A cross-column conditional rule checks values across two or more columns to apply formatting or logic based on conditions involving multiple columns.

beginner

How do you write a formula for conditional formatting that compares values in column A and column B?

Use a formula like =A1>B1 in the conditional formatting rule to compare values in the same row across columns A and B.

intermediate

Why do you use absolute and relative references carefully in cross-column conditional rules?

Because relative references adjust for each cell, while absolute references stay fixed. For cross-column rules, you usually want relative row references to compare cells in the same row across columns.

beginner

Example: What does the formula =AND(A1>10, B1<5) do in conditional formatting?

It applies formatting only if the value in column A is greater than 10 AND the value in column B is less than 5 for the same row.

beginner

Can cross-column conditional rules be used to highlight rows where two columns have the same value?

Yes! Use a formula like =A1=B1 in conditional formatting to highlight rows where the values in columns A and B match.

Which formula highlights cells in column A when the value in column A is greater than the value in column B in the same row?

A =A1>B1

B =B1>A1

C =A$1>B$1

D =$A1>$B1

The formula =A1>B1 compares values in the same row across columns A and B. Relative references allow the formula to adjust for each row.

What does the formula =AND(A1>5, B1<10) do in conditional formatting?

A Formats if A1 is greater than 5 OR B1 is less than 10

B Formats if A1 is greater than 5 AND B1 is less than 10

C Formats if A1 equals B1

D Formats if either A1 or B1 is empty

AND requires both conditions to be true: A1 > 5 and B1 < 10 for formatting to apply.

In cross-column conditional formatting, why avoid using absolute row references like $1 in formulas?

A Because it makes the formula slower

B Because it highlights the wrong columns

C Because it causes errors in Google Sheets

D Because it fixes the row and stops the formula from adjusting for each row

Absolute row references fix the row number, so the formula won't adjust for each row, which is usually needed in cross-row comparisons.

Which formula highlights rows where values in columns A and B are equal?

A =A1=B1

B =AND(A1,B1)

C =A1<>B1

D =OR(A1,B1)

The formula =A1=B1 returns TRUE when values in columns A and B are the same in the same row.

What is the main benefit of using cross-column conditional rules?

A To create charts

B To sort data automatically

C To format cells based on values in other columns

D To delete rows

Cross-column conditional rules let you highlight or format cells based on conditions involving multiple columns.

Explain how to create a conditional formatting rule that highlights cells in column A when the value is greater than the value in column B in the same row.

Think about comparing cells in the same row across two columns.

Describe why relative references are important in cross-column conditional formatting formulas.

Consider what happens when the formula is applied to multiple rows.