Gathering Info For Your Spreadsheet
- Open up a blank Excel Spreadsheet. If you have never used Excel before, when you open it for the first time, you see a grid labelled with letters along the top and numbers down the left-hand side. If you select any of the rectangular squares on this grid, you'll be able to identify them by cell name. So if you select a cell that comes under the column 'C' and is on row 5. Then this is cell is labelled as C5.
- On the top, type in your estimated monthly budget. Type in "Monthly Budget" in cell A1. Then type in your actual budget as the numerical figure in cell B2. See figure 1 below.
- Then what you need to do is to identify your monthly expenditure. For this example, we will use the following:
- Rent - £550
- Electricity - £50
- Bus pass - £15
- Food shopping - £300
- Mobile phone insurance - £8
- Gym membership - £35
On knowing what your monthly expenditure is, you can then type them up on your spreadsheet and present them as shown in figure 2 below. You'll notice, cells A4 to A9 contain the details of the expenditure. And cells B4 to B9 contain the expenditure amount.[caption id="attachment_817" align="aligncenter" width="357"] figure 2[/caption]
Adding Up Figures
- Right, now that you have identified your monthly budget and your monthly expenditure, it is time to apply some very easy formulas to help you manage your budget. The first thing that we have to do is add in “Total Monthly Expenditure” into cell A11 as seen in figure 3. [caption id="attachment_816" align="aligncenter" width="374"] figure 3[/caption]
- Then, we would need to total up the monthly expenditures that we have typed into the spreadsheet. There are two formulas that we can use. The first formula is “=B4+B5+B6+B7+B8+B9” (see figure 4). The second formula, which is much quicker to input, is “=sum(B4:B9)”. Type either formula into cell B11. [caption id="attachment_815" align="aligncenter" width="387"] figure 4[/caption]
- Once you have entered either formula, simply press enter on your keyboard. On doing so, you will notice the total monthly expenditure appearing in cell B11 (see figure 5). [caption id="attachment_814" align="aligncenter" width="405"] figure 5[/caption]
Changing The Numerical Values To Currency Values
- You'll notice that the monthly budget and costs are appearing as normal numbers. We can change this to currency values to allow the figures to be correctly represented. To do this, select the column labelled as “B” by clicking on the top of the column as shown in figure 6. [caption id="attachment_813" align="aligncenter" width="388"] figure 6[/caption]
- Then, at the top of the spreadsheet, scroll your mouse to where you will see a drop down box that contains the word “General”. Click on this on drop down box and then select “Currency” (see figure 7). This enables all numerical values in column "B" to appear as currency values. [caption id="attachment_812" align="aligncenter" width="366"] figure 7[/caption]
Calculating The Difference
- The next thing we need to do is calculating the difference between what we've spent and our monthly budget. To do this, in cell A13 type in “Difference” as shown in figure 8. [caption id="attachment_811" align="aligncenter" width="409"] figure 8[/caption]
- Then in B13, input the formula that will help you calculate the difference which is “=B1-B13” (see figure 9). Once you've entered the formula, then all you have to do is press enter on your keyboard and the difference will be calcuated as shown in figure 10.