Excel tips for do it yourself bookkeepers

For whom are these tips?

Many of my clients organize their information in Excel spreadsheets.  Rather than using off the shelf accounting software, they package their info in an Excel spreadsheet.

Why Excel?

These tips work just as well in other similar electronic spreadsheet programs such as Quattro Pro and Open Office ( a free program). But, my clients, and probably most small business owners, prefer Excel, so I refer to Excel. Also, you’ll find more easily accessible  help with Excel than with any other spreadsheet. There are  tons of resources on the Web and in bookstores to help you with Excel.

Download bank and credit card info

 Most banks will let you download your bank account and credit card info into Excel. This will save you hours of time in writing out the information. After you download the info, you should add in important details, e.g., the payee for each cheque, details of deposits etc. The basic information for each line should contain the date, the amount, the payee or payor  name, and the bank balance.

 Or, just copy from the bank statements and credit card statements

If you prefer not to download, or if it somehow doesn’t look right, then here’s what you do:

·         Get all the bank statements for your fiscal year

·         Put them in month order

·         Enter the info just as you see them from the bank statements—using the same columns for: date, credits, deposits

·         Create also a ‘balance column’. It’s better to create a simple formula for this column, in that each figure = row above it+deposit-cheque. This is in order to ensure your  spreadsheet bank balance always  equals  the actual bank balance

Breakdown the expenses among the more frequently occurring categories

 ·         Label columns to the right of the balance column with common expense names. Use the expenses that more frequently occur. If you have financial statements, you should use the items on the statement of income (otherwise called ‘profit and loss’)

·         You shouldn’t have more than more than a dozen columns. If you have more than a dozen expense categories, just label the  last two columns to the right  1)other and 2)description

·         Copy from the cheques/debits colum into the column on the right that seems the best fit.  (Don’t copy the other way around—from the right to the left. It could mess up your bank balance)

 Separate worksheets for each bank account and credit card account

If you have 2 bank accounts  and 3 credit card accounts, you should have 5 worksheets each one containing the info  for one bank or credit card account.Just click on >insert> worksheet to generate a new worksheet.

One workbook per year

For each fiscal year, you should create a new workbook.

Common ways to use Excel in your business

Apart from writing up of transactions,  you can use Excel in your business  for:

Free templates available

(Update: Sorry, I've been informed by my professional liability insurance provider that providing these templates is considered software distribution which would raise my insurance rates and expose me to the risk of additional liability.  So, I can only offer these templates to my clients on a very discretionary basis.)

[ Home ] [ Up ]

Copyright © 2006 B.C. Chastkofsky C.A.

Last modified: October 31, 2006