How to build a monthly personal budget in Excel step by step

Reaching the end of the month without knowing where the money went is more common than you might think. You don’t need a paid app to fix it: a well-organized Excel sheet shows how much you earn, how much you planned to spend and how much you actually spent in each category.

In this guide you will build a monthly budget from scratch, with two sheets (summary and transactions), formulas that update themselves and colors that warn you when you overspend. All the formulas work in any version of Excel.

What you need before you start

Before opening Excel, gather this information. It takes a few minutes and makes your budget realistic:

• Net income for the month: salary, side jobs, benefits.
• Bank statements from the last two or three months, so you know what you really spend, not what you think you spend.
• Fixed expenses and their amounts: rent or mortgage, utilities, insurance, subscriptions.
• A short list of categories. Between 8 and 12 is enough; with more, tracking becomes tedious and you end up giving up.

Step 1: create the summary sheet

Open a new workbook and rename the first sheet Budget. In row 1 type the headers: Category (A), Type (B), Planned (C), Actual (D) and Variance (E). Then fill in the categories. Here is an example with made-up data:

A tip: treat savings as one more expense that you “pay” at the start of the month. If you only save whatever is left over, there is hardly ever anything left.

Step 2: add the variance and total formulas

The Variance column

We want a positive number to always mean “good” and a negative one “bad”. For income, earning more than planned is good; for expenses, spending less is good. Type this formula in E2 and copy it down:

=IF(B2="Income",D2-C2,C2-D2)

The summary block

In columns G and H, create a small summary. In G2, G3, G4 and G5 type “Actual income”, “Actual expenses”, “Balance” and “Savings rate”. In H2, add up the actual income:

=SUMIF(B2:B9,"Income",D2:D9)

In H3, the actual expenses:

=SUMIF(B2:B9,"Expense",D2:D9)

In H4, the balance with =H2-H3. And in H5, the percentage of your income you have saved (format it as a percentage):

=IFERROR(D9/H2,0)

With the sample data you get 2,220 in income, 1,730 in expenses, a balance of 490 and a 13.5% savings rate. That positive balance is money you can also move to savings or keep as a cushion for next month.

Step 3: log transactions on a separate sheet

Typing the actual spending by hand in the summary sheet works, but it is easy to make mistakes. It is better to record each transaction on a separate sheet and let Excel do the adding.

1. Create a sheet called Transactions with the headers Date (A), Description (B), Category (C) and Amount (D).
2. Select column C and go to Data › Data Validation. Under Allow choose List and, under Source, select the range =Budget!$A$2:$A$9. This way you can only pick categories that exist, and you won’t end up with “Grocery” in one row and “Groceries” in another.
3. Go back to the Budget sheet and type this formula in D2, which adds up every transaction in that category. Copy it down:

=SUMIF(Transactions!C:C,A2,Transactions!D:D)

Record your income in Transactions too, with its category (Salary, Side jobs). All amounts go in as positive numbers: the Type column in the summary already tells income and expenses apart.

Step 4: use colors to spot variances at a glance

Conditional formatting saves you from reading numbers. To highlight in red the categories where you overspent:

1. Select the range E2:E9.
2. Go to Home › Conditional Formatting › Highlight Cells Rules › Less Than.
3. Type 0 and choose the light red fill format.

If you prefer a text label, add a status column in F2 and copy it down:

=IF(E2<0,"Review","OK")

In the example, Groceries, Utilities and Leisure show up as “Review”. That is exactly the kind of information you want: where to adjust next month.

How to split your money: a simple reference

If you don’t know what figures to put in the Planned column, a widely used guideline is the 50/30/20 rule: 50% for needs (housing, food, transport, utilities), 30% for personal spending and leisure, and 20% for savings or debt. It is not a fixed rule, but it works as a starting point. To calculate each share from the actual income in H2:

=$H$2*0.5

Replace 0.5 with 0.3 or 0.2 for the other two shares. If your rent already takes more than half your salary, adjust the percentages to your situation instead of forcing the rule.

Common mistakes

• Forgetting annual expenses: insurance, taxes or tuition. Divide the amount by 12 and add an “Annual expenses” category to set that sum aside every month.
• Amounts stored as text, for example copied from your bank with symbols or spaces. SUMIF ignores them and the total comes out too low. If you see numbers aligned to the left, check them.
• Ranges that don’t grow: if you add a category below row 9, extend the ranges in the summary formulas. Turning the list into a table with Ctrl + T avoids this.
• Only checking at the end of the month. Take a look every week; halfway through the month you can still correct course.

Save, share and archive each month

The most practical approach is to keep one copy of the workbook per month (Budget_01.xlsx, Budget_02.xlsx…) or one sheet per month in the same workbook. If you share expenses with a partner or housemates, send the summary in a format nobody can change by accident: with our free tool you can convert the Excel file to PDF and send or print it as is.

At the end of the year, if you want to analyze all twelve months together, you can merge the Excel files for each month into one and calculate the annual totals on that sheet.

Conclusion

A good personal budget in Excel doesn’t need macros or complicated templates: a summary sheet, a transactions sheet, three or four formulas with SUMIF and IF, and conditional formatting that flags variances. What matters is consistency. Log your expenses every few days, review the variances every week and adjust next month’s budget with what you have learned.

Frequently asked questions