Bookkeeping Using A Spreadsheet

If you want to do your bookkeeping using a spreadsheet, such as Excel, Google Sheets, or Numbers, we explain how to do that here. It might actually be easier to use free online accounting software such as Pandle, as explained in this post. But if you prefer a quick simple solution then bookkeeping using a spreadsheet might be best for you.

How Many Spreadsheets For Your Bookkeeping?

First, you need to work out how many spreadsheets you need for your bookkeeping. You’re going to need one for every bank account and credit card account that is owned by the business. You’ll also need one for the director’s/owner’s own expenses – so things they have paid for the business using personal funds. The reason for having a separate spreadsheet for each account, is so that you can check it agrees with bank statements.

Every Bookkeeping Spreadsheet Needs This

For every spreadsheet, the total income, minus the total payments, should equal the change in the bank balance. If it doesn’t there are errors, such as missing lines, extra lines, or incorrect amounts. [Tip: to help find errors, add a column for ‘Balance’ to enter the starting bank balance, plus the income or minus the payment in each line, then copy down to give you a running bank balance. Then compare this with the bank statement balance to find where the difference starts].

One Bookkeeping Spreadsheet For All Accounts?

You could try to put everything on one spreadsheet but you will need to add a column for the ‘account’ so you can filter the transactions for that account to check it agrees with bank statements. It could get complicated and messy but it means everything is in one place.

How To Start Bookkeeping Using A Spreadsheet

You need to start with the basic transaction data which can be taken/downloaded from bank statements. So, date, payee, description, and amount. Each of these will be a column on your spreadsheet with each transaction taking up a row. You then need to decide on the layout of each spreadsheet. Every transaction needs to be categorised (e.g. sales, travel costs, telephone, VAT), and there are a couple of ways to do that.

Method 1

The first and simplest method of bookkeeping using a spreadsheet is to add a column for every category you need, then copy the transaction amount and paste it into the most appropriate column. Then you can add formulas to sum the total of each column. You will need to check that the total of all the columns agree with the total income minus total payments. If it doesn’t, either a transaction amount hasn’t been copied across, or has been copied across into more than one column, or the amounts in the category columns don’t add up to the transaction amount.

Advantages of Method 1

One advantage of this method is that you can easily split one transaction between categories, e.g. VAT and software, or travel and meals, or sales and bank fees. Another advantage is that you can see from the totals how much is in each category.

Method 2

The second method of bookkeeping using a spreadsheet is to label each transaction with the category. You can do this by adding a column for category, then typing or copying the name of the appropriate category in each line under the category column. You need to use consistent category names, so not all of Tel, Phone, Telephone, Mobile! [Tip: when you are typing a category, the spreadsheet will try to guess which word you are typing based on the words above it – use that suggestion if correct].

Advantages of Method 2

The advantages of this method are:

There are only one or two columns of numbers so you are less likely to make a mistake.
It’s tidier because all of the data is contained in just a few columns.
You can easily see which rows have not been categorised.
It’s easier to add one or two columns onto a downloaded bank statement, than many columns for all of the categories every year.
You can use filters, sorting, and pivot tables to see how much is in each category. Take care not to mix up the data.

VAT Registered?

With both methods, if you are VAT registered, you’ll need to add a column for VAT to enter the VAT amount if applicable [Tip: divide the gross amount by 6 for 20% VAT], and a column for the net amount (excluding VAT). It’s the net amount that needs to be categorised.

Summary

Hopefully you now have a clearer understanding of how to do your bookkeeping using a spreadsheet. After reading this you may prefer to try doing your bookkeeping using online accounting software. It can be free, it can be easy to use, and it can save you a lot of time. Either way, we can help you with your accounts, tax returns, and VAT returns etc, all for a low fixed monthly fee. You can see our prices and get an instant quote following the buttons below.