Budget vs actual: variance, percent and an Over/OK flag

budget finance conditional-formatting

Variance in money and percent, an Over/OK flag, and a total, with no divide-by-zero error.

The problem

You want to see at a glance which categories ran over budget and by how much.

Sample data

Formulas (row 2, fill down)

Variance D2:   =C2-B2                          -> 0, 60, -60, 20
Variance % E2: =IF(B2=0,"",(C2-B2)/B2)        -> 0, 15%, -40%, blank
Flag F2:       =IF(C2>B2,"Over","OK")          -> OK, Over, OK, Over
Total variance: =SUM(C2:C5)-SUM(B2:B5)         -> 20

The IF in the percent column stops Misc (budget 0) from showing #DIV/0!. Format column E as Percent.

Red and green

Add a conditional-format rule on F2:F100: text equals "Over" gives red, "OK" gives green. Or use the custom formula =$C2>$B2 for red.

Excel vs Sheets

Identical in both.

Want it done for you?

The Cellwise personal budget template and the freelancer finance tracker include this layout. (Listing links will be added when they are live.)

Tested on sample data by the Cellwise checker (log CW-PL-20261021-56). AI-made: the text, voice and visuals are generated by AI.

Checked before publishing: a check.