Formula-based rules in Excel - Step-by-Step Guide

Introduction

Formula-based rules let you change how cells look based on their values or other cells. This helps you spot important data quickly without checking each cell one by one.

When you want to highlight sales numbers above a target automatically.

When you need to mark overdue dates in a list of tasks.

When you want to color code expenses that are higher than average.

When you want to show which students scored below passing grade in a test.

When you want to flag duplicate entries in a list.

Steps

Step 1: Select

- the cells you want to apply the rule to

The selected cells are highlighted ready for formatting

Step 2: Click

- Home tab > Conditional Formatting dropdown

A menu with formatting options appears

Step 3: Choose

- New Rule from the Conditional Formatting menu

The New Formatting Rule dialog box opens

Step 4: Select

- Use a formula to determine which cells to format option

A formula input box appears

Step 5: Type

- the formula box

The formula is ready to check each cell for the condition

💡 Start your formula with = and use relative or absolute references as needed

Step 6: Click

- Format button

The Format Cells dialog opens to choose colors, fonts, or borders

Step 7: Choose

- desired formatting options (like fill color)

The preview shows how cells will look when the rule applies

Step 8: Click

- OK in Format Cells dialog, then OK in New Formatting Rule dialog

The formula-based rule is applied and cells change appearance if they meet the condition

Before vs After

Before

Cells A1:A10 have numbers from 1 to 10 with no special formatting

After

Cells A1:A10 show a green fill only for numbers greater than 5 based on the formula =A1>5

Settings Reference

Formula input box

📍 New Formatting Rule dialog

Defines the condition to check for each cell

Default: Empty

Format button

📍 New Formatting Rule dialog

Sets how cells look when the formula condition is TRUE

Default: No formatting

Applies to range

📍 Conditional Formatting Rules Manager

Specifies which cells the rule affects

Default: Selected cells

Common Mistakes

Typing a formula without starting with =

Excel does not recognize it as a formula and the rule will not work

Always start your formula with =, for example =A1>5

Using absolute references ($A$1) incorrectly

The rule applies the same cell reference to all cells, causing wrong results

Use relative references (A1) or mixed references carefully to apply the rule correctly across cells

Applying the rule to the wrong cell range

Cells outside the intended range will not format or wrong cells will format

Select the correct cells before creating the rule or adjust the Applies To range in the Rules Manager

Summary

Formula-based rules let you format cells automatically based on conditions you set with formulas.

You create these rules by selecting cells, choosing New Rule, and typing a formula that returns TRUE or FALSE.

Remember to start formulas with = and use correct cell references for the rule to work as expected.

Practice

1. What does a formula-based rule in Excel do when the formula returns TRUE?

easy

A. It changes the cell's appearance based on the rule.

B. It deletes the cell content.

C. It copies the cell to another sheet.

D. It locks the cell from editing.

Solution

1. Step 1: Understand formula-based rules

   Formula-based rules use formulas that return TRUE or FALSE to decide if formatting applies.

2. Step 2: Effect of TRUE result

   If the formula returns TRUE, Excel applies the formatting like color or font change to the cell.

3. Final Answer:

   It changes the cell's appearance based on the rule. -> Option A

4. Quick Check:

   TRUE formula triggers formatting [OK]

Hint: TRUE formulas trigger formatting changes in cells [OK]

Common Mistakes:

• Thinking the rule deletes or moves data
• Confusing formatting with locking cells
• Assuming FALSE triggers formatting

2. Which of these is the correct formula to highlight cells greater than 50 in conditional formatting?

easy

A. IF(A1>50)

B. A1>50

C. =A1>50

D. =IF(A1>50)

Solution

1. Step 1: Check formula syntax for conditional formatting

   Conditional formatting formulas must start with '=' and return TRUE or FALSE.

2. Step 2: Validate each option

   =A1>50 uses correct syntax and returns TRUE or FALSE directly. IF(A1>50) and A1>50 miss '='. =IF(A1>50) uses incomplete IF without value_if_true and value_if_false.

3. Final Answer:

   =A1>50 -> Option C

4. Quick Check:

   Formula must start with '=' and return TRUE/FALSE [OK]

Hint: Always start formulas with '=' in conditional formatting [OK]

Common Mistakes:

• Omitting '=' at the start
• Using incomplete IF statements
• Writing formulas that don't return TRUE/FALSE

3. Given the formula-based rule with formula =AND(A1>10, A1<20), which cells will be formatted?

medium

A. All cells regardless of value

B. Cells with values equal to 10 or 20

C. Cells with values less than 10 or greater than 20

D. Cells with values between 10 and 20, excluding 10 and 20

Solution

1. Step 1: Understand the AND formula conditions

   The formula checks if A1 is greater than 10 AND less than 20 simultaneously.

2. Step 2: Determine which values satisfy both conditions

   Only values strictly between 10 and 20 (not including 10 or 20) make the formula TRUE.

3. Final Answer:

   Cells with values between 10 and 20, excluding 10 and 20 -> Option D

4. Quick Check:

   AND(A1>10, A1<20) means 10 < A1 < 20 [OK]

Hint: AND means all conditions must be TRUE simultaneously [OK]

Common Mistakes:

• Including boundary values 10 or 20
• Confusing AND with OR logic
• Thinking it formats all cells

4. You wrote the formula =A1>100 for conditional formatting but no cells are highlighted even though some values are above 100. What is the likely problem?

medium

A. The formula is missing the '=' sign at the start.

B. The formula is correct but the range is not set properly.

C. The formula should use IF(A1>100) instead.

D. Conditional formatting does not support comparison operators.

Solution

1. Step 1: Check formula correctness

   The formula '=A1>100' is correct syntax for conditional formatting.

2. Step 2: Consider the formatting range

   If no cells highlight, likely the conditional formatting rule is applied to the wrong or empty range.

3. Final Answer:

   The formula is correct but the range is not set properly. -> Option B

4. Quick Check:

   Correct formula + wrong range = no formatting [OK]

Hint: Check the range where the rule is applied [OK]

Common Mistakes:

• Removing '=' sign
• Using IF unnecessarily
• Believing conditional formatting can't compare values

5. You want to highlight cells in column A that are either blank or contain a number less than 50. Which formula-based rule should you use?

hard

A. =OR(ISBLANK(A1), A1<50)

B. =AND(ISBLANK(A1), A1<50)

C. =IF(A1="", TRUE, A1<50)

D. =NOT(ISBLANK(A1))

Solution

1. Step 1: Understand the condition requirements

   We want to format cells that are blank OR have a number less than 50.

2. Step 2: Choose the correct logical formula

   OR(ISBLANK(A1), A1<50) returns TRUE if either condition is met, matching the requirement.

3. Final Answer:

   =OR(ISBLANK(A1), A1<50) -> Option A

4. Quick Check:

   Use OR for either condition TRUE [OK]

Hint: Use OR to combine multiple conditions [OK]

Common Mistakes:

• Using AND instead of OR
• Misusing IF inside conditional formatting
• Ignoring blank cells condition