Formula-based rules in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a formula-based rule in Excel conditional formatting?

A formula-based rule uses a custom formula to decide which cells to format. If the formula returns TRUE for a cell, the formatting is applied to that cell.

beginner

How do you write a formula-based rule to highlight cells greater than 100 in column A?

Use the formula =A1>100 in the conditional formatting rule. This checks if each cell in column A is greater than 100.

intermediate

Why use absolute references (like $A$1) carefully in formula-based rules?

Absolute references fix the cell position when applying the rule to other cells. Using them incorrectly can cause the rule to always check the same cell instead of each cell individually.

beginner

What does the formula =ISBLANK(B2) do in a formula-based rule?

It checks if cell B2 is empty. If B2 is blank, the formula returns TRUE and the formatting is applied.

intermediate

How can you highlight rows where the value in column C is "Done" using a formula-based rule?

Use the formula =$C1="Done" in the conditional formatting rule applied to the whole row range. The $ before C locks the column, so the rule checks column C for each row.

What does a formula-based rule in Excel conditional formatting return to apply formatting?

A FALSE

B A number

C TRUE

D A text string

The formula must return TRUE for the formatting to be applied to the cell.

Which formula highlights cells in column A that are less than 50?

A =A1>50

B =$A1>50

C =A$1<50

D =A1<50

The formula =A1<50 checks if each cell in column A is less than 50.

What happens if you use an absolute reference like $A$1 in a formula-based rule applied to many cells?

A The rule checks the same cell $A$1 for all cells

B The rule adjusts the reference for each cell

C The rule ignores the formula

D The rule applies formatting randomly

Absolute references fix the cell, so the formula always checks $A$1 regardless of the cell being formatted.

Which formula highlights cells that are empty?

A =ISBLANK(A1)

B =A1=0

C =A1>0

D =NOT(ISBLANK(A1))

ISBLANK returns TRUE if the cell is empty, so the formatting applies to blank cells.

To highlight entire rows where column B equals "Yes", which formula is correct?

A =B$1="Yes"

B =$B1="Yes"

C =B1="Yes"

D =$B$1="Yes"

Using $B locks the column B, and the row number adjusts for each row, so the rule checks column B for each row.

Explain how to create a formula-based conditional formatting rule to highlight cells with values above a certain number.

Think about how to write a formula that checks if a cell's value is greater than your number.

Describe the role of absolute and relative references in formula-based conditional formatting rules.

Consider how $ signs affect the formula when applied to many cells.