How to Create a Budget in Excel (Step by Step)

Step 1 — Set up the structure

A working budget has three sections: Income, Expenses, and Summary. Keep them on separate rows with clear headers.

Column layout:
A: Category
B: Budget (planned amount)
C: Actual (what you spent/received)
D: Variance (=C-B for income, =B-C for expenses)
E: Variance % (=D/B*100)

Step 2 — Income section

Income categories:
- Salary / main income
- Freelance / side income
- Other income

Total income: =SUM(B2:B10)  (sum all income rows)

Step 3 — Expense categories

Fixed expenses (same every month):
- Rent / mortgage
- Loan repayments
- Insurance
- Subscriptions

Variable expenses (change monthly):
- Groceries
- Transport
- Utilities
- Entertainment
- Eating out

Total expenses: =SUM(B15:B30)

Step 4 — Summary and variance

Net position: =Total_Income - Total_Expenses
Savings rate: =Net_Position / Total_Income * 100

Variance tracking:
=C2 - B2     (actual minus budget — positive = over budget for expenses)
=ABS(D2)/B2*100    (percentage variance)

Step 5 — Monthly tracking

Add columns for each month: Jan Budget, Jan Actual, Feb Budget, Feb Actual... or use separate sheets per month with a summary sheet pulling totals via 3D references.

=SUM(Jan:Dec!B5)
-- Sum the same cell across all monthly sheets

💡 Use conditional formatting for variance

Apply red conditional formatting to variance cells where actual > budget for expenses. At a glance you can see which categories are overspent without reading every number.

Now practise it for real

Practice these formulas in the Small Business track — 100 exercises covering cash flow, invoicing, and business analysis. Free to start.

Start the Small Business track free →

How to create a budgetin Excel, step by step