Conditional formatting in Excel for finance reports: variances, thresholds and the rules that matter

Conditional formatting in Excel changes a cell's format, such as its fill or font color, when a rule you set is true. In a finance report its job is to point the reader at the few lines that need attention. The rule that does that best flags a variance only when it is material in both percentage and amount: on the eight cost lines below, it colors three rows and leaves the five that do not matter alone.

What conditional formatting does, and where it is

Every rule lives under Home > Conditional Formatting. Excel evaluates the rule for each cell in the rule's range, and formats the cells where it is true. Rules recalculate with the sheet, so when next month's actuals are pasted in, the colors move with the numbers.

New rules start at Home > Conditional Formatting > New Rule; existing ones are edited in Home > Conditional Formatting > Manage Rules, the Conditional Formatting Rules Manager (Microsoft Support).

The built-in rules finance reports use

These cover a single column judged on its own values. A variance report needs more: a format on the whole row, decided by two other cells. That takes a formula rule.

Formula rules and formatting based on another cell

Choose New Rule > Use a formula to determine which cells to format, enter a formula that returns TRUE or FALSE, click Format, pick the fill, and click OK. Microsoft's guide to AND, OR and NOT notes that in a conditional formatting rule "you can omit the IF function and use AND, OR and NOT on their own."

Two rules about references decide whether it works:

1. Write the formula for the top-left cell of the range. If the rule applies to $A$2:$E$9, write it as if for row 2. Excel shifts it for every other cell.
2. Lock the column, not the row. $E2 keeps column E for every cell across the row, so the whole row is judged by its variance percentage, while the row number moves from 2 to 9. $E$2 would judge every row by row 2; E2 would drift sideways to F, G and H as Excel moves across the row.

Worked: budget vs actual for eight cost lines

Columns: A line, B budget, C actual, D variance, E variance %. In D2 and E2, filled down to row 9:

=C2-B2
=IF(B2=0,"",D2/B2)

Select A2:E9 and add two formula rules. Red, for cost lines over budget:

=AND($D2>0,ABS($E2)>0.05,ABS($D2)>=5000)

Green, for cost lines under budget:

=AND($D2<0,ABS($E2)>0.05,ABS($D2)>=5000)

The check: the variance column must sum to the total variance, 32,000 + 11,000 + 2,200 − 1,500 − 7,000 − 1,000 − 8,000 + 0 = 27,700, which is 716,700 − 689,000. Contractors alone explain more than the whole net overspend, which is exactly the line the red row puts in front of the reader.

The two-condition materiality rule

Each test alone fails, in opposite directions.

• Percentage only (=ABS($E2)>0.05) also flags Utilities: 24.4% over, but only $2,200. Small lines swing by large percentages and fill the page with color.
• Amount only (=ABS($D2)>=5000) also flags Salaries: $11,000 over, but 2.6% on a $420,000 line is inside normal noise. Large lines cross any fixed amount by small percentages.

Both together leave Contractors, Travel and Training: lines that moved by a meaningful share and a meaningful amount. The 5% and $5,000 here are illustrative; set them per report, from what the audience can act on and how much each line usually moves. Setting thresholds so the weekly digest is not noise covers how, and the glossary defines a threshold.

Favorable vs unfavorable

Red for "over budget" is right for costs and wrong for revenue: revenue over budget is good news. Add a type column F holding Cost or Revenue, and let the rule pick the direction:

=AND(IF($F2="Cost",$D2>0,$D2<0),ABS($E2)>0.05,ABS($D2)>=5000)

That is the unfavorable rule; swap the two comparisons inside the IF for the favorable one. A mixed P&L can then share one pair of rules.

Do not rely on color alone. The W3C's WCAG success criterion 1.4.1 says color should not be "the only visual means of conveying information." Keep the sign on the variance, or add an icon set or an F/U column, so a reader who cannot tell red from green, or prints in black and white, still sees the flag.

Managing rules

• Order. Rules are evaluated top to bottom in the Rules Manager; move the one that should win to the top.
• Stop If True. Tick it on a rule to stop lower rules applying when it is true.
• Applies to. One rule covering $A$2:$E$9 is easier to maintain than eight copies. Edit this box rather than adding rules.
• Format Painter. Copies rules to another range; check the Applies to range afterwards.
• Copied and inserted rows. Copying rows and inserting rows can split one rule into many fragments with broken ranges. When the Rules Manager shows a dozen near-identical rules, delete them and rebuild one.

Where it goes wrong

• Wrong reference style. $E$2 compares every row with row 2; E2 without the dollar on the column shifts sideways across the row.
• A percentage-only threshold that colors small lines and misses large ones.
• Color as the only signal. Add a sign or an icon for readers who cannot distinguish red and green.
• Rules multiplied by copying and inserting rows, until the Rules Manager holds dozens of fragments.
• Numbers stored as text, which value comparisons silently skip. Convert them before the rule can see them.

The same rules make a whole report readable; see them on a full report in the monthly commercial pack. For a whole report page, the Excel dashboard guide puts flagged variances next to charts.

Board decks that flag themselves

Colored cells tell the reader where to look; they do not say why. Covirage's tools set thresholds per measure from its own history, compute which lines crossed them in both percentage and amount, and put those first; the external AI model writes the explanation from the computed variances and never calculates them. See board reporting for a deck that leads with what crossed its threshold, and a board narrative with a citation on every number for the step from a flagged cell to a cited sentence. For the layout these rules sit on, see the budget vs actual template, and for explaining the lines they flag, see variance analysis.