Using Excel to keep your financial records : Parks Community UK

This is a guide on how to set up your accounts using Microsoft Excel. This guide has been written as clearly as possible to be understood by all, it may well explain things you already know, apologies if this is the case.

Open an Excel document by clicking on icon for Microsoft Office Excel
Save blank document as Accounts 201.. (or whatever you wish to call it). Ideally it should include the year so you can find it easily.
Change orientation and margins. It will automatically open as a portrait document with 2.5 cm margins. Go to Page Layout. Click Orientation and change from Portrait to Landscape. Then Click margins and click narrow. Go back to Home page.
At the bottom of the screen there is a bar with tables, titled Sheet 1, Sheet 2, Sheet 3. Double click on Sheet 1. When it is highlighted type Income. Sheet 1 will change to Income. Then double click Sheet 2 and type Expenditure, then Sheet 3 and type Expenses. Note: You don’t have to use those titles you can call it whatever you want.
Click on the tab for whichever sheet you want to begin with, either Income, Expenditure or Expenses.

Go up to the blank row beneath the row with the letters A, B, C, etc. Type in your column headings across this row.

Suggestions for Expenditure are: Date, Payee, Cheque No, Invoice No, Description, Amount. These are the most important headings. You can then use more columns to break down how much you have spent under each heading. Suggestions are: Room hire, catering, Expenses and any others you wish to list. You enter the amount of the payment into the Amount column and the relevant breakdown column. Write on invoice the date paid and the cheque number.

Suggestions for Income are: Date, Source (who the cheque came from), Description and Amount. You can then use breakdown columns such as Grants, Donations, Event 1, Event 2 (you would use a better description of the event than that).

Suggestions for Expenses: Date, Paid to, Ref No and Amount. Put the ref number on the Expenses Claim form and the date it was paid.
Go back to Income page or Expenditure tab, whichever one you want to start with.
In Page Layout, tick View and Print in Gridlines. This will show the lines faintly to make it easier to work on.
You can widen the columns for Payee by holding cursor over the line between B and C (or whatever the letter is above your Payee column – you need to be on the line between that letter and the letter above the column on the right of Payee). It will show a cross with arrowheads to the left and right on the horizontal line of the cross. Drag to right to widen the column. Repeat on any other columns as required – probably only Description will need widening. This is also how you make columns narrower – you drag to the left to make them narrower.
If your heading doesn’t fit. On Home page go onto down arrow at right of Format Cells and click on Format Cells at the bottom of the list that comes up. Click on the Alignment Tab on the drop list that comes up. Click Wrap Cell and it will put the heading on 2 lines or more.
Whilst on that Format tab, click on Currency on top bar and set number of decimal places to 2, using up and down arrows to change to 2.
On the same page, against Symbol, there may be a £ sign. Decide if you want a £ sign on every number box. If you don’t, click the down arrow at the right of the box and choose None. Note: If you type in a £ sign it will appear, ie for the total box.
To get headings to repeat on every page. In Page Layout, click Print Titles. In ‘rows to repeat’ at top, type A1 to K1. Note: for M1 put your last column letter plus two letters, ie if your last column was K, put M1. This will give you two spare columns which you may need. Note: It may appear as $1:$M – don’t worry.
On the same page, tick Print Gridlines.
You are now ready to start entering invoice details.
When you have entered all the invoices, leave about 10 lines blank after your last entry and type TOTAL in the Payee column. Highlight the figures in your Amount column from Row 2 (ie below headings) to the row where you have typed TOTAL. When it is highlighted, click Σ on the Home page and it will fill the total of the column in. You will probably find this symbol in the last block on the Home page.

Once totalled, click on the amount and highlight it. Press copy symbol (below scissors on top bar), then highlight the TOTAL line across your breakdown columns and press Paste. This will fill the total cell of each column on that line.
You can check these figures are correct, by highlighting the totals in the breakdown columns. On the bottom line of the screen you will see the word Sum. The figure alongside is the total of the breakdown column totals and should agree with your main total column.
If it doesn’t agree, check you have carried all the figures in the amount column into the breakdown columns. Correct any not carried over or carried over wrongly. Then highlight the total of the breakdown columns again and see if it agrees.
You can use this check to check on any lines you highlight, either vertically or horizontally.
Once you have set up the Expenditure page, click on the Income tab at the bottom of the screen and set up your Income page in exactly the same way and then do your Expenses page if you have one. Note: You don’t need to breakdown the Expenses page, so this page can stay in Portrait style if you wish.
Once you have figures on your Income and Expenditure pages you can link them together so you will always have a running balance available. To do this:

Under where you have typed TOTAL on your Income page, type LESS EXPENDITURE. Underneath the figures in the amount type =Expenditure!F16 Note: ‘Expenditure must be typed exactly as it is written on the tab – same word, all capitals or all lower case, etc. No spaces between any of the formula (doesn’t need to be in red though) and the exclamation mark and = sign are important. I have used F16 in my example. The letter should be the letter above the column on your expenditure sheet where the amounts are and the number should be the row number (from the left of the screen) where your total line is on the expenditure sheet. If any of this is entered wrongly, it won’t work.
When you need to add lines on any of the pages, add them above the total line, ideally two blank lines above. On the Home page click on the numbers on the extreme left of your page on the blank line where you want the lines added. This number will highlight a blank line and the number, then click Insert a few times. It will add a row for each click above the line that is highlighted. If the number is not highlighted as well as the row, it will just add a single cell rather than a row.

You do not need to do anything to the total row. The computer will automatically change the formula that is in place.
When you want to print a page. Go down to the line below the last column in use and highlight back up to the top left hand corner (A1). Then give the command to print.

It may automatically print as landscape, if not change your printer to landscape by pressing print and going into Properties on the print page and clicking on Landscape. Note: This will not change the printer forever, it will just change it for this document.
You may find when you print off a page that it goes across two pages. You can make some of your columns narrower to get it all on one page. How to do this is explained above. If you change a money column and make it too narrow for amounts, the numbers will disappear and it will just show symbols. Widen the column slightly and the figures will reappear. You can narrow the description column by using the wrap round command and the description will go onto two or three lines as required.
If you click on Entire Workbook on your print page it will print all your pages, Income, Expenditure and Expenses.
If you decide you need another working sheet, for an event or something, go down to the right of the tabs on the bottom of the screen – where it says Expenses or Sheet 3. Click on the icon to the right of Sheet 3 and it will insert a blank sheet. Give this tab a title as you did before and set up the page as you did for the Income and Expenditure sheets.
You can add columns in by going to Home page. Go to Alphabetic line and highlight letter where you want the column added. The column will be added before the letter, so if you want a column added between B and C, highlight C. Once highlighted, go to insert on bar above your sheet and click on arrow on the right, this opens a drop box. Click on insert sheet column and it will drop it in place. Note: You may find that this doesn’t total as your other columns do. To fix this, highlight column from Row 2 (ie under headings) down to the total time and press the Σ symbol on the Home Page and it will total the column. This will then automatically be added in with other totals.
If you make any payments by cash, make sure you get a receipt signed. If there is no invoice, type a receipt of your own and get the person being paid to sign it that it was paid cash. Write on receipt that it was paid cash. Enter the payment up as usual but don’t forget to enter the cash amount on your income sheet as well.
When you get your statements, you can mark off the cheques and income as they show on the statement by changing the colour of the amount. That way you will always know which cheques are still to clear and which income amounts have gone in.
Don’t forget to add any direct debits, standing orders, bank charges, etc., to your expenditure sheets and check that any payments directly into your account by BACS are also included.