How to make a basic bookkeeping spreadsheet

Bookkeeping templates are easy guides to follow when setting up an accounting package. It may sound complicated the first time, but there is a way to do simple bookkeeping and it’s all by setting up an accounting spreadsheet or a spreadsheet template. Follow the steps below to set up your very own purchase daybook:

1) Open up Excel
2) Left click on the second worksheet tab on the bottom left of the screen. A new Excel worksheet will open
3) Click on rename and type ‘Purchase Daybook’
4) Click back into the worksheet
5) Type the following in the cells indicated:

Cell   Number   Type
C           1          Purchase daybook
A           3          Month
B           3          Day of month
C           3          Name of supplier
D           3          Invoice number
E           3          Fo.
F           3          Total net
G           3          VAT
H           3          Gross total
I             3          Stationary

6) In the cells to the right of 13, type appropriate headings for the analysis of the headings, e.g. stationary, office equipment etc. in all the cash cells in row 4, type and centre ‘£ : p’
7) Click into cell A4. Hold down the shift key and click on B4. This will highlight both cells.
8) Click on the merge and centre arrow at the right of the icon in the alignment group on the ribbon bar (the square with the little ‘a’ in it).
9) Type the current year into that space (e.g. 2016).
10) Click on the format option in the format group at the right hand side of the ribbon. Select the column width option and then alter it to a width of just five characters. Leave enough line to allow for roughly the amount of entries you expect to make in the current month, and then place the cursor in the Net invoice total column, hold down the shift key and press the right direction arrow twice to highlight the three adjacent cells in the columns for net invoice value, VAT and gross total.
11) Click on the arrow at the right of the answer box icon, which is situated a quarter of the way across the ribbon bar. This will reveal a menu of answer box styles. Select the one that has a single top line and a double bottom line and press the return key.
12) Move the cursor one way to the right. Hold down the shift key and click the right direction arrow as many times as necessary to highlight the line of cells in all of the remaining columns.
13) Click on the answer box icon again and this time select the one that has a single top line only and no bottom line. Press return.
14) Click the cursor into the answer box in column F.
15) Click on the sigma icon and highlight all the cells above the answer box at the far right for all columns used.
16) Click on the sigma icon, which is found near the right of the ribbon bar. This will place a summation formula into each of the answer boxes.
17) Click the cursor into cell A1. Hold down the shift key and scroll to the cell that contains the last heading you types on line 4.
18) Click on the format cells button.
19) Click on the format cells option at the bottom of the drop-down menu.
20) Return to the worksheet and un-highlight cells.
21) Click on the review tab at the top of your screen.
22) Select the protect worksheet option. You will be asked for a password, which you must create yourself. Click return and it will ask you to repeat your password. Enter it again and click return. Your headings will now be protected against accidental erasure or modification.
23) You are now ready to enter data.