Tutorial: Building A Basic Budget In Excel (For The Absolute Excel Beginners) Part 1

Excel is an awesome programme for personal budgeting. I have been using it now for 3 years. When you create your own budget yourself, you can customise it to meet your own needs and circumstances exactly.

This tutorial starts from the very beginning for those who have never met a spreadsheet before. I use Excel 2007 so if you’re using the older version of Excel, you will find the same functions in the tool bar and menu bar. If you don’t have Excel, then the free alternatives (Open Officeor Google docs) are pretty similar and this tutorial should still, I hope, be helpful.

Click each diagram for a larger view. If there is something that I haven’t explained very well, then please let me know. If you have any questions, drop them in the comments below or feel free to email me.

1. Start a new document, give it a heading and type in the months along the next row starting at B2 (tip: if you type in January and click on the small black box (fill handle) in the bottom right hand corner of your cell B2 (your cursor will change to a black cross hair as you hover over it) and drag it across, it will fill in the rest of the months for you without you having to type them).

2. After December, type the headings Total and Monthly Average.

3. In A3 type the heading Income and list below all streams of income followed by a Total row.

4. Skip a row and type the heading Expenses in A10 and list all expense categories followed by a Total row.

I’ve just typed in a few basic categories for the sake of example, type in as many categories as are relevant to you. You may prefer to set out more specific expense categories, something like this:

5. Double click the line between each column to automatically adjust the columns to fit the content.

For the Monthly Average heading, click in the cell and select wrap text. This should automatically adjust the height of row 2, but if it doesn’t, click and drag the line between row 2 and 3 to adjust the height to fit the heading.

Select the cells January to Monthly Average (when selecting cells, your cursor should look like a white cross) and centre them vertically and horizontally.

6. In the Total rows and Total column use the auto sum button (∑) to insert a formula that will add up your data. Click first in B8, then click the auto sum button (top right corner of the home ribbon) and then click in B3 and drag to select (cursor should look like a white cross) the cells that you want to add together, then press enter.

As with the headings click the fill handle and drag the formula across to December. This copies the formula. You can check that the formula is correct by double clicking any of the formula cells and a highlighted rectangle will show you what is being calculated (pres ESC to get back out of the formula).

  Do the same with the the expenses Total row and the Total column.

Skip a row and add a cash flow total or Savings row. Type the heading Savings and then in B18 type in the formula =B8-B16 (click on the cells rather than typing in the references. Change the cell references to suit your spreadsheet set up – it’s just = Total Income minus Total Expenses) and press enter. Use the fill handle to copy this formula across.

8. To calculate the Monthly Average, click in O3 and use the drop down arrow on the auto sum button to select average. Select the cells from January to December (don’t include the total column), press enter and fill down, just as with the auto sum. Don’t worry about the #DIV/0! error for now.

  8. Format your budget. This step isn’t essential, but it is fun. Anyway it’s not a bad idea to do a little bit of formatting, because it will make your budget easier to read. If you have used Word, then the formatting is exactly the same, you just need to select your cell (click in it) before you format it. If it’s all totally foreign, have a play around with the formatting buttons (see below) to get an idea of what you can do. If you’re not sure what a button does, hovering over it will tell you.

 

• Start by formatting your heading. Select the rows A1 to O1 and click on the Merge and Centre button. Fancy up your heading as much as you like using the Font tools on the Home Ribbon.
• Use the Font tools in the Home Ribbon to format your month headings and income and expense headings. Make the totals rows stand out.
• Select all of the cells that will contain numbers and change the number format to currency.
• Add a border around your budget by selecting all the cells of your budget and choosing border – more (or Format: border for older versions) Click on outline and inside to add a border.

Save As now and you will have a template that can be reused every year. Save As again to create a 2010 budget.

9. Now it’s time to fill in your budget. Type in expected income and expenses for each month. If you’ve been tracking your expenses, then use the information to fill in your budget.  If there are months where the income or expenses are zero, then type 0 – this will ensure that the average calculates properly. You will notice that the formulas change automatically as you add your data (tip: use the fill handle to copy values across your budget).

I’ve typed in some data to show you what it will look like, but you wouldn’t just type the same thing for each month. If your rates are due in January for example, then your rates category would only have a value under January, the rest of the year would be 0.

If you have a whole lot of negatives in your Savings row or a big fat negative number at the bottom of your Total column, then you’re budgeting to spend more than you earn.

A better example of a budget set up:

In part two, I’ll set up a spreadsheet in which to type actual income and expenses and use a formula to automatically calculate the variance between what was budgeted and what was actually earned/spent.

---