Managing rule priority in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the result of this conditional formatting formula?

You apply this formula in conditional formatting to cell A1:

=AND(A1>10, A1<20)

If A1 contains 15, what will be the formatting result?

A The formatting is not applied because AND always returns FALSE.

B The formatting is not applied because 15 is not greater than 20.

C The formatting is applied only if A1 equals 10 or 20.

D The formatting is applied because 15 is between 10 and 20.

💡 Hint

AND returns TRUE only if all conditions are TRUE.

The formula checks if A1 is greater than 10 and less than 20. Since 15 meets both, the formatting applies.

❓ Function Choice

intermediate

Which function correctly prioritizes rules to highlight the highest value?

You want to highlight the cell with the highest value in range B1:B10 using conditional formatting. Which formula should you use?

A =B1>AVERAGE($B$1:$B$10)

B =B1=MIN($B$1:$B$10)

C =B1=MAX($B$1:$B$10)

D =B1=MEDIAN($B$1:$B$10)

💡 Hint

MAX finds the largest number in a range.

The formula checks if the cell equals the maximum value in the range, so only the highest value is highlighted.

🎯 Scenario

advanced

How does rule priority affect overlapping conditional formatting?

You have two conditional formatting rules on the same cells:
1) Cells greater than 50 are colored green.
2) Cells greater than 70 are colored red.

If a cell contains 75, what color will it be and why?

A Green, because the first rule always applies regardless of priority.

B Red, because the rule for >70 has higher priority and overrides the >50 rule.

C No color, because the rules conflict and cancel each other out.

D Both colors, layered on top making a mixed color.

💡 Hint

Higher priority rules override lower ones when conditions overlap.

Since 75 is greater than both 50 and 70, the rule with higher priority (usually the one listed first or moved up) applies, coloring the cell red.

📊 Formula Result

advanced

What is the output of this formula in conditional formatting?

Given the formula:

=OR(A1>100, AND(A1>50, A1<80))

What is the result if A1 contains 75?

A TRUE, because 75 is between 50 and 80.

B FALSE, because 75 is not greater than 100.

C TRUE, because 75 is greater than 100.

D FALSE, because AND always returns FALSE here.

💡 Hint

OR returns TRUE if any condition is TRUE.

75 is not greater than 100, but it is greater than 50 and less than 80, so AND returns TRUE, making OR TRUE.

❓ data_analysis

expert

How many cells will be formatted with these overlapping rules?

You apply two conditional formatting rules to range C1:C5:
Rule 1 (higher priority): Format cells if value > 30
Rule 2 (lower priority): Format cells if value > 50

Values in C1:C5 are: 25, 35, 45, 55, 65

How many cells will be formatted and which rule applies to each?

A 4 cells formatted: 35, 45 by Rule 1; 55, 65 by Rule 1 (Rule 2 overridden).

B 5 cells formatted: all values except 25 by Rule 1 only.

C 3 cells formatted: 35, 45, 55 by Rule 1; 65 by Rule 2.

D 4 cells formatted: 35, 45 by Rule 1; 55, 65 by Rule 2 (higher priority overrides).

💡 Hint

Higher priority rules override lower ones when conditions overlap.

Values 35 and 45 meet only Rule 1. Values 55 and 65 meet both rules, but Rule 1 has higher priority so it applies to all cells that meet its condition.

Practice

1. In Google Sheets, when multiple conditional formatting rules apply to the same cell, which rule takes effect?

easy

A. The rule that was created last

B. The rule with the most complex formula

C. The rule with the highest numerical value in its formula

D. The rule listed first (top) in the Conditional Formatting pane

Solution

1. Step 1: Understand rule order in Conditional Formatting

   Rules are applied from top to bottom in the Conditional Formatting pane.

2. Step 2: Determine which rule applies when multiple match

   The first matching rule (topmost) is applied, and others below are ignored unless "Stop If True" is unchecked.

3. Final Answer:

   The rule listed first (top) in the Conditional Formatting pane -> Option D

4. Quick Check:

   Rule priority = top rule applies [OK]

Hint: Top rule in the list applies first [OK]

Common Mistakes:

• Thinking last created rule applies
• Believing formula complexity affects priority
• Assuming numerical values in formulas decide priority

2. Which of the following is the correct way to change the priority of conditional formatting rules in Google Sheets?

easy

A. Delete and recreate the rules in desired order

B. Drag the rules up or down in the Conditional Formatting pane

C. Change the cell range of the rules

D. Rename the rules alphabetically

Solution

1. Step 1: Locate Conditional Formatting pane

   Open the Conditional Formatting sidebar where rules are listed.

2. Step 2: Adjust rule order by dragging

   You can click and drag rules up or down to change their priority order.

3. Final Answer:

   Drag the rules up or down in the Conditional Formatting pane -> Option B

4. Quick Check:

   Drag rules to reorder priority [OK]

Hint: Drag rules to reorder priority [OK]

Common Mistakes:

• Trying to rename rules to change order
• Changing cell ranges instead of order
• Deleting rules unnecessarily

3. Given two conditional formatting rules on the same cell:
Rule 1 (top): Format if cell > 10 (color red)
Rule 2 (below): Format if cell > 5 (color green)
If the cell value is 12, what color will the cell be?

medium

A. No color

B. Green

C. Red

D. Both red and green

Solution

1. Step 1: Check which rules apply for value 12

   12 is greater than 10 and also greater than 5, so both rules match.

2. Step 2: Apply rule priority

   Since Rule 1 is on top, its formatting (red) applies first and stops further rules.

3. Final Answer:

   Red -> Option C

4. Quick Check:

   Top matching rule color applies [OK]

Hint: Top matching rule color shows [OK]

Common Mistakes:

• Choosing green because 12 > 5
• Thinking both colors combine
• Assuming no color if multiple rules match

4. You have two conditional formatting rules:
1) Format cells if value < 50 (yellow)
2) Format cells if value < 100 (blue)
But cells with values less than 50 are showing blue instead of yellow. What is the likely fix?

medium

A. Move the yellow rule above the blue rule in the list

B. Change the yellow rule formula to value < 100

C. Delete the blue rule

D. Apply both rules to different ranges

Solution

1. Step 1: Analyze rule order and conditions

   The blue rule (value < 100) is likely above the yellow rule (value < 50), so it applies first.

2. Step 2: Fix priority by reordering rules

   Moving the yellow rule above the blue rule ensures values < 50 get yellow formatting first.

3. Final Answer:

   Move the yellow rule above the blue rule in the list -> Option A

4. Quick Check:

   Top rule priority fixes color conflict [OK]

Hint: Put specific rules above general ones [OK]

Common Mistakes:

• Changing formulas incorrectly
• Deleting needed rules
• Applying rules to different ranges unnecessarily

5. You want to highlight cells in column A with:
- Red if value > 100
- Yellow if value > 50
- Green if value > 0
How should you order these rules to ensure correct colors show without overlap?

hard

A. Red rule first, then Yellow, then Green

B. Green rule first, then Yellow, then Red

C. Yellow rule first, then Red, then Green

D. Order does not matter if formulas are correct

Solution

1. Step 1: Understand rule specificity

   Red applies to highest values (>100), Yellow to mid (>50), Green to lowest (>0).

2. Step 2: Order rules from most specific to least

   Place Red rule first, then Yellow, then Green to prevent lower rules overriding higher ones.

3. Final Answer:

   Red rule first, then Yellow, then Green -> Option A

4. Quick Check:

   Order rules from highest to lowest value [OK]

Hint: Order rules from highest to lowest value [OK]

Common Mistakes:

• Putting green rule first causing wrong colors
• Assuming order doesn't affect results
• Mixing rule order randomly