This is the second in a series of 3 articles on using office applications – next time we will use both Microsoft Word and Excel to create mail merged letters. In the meantime, here’s the second installment on Microsoft Excel.
Microsoft Excel – My Money Tracker
In this article I will show you how you can use Excel (a spreadsheet program) to do some basic maths for you in the form of a money tracking spreadsheet. The finished article will allow you to enter the balances of your various bank account (Spanish and English) as well as cash in hand. It will also convert to Euros for you. You then enter your outgoings and it will tell you the balance available to you.
Your first job, to save a bit of time is to head on over to http://shop.all-tech-plus.com in the Software section and order and download the free spreadsheets – you’ll get an unfinished one as well as the completed article. I won’t be able to cover much here, but if you do want to learn more, remember you can always book lessons with me on my main website http://www.all-tech-plus.com and I am also available to develop spreadsheet solutions for your business.
The starting point spreadsheet has most of the layout and formatting done for you, though if you don’t like the colours or fonts you can change them – click in one of the boxes (these are known as cells), or click and drag to select more than one and use the standard formatting features to change (but you might want to just hang fire for a moment as the colours I have used are going to be useful).
Dark green – you type in what you want here. Light green – you’re going to be putting in numbers in these, and light blue, you’re going to be putting in formulas. You can think of a formula as a little program or instruction that you want Excel to workout for you.
Down the side of the sheet you will see numbers, and along the top letters – so for example, the current euro rate is in cell F1 (this is the cell reference) – lookup on Google ‘how many euros in 1 pound’ to get the rate and change it on the sheet.
Take a look at the completed sheet now, and fill in the dark green bits in your starting sheet to show your own bank accounts, forthcoming expenses, and spending tracking. Whilst you’re at it, enter the amounts in the light green boxes – once this is done, we’re ready to get Excel to do some working out for us using formulas.
To workout the converted to euros totals in the first table, we will need to multiply any sterling amounts by the euro rate. Double click in cell E5 (you should now be able to type in this box). Type in =(C5*F1)+D5 and press the enter key [the equal sign tells Excel this a formula, we put brackets around the first thing we want it to work out – the sterling balance of our first account multiplied by the euro rate, and then we add the euro amount – which may or may not have something in it]. Did it work? Check with a calculator or old fashioned pen and paper if you wish! Change the Sterling amount – did the conversion update automatically – hope so!
Now you could do the same in cells E6, E7, E8 and E9, however, click back in E5 (just one click this time) and notice the little square blob in the bottom right of the cell (this is the fill handle), click and hold on this, it’s a little tricky, now drag down as far as cell E9 and then let go – if all has worked well you should now have euro totals in all those cells.
C11, D11, E11 are for totals, the formula you need for C11 is =SUM(C5:C9) – just follow the process as above, but instead of drag-filling down, drag-fill to the right as far as E11.
The formula for E12 will be =SUM(K:K) to total all the figures that end up in column K (your paid expenditure).
Finally, E13 needs to subtract the total in E12 from E11 to tell you what’s left ( =E12-E11 )!
You should now have enough information to get the rest of the sheet working. In this ever so brief article you’ve learnt what a cell is, how to reference a cell, what is meant by a formula, using simple formulas for totaling (SUM), subtracting, and multiplying.
As a bonus, I’ve also include a learning spreadsheet I used to use with my Year 7 students – but don’t let that put you off!
Mark from All-Tech-Plus (http://www.all-tech-plus.com) is available for private lessons or corporate training. Lessons can be booked through the website. For business consultations please contact firstname.lastname@example.org , Mark would be delighted to develop training packages or help you streamline your business processes.
You may find additional information on Mark’s website including previous articles – please do visit and signup for his newsletter.