Purpose
By the end of this lesson, you will be able to apply conditional formatting rules that make patterns and exceptions visible at a glance.
Lesson Explanation
Conditional formatting (Home → Conditional Formatting) changes a cell’s appearance – color, font, borders – based on its value, without changing the underlying data. This turns a table of numbers into something a reader can scan visually rather than read line by line.
A common business use: highlighting cells red if a value falls below a threshold. Selecting a range and choosing “Highlight Cell Rules → Less Than” with a value of 0 automatically colors every negative number red – useful for instantly spotting budget shortfalls in a column of hundreds of rows.
Color scales apply a gradient across a range based on relative value (e.g., green for high, red for low), useful for spotting patterns across a large dataset, like which months had the strongest and weakest sales.
Data bars draw a small bar inside each cell proportional to its value, letting a reader compare magnitudes visually without needing to read every number. Icon sets (arrows, traffic lights, stars) categorize values into visual tiers, like marking performance as green/yellow/red based on target thresholds.
A more advanced option, a custom formula rule, applies formatting based on a formula rather than a built-in condition: =B2>C2 applied to a range could highlight any row where actual spending (B2) exceeds budget (C2), even though the comparison involves two different cells rather than a fixed value.
Practice Questions
1. A budget spreadsheet needs every negative value in column D to automatically turn red. What conditional formatting rule accomplishes this?
View Answer
“Highlight Cell Rules → Less Than,” with a value of 0, applied to the range in column D.
2. What is the key difference between conditional formatting and simply manually coloring cells red?
View Answer
Conditional formatting updates automatically as the underlying values change, while manual coloring stays fixed and must be redone if the data changes.
3. A manager wants to visually compare monthly revenue figures across 12 months without reading every number individually. What conditional formatting tool would help most, and why?
View Answer
A color scale (or data bars) – both let the reader visually compare relative magnitude across the range at a glance, rather than reading each number.
4. A spreadsheet needs to highlight any row where “Actual Spend” (column B) exceeds “Budget” (column C), a comparison between two different cells rather than a fixed number. What kind of conditional formatting rule is needed?
View Answer
A custom formula rule, e.g., =B2>C2 applied to the relevant range.
5. What do icon sets (like traffic-light colors) typically represent in a business spreadsheet?
View Answer
Categorized performance tiers – often green/yellow/red – based on whether values meet, approach, or fall short of a target threshold.
6. A report uses data bars to show quarterly sales by region. What do the data bars’ relative lengths represent?
View Answer
The relative magnitude of each value compared to others in the range – a longer bar means a larger value.
7. A conditional formatting rule is applied to highlight cells “Less Than 0” in a column, but a cell showing -50 remains unformatted. What is one likely explanation?
View Answer
The cell might contain a value stored as text rather than a true number, which conditional formatting numeric rules typically don’t evaluate the same way.
8. A team wants to flag any expense report submitted more than 30 days after the purchase date, using a custom formula rule. If purchase date is in column B and submission date is in column C, write the formula.
View Answer
=(C2-B2)>309. Why might conditional formatting be described as changing “appearance” rather than the underlying data?
View Answer
The actual values in the cells remain completely unchanged – only how they visually display (color, icons, bars) is affected by the rule.
10. A color scale is applied to a column of exam scores, coloring the highest scores green and the lowest red. If a new, very high score is added to the dataset, what happens to the coloring of the other cells?
View Answer
The color scale recalculates relatively across the whole range, so other cells’ colors may shift slightly since the new highest value changes what counts as “high” on the gradient.
11. A spreadsheet uses a custom formula conditional format =B2>C2 to flag overspending, applied to the range B2:C50. Why is it important that this formula uses relative references rather than absolute ones in this context?
View Answer
Relative references allow the same rule to correctly compare each row’s own actual-vs-budget pair (B3 vs C3, B4 vs C4, etc.) as it’s applied down the range, rather than comparing every row to the same fixed cell.
12. A dataset has both a color scale and data bars applied to the same column. What genuinely different kind of visual information does each convey?
View Answer
The color scale conveys where a value falls on a high-to-low gradient relative to the rest of the range; data bars convey the value’s magnitude directly and proportionally within each individual cell.