Household Budget Spreadsheet Guide

Creating a budget spreadsheet can help a household see where the money goes each month by matching planned and actual spending, with overspending clearly highlighted. Setting it up only takes a few basic formulas and formatting steps.

Set up the monthly layout

Set up the spreadsheet with income at the top, expenses in the middle, and a running balance that subtracts actual spending from income. This clearly shows the budget surplus or deficit.

South African budget spreadsheets commonly include categories like:

• Rent or bond repayment
• Rates and levies
• Electricity and water
• Groceries
• Data and airtime
• Medical aid
• Insurance
• Debt repayments
• Savings
• Entertainment and eating out

The basic layout should have columns for each:

• Category
• Monthly budget
• Actual monthly spending
• The difference between budget and spending

Then add a balance cell that shows the surplus or shortage for the month.

Add categories and formulas

Fill in the category list and the formula cells for each:

• Actual monthly expenditure = SUM of all the actual expense amounts for that month
• Difference = Budgeted amount - Actual amount
• Summary balance = SUM of all income for the month - SUM of all actual expenses for the month

So the formulas should be:

• The difference column = Budgeted amount - Actual amount, using =B7-C7
• The balance cell = Total income - Total actual expenses, using =SUM(C3:C5)-SUM(C7:C16), with income amounts in C3:C5 and actual expenses in C7:C16

Flag overspending

Highlight any cell where the difference between planned and actual spending is negative.

• In Calc, select the Difference cells, choose Format > Conditional > Condition, set Cell value is, less than, 0, choose the style Bad and click OK.
• In Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than, type 0 and click OK.

Conditional formatting is copied along with the sheet each month.

See the pattern and start next month

Once spending zigzags in the difference column and balances tell the story, visualise the data in a supportive graphical style.

Most spreadsheet tools have a chart tool that suggests charts. In Calc, select the Category and Actual columns (hold Ctrl), choose Insert > Chart and pick Bar or Column; in Excel, use Insert > Recommended Charts.

Each month, it’s easiest to copy the sheet for the next month, which extends cells, formulas, formatting, and gained insights.

• In Calc, right-click the sheet tab, choose Move or Copy Sheet, select Copy, type a new name and click OK.
• In Excel, right-click the sheet tab, choose Move or Copy, then tick "Create a copy".
• In Google Sheets, right-click the sheet tab and choose Duplicate.

Copying does not reset anything, so clear last month's Actual amounts on the new sheet; the formulas and formatting stay in place.

Keep it simple

So just enter the income rows and the category list, fill in those key formulas, then copy the sheet for each new month. The difference column flags overspending so you can follow balances faster. Copying a sheet readies the next month’s sheet. Then fill in projected income, budgeted categories, and daily as-you-go.

You’re set to take control of your financial management with no more paper, guessing or brain-load. Just track your day-to-day, month to month: Track your spending with small practices. The steps make budget-sharing easy too: just copy the link or email the spreadsheet file. Soon, you’ll see where the money’s going and consider future household budget-keeping and money-saving with interactive financial insight.