GUIDE · BUDGET SPREADSHEET

How to build a monthly budget spreadsheet (Google Sheets and Excel)

You need three tabs and four formulas. This works the same in Google Sheets and Excel, without macros. At the end there is one extra formula most templates skip: splitting installments and buy now pay later purchases across the months you actually pay them.

1. Categories tab

One row per category with its type and monthly budget. Name the tab Categories.

A: Type	B: Category	C: Monthly budget
Income	Salary
Expense	Rent	1,450
Expense	Groceries	480
Saving	Savings

2. Entries tab

One row per transaction. This is the only tab you type in day to day. Name it Entries, headers in row 1, data from row 2.

A: Date	B: Type	C: Category	D: Amount	E: Months
2027-01-03	Expense	Groceries	96.40
2027-01-12	Expense	Shopping	240.00	4

Make B and C dropdowns so spelling always matches: Data → Data validation → list from Categories!B2:B30. Leave E empty for normal purchases; put the number of payments for installments or BNPL.

3. Helper columns (for installments)

Add three columns to Entries. They work out which month a row starts in and how much lands in each month.

F2  (start month)   =IF(A2="",0,MONTH(A2))
G2  (months)        =MAX(1,N(E2))
H2  (per month)     =IF(A2="",0,ROUND(D2/G2,2))

Fill them down as far as your entries go. A 240 purchase in January over 4 months gives F=1, G=4, H=60.

4. Monthly tab

Categories down column A (link them: =Categories!B2), month numbers 1 to 12 across row 1 from B1 to M1. For simple totals without installments, SUMIFS is enough:

=SUMIFS(Entries!$D:$D, Entries!$C:$C, $A2,
        Entries!$A:$A, ">="&DATE(2027,B$1,1),
        Entries!$A:$A, "<"&DATE(2027,B$1+1,1))

To spread installments across the months they are paid, use this instead. It counts a row in every month from its start month to start month + months − 1:

=SUMPRODUCT((Entries!$C$2:$C$2000=$A2)
           *(Entries!$F$2:$F$2000<=B$1)
           *(Entries!$F$2:$F$2000+Entries!$G$2:$G$2000-1>=B$1)
           *Entries!$H$2:$H$2000)

With the example above, Shopping shows 60 in January, February, March and April instead of 240 in January. That is the number that tells you what is really left each month.

Add a total row for income, spending and savings with SUMIF on the Type column, then "money left" = income − spending − savings.

5. Red when over budget

Add a column N with the yearly total (=SUM(B2:M2)) and O with the yearly budget (=Categories!C2*12). Select N2:N30 → Format → Conditional formatting → custom formula =N2>O2 → red text. Do the same on each month against the monthly budget if you want monthly warnings.

Common mistakes

Category spelling. "Groceries" and "Grocery" are two categories to a formula; dropdowns prevent it. Whole-column ranges in SUMPRODUCT. They are slow; use a fixed range like rows 2 to 2000. Year rollover. The installment formula above uses month numbers within one year; an installment started in December needs its remaining payments entered again in the new year's file.

Rather skip the building? The 2027 Budget Planner for Google Sheets and Excel ($7) is this exact setup finished: dropdowns, the installment split, a dashboard with charts, a 12-month view and a bills checklist. Or start from the free version (entries and 12-month summary), or check your numbers first with the free 50/30/20 calculator.

Keep reading

How to budget your money · Sinking funds list · How to pay off credit card debt