To highlight overspending in Excel, put each category's budget and its actual spending side by side, select the rows, and add a conditional formatting rule with a formula such as =$C2>$B2. Excel then colors every category where actual spending passed the budget. The rule recalculates as soon as new statement rows change the totals.
What counts as overspending in an Excel budget sheet?
Overspending in a budget sheet means a category's actual spending for the period is greater than the amount you budgeted for it. The rule compares two numbers on the same row: the budget and the actual total. Everything else, such as colors and warning levels, is built on that single comparison.
Both numbers must cover the same period. A monthly budget compared with a year-to-date total will show every category as overspent by March. Decide on the period, usually one calendar month, and make sure the actual column adds up only that month's statement rows.
The actual column should also hold spending only. Card payments, savings transfers and moves between your own accounts appear on a statement but are not spending. Leave them in and a category looks overspent when nothing was bought. Tagging and filtering those rows is covered in excluding transfers from budget totals.
Budget and actual columns the overspending rule needs
The sheet needs one row per category with three columns: category name in A, budget in B and actual spending in C. A fourth column, D, can show the difference as =B2-C2. Keep amounts as positive numbers in both B and C, so "more" means the same thing in each column.
An example layout, with made-up figures:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Budget | Actual | Left |
| 2 | Groceries | 450.00 | 487.35 | -37.35 |
| 3 | Fuel | 140.00 | 118.60 | 21.40 |
| 4 | Eating out | 120.00 | 131.00 | -11.00 |
| 5 | Subscriptions | 40.00 | 38.97 | 1.03 |
| 6 | Clothing | 60.00 | 0.00 | 60.00 |
Actual spending usually comes from a SUMIFS over the statement rows, with the category and the month as criteria. Statement amounts for purchases are often negative, so put a minus sign in front of the SUMIFS to make the total positive.
If you budget purchases on a credit card, count them from the card statement, not from the payment that leaves checking. Otherwise each purchase is counted twice. The full approach is in budgeting with a credit card without double counting.
The conditional formatting formula that highlights overspending
The rule uses a formula that compares the actual column with the budget column, with dollar signs in front of the column letters only. That way, one rule covers every row and checks each row against its own budget. Groceries and eating out turn red in the example; the other three stay plain.
Steps in Excel:
- Select A2:D6, every category row and all four columns. Start the selection on row 2.
- Choose Home, Conditional Formatting, New Rule.
- Pick "Use a formula to determine which cells to format".
- Enter
=$C2>$B2. - Click Format, choose a light red fill and dark red text, and confirm.
The dollar sign before C and B locks the columns, so every cell in a row looks at that row's budget and actual. The row number has no dollar sign, so it moves down with each row. Writing =$C$2>$B$2 instead would color every row based on groceries alone.
Google Sheets has the same feature under Format, Conditional formatting, with "Custom formula is" as the condition. The formula is identical.
Warning colors for categories close to overspending
A second rule can flag categories that are close to their budget before they go over. Use a formula such as =$C2>=$B2*0.9 with an amber fill, and put it below the red rule. Categories at 90% or more of budget turn amber, and the red rule takes over once they pass 100%.
Rule order matters. In Conditional Formatting, Manage Rules, the rule nearer the top wins when two rules match the same cell. Keep the overspending rule above the warning rule. In the example, subscriptions at $38.97 of $40.00 is above 90%, so it shows amber, while groceries shows red.
Mid-month, the warning color is more useful than the red. A category at 90% on the 12th needs attention for the rest of the month.
Why does the overspending rule color the wrong rows?
The rule colors the wrong rows when its references are off by a row, when the actual amounts are stored as text, or when negative statement amounts reach the comparison unchanged. Each one has a quick check, and fixing it does not require rebuilding the rule.
- Off by one row. The selection started at row 1 but the formula says row 2, so each row checks the row below. Edit the rule and make the formula's row match the first selected row.
- Text instead of numbers. Amounts copied from a PDF often arrive as text, which sits on the left of the cell. Text compares differently from numbers, so the rule misfires. Convert the column with Data, Text to Columns, Finish.
- Negative actuals. If C holds -487.35, it is never greater than 450, so nothing turns red. Flip the sign in the SUMIFS.
- Blank budget cells. A category with no budget counts as zero, so any spending turns it red. Type 0 deliberately, or add
$B2<>""to the rule with AND.
The same rule works for a small business tracking costs against a plan, and a spreadsheet can carry that for a while. When it no longer does, small business accounting software cost per year sets out what the next step costs.
If your actual figures come from PDF statements, CheapBankConvert converts them to Excel files with amounts stored as numbers, so the comparison works when you paste. It is in early access, and you can get started to hear when it opens.