This is part three of a three-part series on bookkeeping with spreadsheets. The first post covered the benefits and limitations of bookkeeping with spreadsheets. The second post outlined the steps to create a bookkeeping spreadsheet template. This post details how to use the bookkeeping spreadsheet template.
Basic Use
Here are the basic steps to use the template:
- Copy and rename the file. Avoid saving over the template.
- Optional: Copy and rename sheets on the spreadsheet, if using multiple sheets.
- Enter each transaction (from your bank statement or a cash receipt) on a separate row. You can split one transaction across multiple columns, e.g. at the supermarket, you purchase envelopes (printing & stationery) and a USB drive (computer expenses) in one transaction, so this transaction would be on the same row with the envelope cost in one column and the USB drive cost in another.
- Add more rows above the blank line that is above the total line, otherwise the total formula might not include your new row. Check that the appropriate total cell changes when you enter a number in your new row.
Send to your Accountant
When your accountant needs your transaction records, send the whole file, as they may want to check your formulae (see point 4 above).
Adjustments for Printing
Before printing, check the Print Preview. As you add more rows, it may not be possible to fit everything on one page, so you will need to make some adjustments. Here are some example adjustments:
- Adjust the print margins. This will allow more information on each page.
- Adjust the column widths. To make thinner columns, you may need to choose shorter column heading names.
- Using Page Break Preview in Microsoft Excel, you can adjust the location of the vertical and horizontal page breaks. You can also adjust this by adding blank rows and/or columns, but this can be time-consuming.
- Using Page Setup in some versions of spreadsheet software, you can automatically add the column headings to each page
Changing the Template
If you find that you are making the same adjustments each time you use the template, then make those same adjustments to the template and save the file.
After adding rows or columns, make sure that the relevant total formulas include these new rows/columns: type a dummy number in the new row/column and watch the relevant total cell(s) change.
Sorting and Filtering
Looking for a particular type of transaction? Want to sort the results? Use the Filter to add extra functionality to the column headings:
- Highlight every row above the total line, starting with the row of column headings. Don’t include the total rows, to avoid the sort moving them.
- Click the Filter button, which looks like a funnel. In recent versions of Microsoft Excel, this is located under the Data tab. In Google Sheets, it is located in the main toolbar.
- You should now see a little arrow button on each of your column headings. Click the arrow button on a heading and look at the options.
- Sorting alphabetically (ascending or descending) will re-arrange the rows so that the column you clicked is sorted alphabetically.
- Below the sort options, you will see the filtering options. This allows you to hide/show rows based on the value it has in that particular column. You can filter in a number of columns simultaneously to create a very specific and short list of rows.
Sorting and filtering should not change the figures in the total rows/column. The formula in the rows will not change when hiding rows using filtering, because the rows still exist.
Experimenting
Experimentation is the best way to find the tricks that work for you. Copy the template file and then play with the different features of your spreadsheet software or try different layouts.
I hope this series on bookkeeping with spreadsheets has been helpful. However, eventually it will be more efficient to start using accounting software, so my next post answers the question “When to start using accounting software?“