Introduction This article is not about Bookkeeping but is about how beginners can use features in Excel to easily create Books and Accounts for households and small business.
Microsoft Excel is often already on a computer and can readily be used to keep track of household finances or to complete books and accounts for startup and small businesses.
Although other specialist software is available it may be an unnecessary expense and is usually too complex for very small businesses and householders Bank Statements The basics that you need to keep track of are payments and receipts and reconcile these with your bank statement.
Depending on the number of transactions records can be written up weekly, monthly or quarterly.
The first step is to download your bank statement to Excel.
Most online bank statements have a function to download the statement to a spreadsheet or CSV format.
Open up a new Excel file and copy and paste this into the file.
Very often all of the information will not show up.
Go to the heading of each row and when arrows appear on the borders expand the width of each row by dragging with the mouse to expand the row until all the information shows up.
The bank statement may show the first transaction and then the balance after that transaction.
To show the opening balance obtain the balance for the previous period and insert this in a new row above the existing first row.
In Excel 2003 go to insert and then insert sheet rows.
In Excel 2007 on the Home Tab go to insert and then insert sheet rows.
Formatting One point to check and be careful of is that in the default general numbering format pence will not show up if they are zero.
To show everything in more of an accounting format select all rows, go to format and then format cells.
In the pop up window click on the tab for number and then under Category click number.
Against decimal places show 2 and click on the box to show 1000 separator.
Another way is under Category click accounting and select none for currency symbol.
A quick shortcut is to select all rows and then CTRL+SHFT+! In Excel 2003 there is a shortcut in the Toolbar and in Excel 2007 on the Home Tab and the dropdown in the Numbers Section.
Note that Excel is not very text-oriented.
To do a text heading across columns insert extra sheet rows above.
For example to do a 3 line Heading with a line space between each Select six existing rows and then Insert and Sheet Rows to insert 6 rows above.
For each row where text is to be inserted select the row.
Then in Excel 2003 on the formatting toolbar select merge and centre.
In Excel 2007 on the Home Tab in the Alignment section select Merge and centre.
To easily create underlines of cells or adjoining cells for totals in the Accounts select the cells.
Go to format and format cells or right click and format cells.
Select Borders from the top tabs.
In the formatting box select the graphic for top or bottom border as appropriate.
In Excel 2007 in the Home Tab, Font Section use the drop down next to the little borders box to choose the relevant border Easy Formulas To quickly add up a column or row of figures select all cells including a blank cell at the end in which the total is to appear.
Click the Auto Sum symbol and the total will appear in the last cell with a formula automatically inserted.
There is a very useful way to quickly create addition and subtraction formulas.
Double click the cell you wish the total to appear in and type = which starts a formula for that cell.
Click the first cell for the addition and its column letter and row number will appear in the formula shown in the totals cell and the formula bar at the top.
Then type + and click on the next cell and press enter to finish the formula.
Usually for accounts plus and minus are all that you need.
More than 2 cells can be included in the formula.
For Profit and Loss Accounts you can readily create a series of formulas.
If you need to make a correction Excel automatically updates all subsequent figures.
A handy tool in Excel is that it will show some calculations instantly without a formula.
Select a series of cells or non adjacent cells using control.
In Excel 2007 the average and the sum of the figures in the cells is shown automatically in the taskbar.
The same result can be obtained in Excel 2003 by right clicking on the taskbar and selecting the calculation required.