MO-201 · Question #2
On the "2019 Visitors" worksheet, modify the conditional formatting rule to format rows for parks that have a "Size" greater than 1,000 square miles.
Excel Conditional Formatting: Modifying a Rule for Row-Level Formatting Overall Goal The task asks you to edit an existing conditional formatting rule so that entire rows highlight when a park's "Size" column value exceeds 1,000 square miles. This is the correct approach because
Question
Explanation
Excel Conditional Formatting: Modifying a Rule for Row-Level Formatting
Overall Goal
The task asks you to edit an existing conditional formatting rule so that entire rows highlight when a park's "Size" column value exceeds 1,000 square miles. This is the correct approach because conditional formatting lets you apply visual cues dynamically - as data changes, formatting updates automatically without manual intervention.
Step-by-Step Breakdown
1. Navigate to the "2019 Visitors" worksheet You must be on the correct sheet before making any changes. Conditional formatting rules are sheet-specific; editing on the wrong sheet modifies the wrong rule or creates a new one elsewhere.
2. Open the Conditional Formatting Rules Manager Go to Home → Conditional Formatting → Manage Rules. This is where existing rules live. You must modify the existing rule (not create a new one) - creating a duplicate would cause conflicting rules and unpredictable formatting behavior.
3. Confirm "Show rules for: This Worksheet" The dropdown defaults to the current selection. If it shows "Current Selection," you may not see all rules. Switching to "This Worksheet" ensures you find the rule applied to the data range.
4. Select the existing rule and click "Edit Rule" You're modifying, not replacing. Clicking "New Rule" would leave the old rule active alongside the new one, likely causing conflicts or redundant formatting.
5. Change the rule type to "Use a formula to determine which cells to format" Row-level formatting (where the entire row highlights based on one column's value) requires a formula-based rule. A simple "Format cells that contain" rule only formats the individual cell matching the condition - it cannot span an entire row.
6. Enter the formula: =$C2>1000 (assuming Size is in column C)
- The
$before the column letter (absolute column, relative row) is critical. It locks the formula to always check column C regardless of which cell in the row is being evaluated, while allowing the row number to shift as Excel checks each row. - If you used
$C$2(fully absolute), only row 2 would ever be evaluated - no other rows would format. - If you used
C2(fully relative), the column reference would shift as Excel moves across columns, breaking the logic.
7. Confirm the "Applies to" range covers full rows
The range should look like $A$2:$Z$100 (or whatever spans the full table rows), not just a single column. If this only covers one column, only that column's cells will be formatted - not the whole row.
8. Set the format (fill color, font, etc.) and click OK The format is what becomes visible when the condition is true. Without defining a format, the rule exists but produces no visible change.
What Goes Wrong If Steps Are Skipped
| Skipped Step | Result |
|---|---|
| Using "New Rule" instead of "Edit Rule" | Two conflicting rules; unpredictable or doubled formatting |
Wrong column lock ($C$2 vs $C2) | Only one row formats, or columns shift the reference incorrectly |
| Applies-to range covers only one column | Only that column's cells highlight, not full rows |
| Not switching to "Use a formula" rule type | Cannot reference another column to drive row-wide formatting |
Memory Tip
"Dollar sign locks the column, frees the row." For row-based conditional formatting, always anchor the column (
$C) but leave the row number free (2), so Excel can walk down each row independently while always checking the same column.
Think of it as: one column decides the rule, the whole row feels the consequence.
Topics
Community Discussion
No community discussion yet for this question.