Spreadsheets

Conditional formatting to turn over-budget cells red in Excel

Turn over-budget cells red in Excel with one formula rule, =$C2>$B2, and learn exactly which dollar signs to lock for a budget versus actual table.

Editorial illustration for Conditional formatting to turn over-budget cells red in Excel

To turn over-budget rows red in Excel, select the table rows, choose Conditional Formatting, then New Rule, then Use a formula to determine which cells to format, and enter =$C2>$B2 with a red fill. The dollar sign before each column letter keeps the test pointed at columns B and C while the row number moves down, so every row is judged on its own budget and actual.

What are the steps, in order?

Say your table has Category in A, Budget in B, Actual in C and Left in D, with headings in row 1 and data from row 2 to row 20.

  1. Select A2:D20, from A2 to the bottom right cell, so the whole row will colour and not only the actual figure.
  2. On the Home tab choose Conditional Formatting, then New Rule.
  3. Pick Use a formula to determine which cells to format.
  4. In the formula box type =$C2>$B2.
  5. Click Format, open the Fill tab, pick a light red, and on the Font tab pick dark red text. Press OK twice.

Why is the dollar sign placed like that?

The formula is written from the point of view of the first selected cell, A2, and Excel then shifts it for every other cell. The dollar signs decide what is allowed to shift.

FormulaWhat happens
=C2>B2Nothing is locked, so the test for cell B2 compares D2 with C2 and goes wrong. Only column A looks right.
=$C2>$B2Column locked, row free. Every cell in a row compares that row's actual with that row's budget. This is the one to use.
=$C$2>$B$2Everything locked. Every cell compares row 2 only, so the whole table is red or none of it is.

A common slip is clicking a cell while the formula box is open: Excel inserts an absolute reference such as $C$2 by itself. Delete the extra dollar signs on the row number.

What if the budget is blank or you want a warning first?

  • Skip rows with no budget: =AND($B2<>"",$C2>$B2)
  • Amber when spending passes 90 percent of the budget: =$C2>=$B2*0.9. Create it as a second rule with an amber fill and put the red rule above it in Manage Rules, so over-budget rows stay red.
  • Highlight only the Actual cell: apply the same formula to C2:C20 instead of A2:D20.

To check, type a budget of ₹5,000 in B2 and an actual of ₹5,600 in C2. The row should turn red because 5,600 is greater than 5,000. Change C2 to ₹4,800 and the colour should go.

How do you fix the rule later?

Go to Conditional Formatting, then Manage Rules, and look at the Applies to column. If you insert rows or paste cells, the range can fragment into several pieces, or stop short of the last row. Edit the range to $A$2:$D$20 or whatever you need. Choose This Worksheet in the Show formatting rules dropdown to see everything on the sheet.

Before you rely on it

  • Confirm that budget and actual are real numbers and not text (left-aligned numbers are a warning sign).
  • Test with one row you know is over and one you know is under.
  • If the sheet grows each month, convert the data into an Excel table so the rule range extends.

If you share the file, add a small legend above the table saying red means actual is above budget. A colour with no explanation tends to get ignored or misread by whoever opens the file next.

The rule only colours. When you want the rows themselves built from a bank or card statement, SpendixAI can take the upload and let you review the lines before saving, though the red-cell trick above is a handy check on any sheet.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports