Excel Budget Template Generator

About this template

This builds a year-at-a-glance budget: category names down the left, one column per month across the top, and a Total column at the right that adds a category's twelve months together. It is the shape most people draw by hand on the first sheet of a new workbook, with the arithmetic already wired up so the first thing you do is type a number rather than a formula.

It suits anyone budgeting against a monthly rhythm - a household planning rent, utilities and groceries; a freelancer whose income lands unevenly and who needs to see which months are thin; a small team tracking a departmental spend. It is a planning sheet rather than a ledger: one figure per category per month, not a list of individual transactions.

With the default options the sheet reads like this. The title banner is row 1. Row 3 is the header: Category in A, the twelve months in B to M, Total in N. Row 5 opens the Income band, its categories follow it, and row 9 is Total income. Row 11 opens the Expenses band, its categories follow, and row 21 is Total expenses. Row 23 is the Net row, which subtracts one from the other for every single month and for the Total column.

Cream cells are yours to type in and hold nothing at all when the file is generated - there is no sample data to delete. The pale blue-grey cells are formulas, and the two banded rows are the section totals. Nothing is hard-coded: change a category name, add a row, or overwrite a figure and every total downstream moves with it.

How to use it

1. Set the shape before you download - The number of months, the starting month, the currency and both category lists are all decided on this page. Editing a category name in Excel afterwards is trivial; adding a thirteenth month column is not, so get the month count right here first.
2. Type your monthly figures into the cream cells - With the defaults that is B6:M8 for income and B12:M20 for expenses. Leave a cell blank rather than typing 0 for a month you have not planned yet - a blank and a zero add up the same, but a blank is easy to spot.
3. Read the three formula rows - Row 9 gives total income per month, row 21 total expenses, and row 23 the net figure. Column N does the same across the year, so N23 is your projected surplus or shortfall for the whole period.
4. Add a category without breaking the totals - Right-click a row inside a block - say row 7, which sits between the first and last income category - and choose Insert. Excel widens SUM(B6:B8) to SUM(B6:B9) for you. Inserting immediately above the total row instead lands outside the range, and you would have to edit the SUM by hand, so always insert within the block.
5. Delete rather than clear when you drop a category - Deleting the whole row shrinks every SUM automatically. Selecting the cells and pressing Delete only empties them, which leaves an unlabelled gap in the middle of the section.
6. Reuse it next year - Right-click the Budget tab, choose Move or Copy, tick Create a copy, and rename the new sheet. Every formula points at cells on its own sheet, so the copy works on its own.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =SUM(B6:B8) - Row 9, one per month column: the total of every income category for January. The same formula, shifted a column at a time, runs across to the Total column, and row 21 repeats it for the expense block.
• =SUM(B6:M6) - The Total column, N6 here: one category summed across all twelve months. Every category row carries its own copy, which is why the right-hand column stays correct when you type into any month.
• =B9-B21 - The Net row, row 23: total income for the month minus total expenses for the same month. It reads the two total rows rather than the categories, so adding a category anywhere feeds through without touching this row.

Customising it

• Change the currency after the fact by selecting the money cells and using Format Cells - the formulas hold plain numbers, and the symbol is only a number format written at generation time.
• Flag a negative month: select B23:N23, then Home > Conditional Formatting > Highlight Cells Rules > Less Than, enter 0, and pick the red fill. The Net row then tells you at a glance which months do not balance.
• Track plan against actual by copying the sheet twice, naming one Plan and one Actual, and putting =Actual!B6-Plan!B6 on a third sheet laid out identically.
• Add a share-of-income column: in O6, next to the Total, enter =N6/$N$9 and format it as a percentage, then fill it down the expense rows to see what each one costs you as a fraction of what comes in.
• The header row and the category column are frozen already, so scrolling right to December keeps the labels in view. Add Data > Filter on row 3 if you want to hide categories rather than delete them.