A budget you only check at month end tells you what went wrong after it's too late. A table where spending against budget changes color shows which categories are in trouble every time you paste in new receipts, while there's still time to adjust.

1. Create a budget sheet

On a new sheet named Budget, list your categories and monthly budgets.

Category Monthly budget Spent this month % used Remaining
Groceries 600.00
Eating out 200.00
Household 150.00
Transport 120.00
Fun 100.00

Category names must match the Category column on your spending sheet exactly, or the totals won't add up. A Data Validation list based on this column keeps them consistent.

2. Total this month's spending automatically

Assume the Spending sheet has dates in column B, amounts in column D and categories in column F. Paste in the date, store and total from the receipt tool and fill in categories.

  • Spent this month (C2), counting only the current month: =SUMIFS(Spending!D:D, Spending!F:F, A2, Spending!B:B, ">="&EOMONTH(TODAY(),-1)+1, Spending!B:B, "<="&EOMONTH(TODAY(),0))
  • % used (D2): =IF(B2=0, "", C2/B2) with a percentage format
  • Remaining (E2): =B2-C2

EOMONTH(TODAY(),-1)+1 is the first day of this month and EOMONTH(TODAY(),0) the last. When the month changes, the formulas follow without edits. To look back at a past month, replace TODAY() with a cell containing a date in that month.

3. Color by percentage used

Select the % used cells (D2:D6), choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and create two rules:

  1. =D2>1 with a red fill (over budget)
  2. =AND(D2>=0.8, D2<=1) with a yellow fill (80% or more)

In Manage Rules, put the over-budget rule first. To color the whole row, select A2:E6 and anchor the column in the formula, as in =$D2>1.

4. Show progress with data bars

Colors don't always give a feel for how much is left. Add Conditional Formatting > Data Bars to the % used cells and each cell fills like a progress bar. In Edit Rule, set the minimum to the number 0 and the maximum to the number 1, so a full bar means exactly 100% of the budget.

5. Mid-month check: spending vs time elapsed

Being at 50% halfway through the month is fine. To see whether a category is running ahead of the calendar, add a month elapsed figure.

  • Month elapsed (G1): =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))
  • Conditional formatting rule: =D2>$G$1+0.15, flagging categories more than 15 points ahead of the calendar

This catches categories that drain quickly early in the month, which a simple over-budget rule only flags once it's too late.

6. How often to paste in receipts

The colors are only as current as your spending sheet. Rather than logging every day, turn a week of receipt photos into rows and paste them in once a week, and the budget sheet reflects where you stand that week.

Summary

A budget sheet by category, SUMIFS with EOMONTH for this month's totals, and conditional formatting on % used move the "we're over budget" moment from month end to mid-month. Add a weekly receipt paste and you're set.