How to set up a yearly budget spreadsheet for 2027 (Excel and Google Sheets)

Last updated: 20 October 2026 · By Quicksloth

A yearly budget spreadsheet has to answer two questions: is this month on plan, and is the year on plan? It needs only three things: a list of categories with a monthly budget, one list of every money move, and two summaries built from that list. Here is the setup step by step, with one household's 2027 budget, their January checked against it, and their first three months read as a year.

Short version: give every category a monthly budget; the year budget is 12 × that. Write every money move once, in one list, with a category: income, bills, spending, savings and debt payments. A month's actual is the sum of its rows by category. On the year view, compare what you've spent so far with the part of the year that has passed (after March, about a quarter), not with the whole year.

This is a worked example of a calculation, not financial advice. Your own pay, bills and categories will be different.

Step 1: the settings

A made-up couple, Sam and Alex, set up their 2027 budget. Before any category, the file needs five answers:

The three-paycheck months come back in step 4: they are the main reason a monthly budget and a real month don't match. How they are counted is in How to budget by paycheck.

Step 2: categories and a monthly budget

Every category gets a type, and the type decides where its money counts:

• Income: paychecks, side income, anything that comes in.
• Bill: fixed amounts with a due day (rent, utilities, insurance). The due day is what lets a paycheck budget know which paycheck pays them.
• Expense: day-to-day spending that changes every month (groceries, gas, eating out).
• Savings: money you move out of the budget on purpose (retirement, a savings account). Sinking funds, savings goals and debts get their own lists, but count the same way: savings in the Saved line, debt payments in the Debt line.

Sam and Alex's monthly budget, by group:

• Plan the left over on purpose. $370 a month is the margin for the things nobody budgets for. If it comes out below zero, the plan spends more than it earns before the year has even started: cut a budget now, not in March.
• The pay line is the tricky one. They budgeted $5,300 of pay a month, a round figure close to a year of pay spread over 12 months (26 paychecks × $2,450 ÷ 12 = $5,308.33). But no real month brings $5,300: a month brings two paychecks ($4,900) or three ($7,350). Step 4 shows what that does.

Step 3: one list of transactions

Every money move goes on one row: date, description, category, amount. Amounts are always positive; the category tells the file what the money did. Moving money to savings, a sinking fund or a debt is a row too: that's what makes the left over real. The first rows of Sam and Alex's January:

January ends with 39 rows: 3 paychecks, 1 side job, 7 bills, 18 day-to-day purchases, 6 transfers to savings, funds and goals, and 4 debt payments. Write them as they happen, or once a week from your bank app: one list is quicker to keep than a separate sheet per month.

Step 4: check the month

The monthly view adds up the rows of the month you pick, by group and by category, next to the budget. Left (budget − actual) works the same way on every line, with a word next to it that says what the number means: left to spend, still to come, over budget or above plan (shown with a +, because it's good news). Sam and Alex's January:

• Income +$1,990, above plan: three paychecks ($7,350, $2,050 over the pay budget) and a side job that brought $290 instead of $350.
• Bills −$4, over budget: electricity $122 and water $47 against $120 and $45. Fixed bills that move a little are normal; a bill that's always over needs a new budget.
• Expenses $150 left to spend: nothing on gifts ($50 under), groceries $614 of $650 ($36 under), health $30 of $50 ($20 under), and small amounts under in most other categories.

January's $2,506 is not the normal month. February and March bring two paychecks each and end with $29 and $118 left over: pay was $400 under budget in both. Over the three months the left over is $2,653, well above three months of budget (3 × $370 = $1,110), but only because of January's third paycheck. Two ways to budget pay that comes every 2 weeks:

• Average it (what Sam and Alex did): the two-paycheck months run a little short on pay and the three-paycheck months make it up. Fine as long as the short months don't empty the account.
• Budget two paychecks a month ($4,900 here): every normal month has to balance on its own, and the third paychecks (January, July and December in their 2027) become money you decide on when it arrives. For Sam and Alex that budget would be $30 short each month, so they'd trim $30 somewhere first.

Step 5: read the year so far

The year view puts the whole year's budget next to everything so far. After March it looks like this:

Don't read the Left column as a verdict. $49,660 of income still to come only means nine months of pay haven't arrived yet; $14,988 of expenses is what's left to spend in the rest of the year. The useful comparison is with the part of the year that has passed: after three months, a quarter of each line. Sam and Alex are $1,190 ahead on income, $10 over on bills, $363 under on expenses, and exactly on plan for savings and debt.

Month by month, the same rows:

By category, the same check: after a quarter, most categories should be near 25% of their year budget.

• Clothing at 27% is a little ahead ($15 over a quarter of the budget): worth a look, not a problem yet.
• Gifts at 8% isn't necessarily money saved. If most of your gift spending comes in one season, a monthly gift budget will look under for most of the year and then blow up. Money for costs that come once or twice a year is better set aside in a sinking fund, as they already do for Christmas: see Sinking funds: a worked example for irregular bills.

The year view also adds up the savings and debt lists. After March: sinking funds hold $1,830 of the $4,040 they aim for, savings goals $7,950 of $35,000, and $16,535 of debt is left (the $18,680 they started with minus $2,145 of payments; interest isn't in that number until you type the balance from your statement). Which debt to pay first is in Debt snowball vs avalanche: same debts, two plans.

The formulas, if you build your own

Everything above comes from two lists and a few sums. Say you have a Transactions tab with Date in column A, Description in B, Category in C and Amount in D, and a Categories tab with Category in A, Type in B and Monthly budget in C. On a monthly tab, put the first day of the month in B1 (for example 1/1/2027) and a category name in A5.

• Actual for one category in the month (A5, month in B1):
  =SUMIFS(Transactions!$D:$D,Transactions!$C:$C,$A5,Transactions!$A:$A,">="&$B$1,Transactions!$A:$A,"<"&EDATE($B$1,1))
  It adds the amounts of that category from the 1st of the month up to, not including, the 1st of the next month. Because it compares full dates, rows from other years never get mixed in.
• Year budget for one category: =12*VLOOKUP($A5,Categories!$A:$C,3,FALSE)
• Monthly budget of a whole group, for example all bills: =SUMIF(Categories!$B:$B,"Bill",Categories!$C:$C)
• Left over: income − bills − expenses − saved − debt payments, for the budget and for the actual.

We checked these formulas on Sam and Alex's rows: groceries $614 for January and $642 for February, paycheck $7,350 for January, bills budget $1,900, the same numbers as above. For the actual of a whole group, add a Type column to the transactions (a lookup of the category's type) and sum by type the same way.

Excel or Google Sheets

• The formulas above work in both, and in LibreOffice Calc: SUMIFS, SUMIF, VLOOKUP and EDATE are in all three.
• Google Sheets is free with a Google account. To use an .xlsx file there: File → Import → Upload. The import keeps the formulas, as long as they use functions both programs have.
• Sharing with someone on the other program? Leave out functions that only one of them has, like QUERY and GOOGLEFINANCE, which exist only in Google Sheets.
• Amounts have to be numbers. An amount stored as text (often one pasted from a bank statement or a web page) is skipped by the sums without warning. Unless you've changed the alignment, numbers sit on the right of the cell and text on the left: that's the quick check.
• Dates have to be real dates. A date stored as text won't match the month in SUMIFS. If a row doesn't show up in its month, check the date first.

What this example assumes

• The household, names and amounts are made up. The example has three months of transactions, January to March 2027.
• The Saved budget is fixed at $1,040 a month, the transfers they actually make. In our planner the Saved budget is worked out from the savings categories plus what each sinking fund and savings goal needs per month to reach its goal by its date, so it changes with today's date: in the example file you will see a different Saved budget and Left over budget from the ones here. Every actual number is the same.
• The pay budget is $5,300 a month; take-home pay is the same on every paycheck.
• Debt left is the starting balances minus payments, without interest.

We worked the numbers out twice: with the formulas of the spreadsheet, recalculated in LibreOffice on the example file, and with a separate script. Both give the same actuals, budgets and differences, to the cent, apart from the Saved budget explained above. We used AI tools to help write this article and the calculations.

Try it with your own numbers

Our Budget Planner is this setup, ready to fill: a Start Here tab for the year, pay frequency, first payday and take-home pay; 24 starter categories you can rename (up to 60) with a type, a monthly budget and a due day; one Transactions list with 2,000 rows and a Check column that flags a date typed as text or a category that isn't in your list; a Monthly tab for the month you pick, an Annual tab with every category month by month, and a Dashboard with the year so far. It works in Excel and Google Sheets and is coming soon to our Etsy shop.

Paid every 2 weeks or weekly? See How to budget by paycheck (weekly, every 2 weeks, twice a month). Saving for bills that don't come every month? See Sinking funds: a worked example for irregular bills. Paying off debt? See Debt snowball vs avalanche: same debts, two plans.

Not financial advice. The example shows how a yearly budget adds up under the assumptions above; it doesn't know your income, your goals or what your bills will really cost.