Using Spreadsheets for Your Accounts

Using spreadsheets to do your accounts used to be the standard way of doing things until the advent of database type specialised accounts programs, such as Sage, Xero, Quickbooks, FreeAgent, etc.

Whether you intend to use spreadsheets or an account program, a spreadsheet is an easy way to get your business transactions to us to do your accounts. If you want to use our accounts software to input and categorise your transactions, that’s also easy. But it’s much easier for me to demonstrate the accounts software when there is data in the program that is recognisable. So I will usually ask you to do your first few weeks or months in a spreadsheet. When you email me the spreadsheet, I will import it into your accounts software and demonstrate it to you to get you started.

Or, if you prefer, you can stick to using spreadsheets for your accounts. Lots of people who are used to Microsoft Excel prefer to use spreadsheets rather than database type accounts programs. And the good news is that there is what is known as ‘Bridging Software’ that will allow you to continue using spreadsheets when Making Tax Digital for Income Tax Self Assessment (MTD ITSA) comes into effect in April 2024.

Here are two examples of Bridging Software, both approved by HMRC for Making Tax Digital:-

For Income Tax Self Assessment:

For VAT:

Here is a guide to the Columns in our Spreadsheet Stencil:

  • Date: The date of the transaction in the format dd/mm/yyyy or dd/mm/yy
  • Type: This is the transaction ‘Type’ code as will be on your bank statements. It’s just to keep a record of how the transaction was paid. If you don’t know any codes, just make up your own. e.g. D/D (Direct Debit), S/O (Standing Order), CSH (Cash), CHG (Charges), DBC (Debit Card), CDC (Credit Card), XFR (Bank Transfer), BAC (Bank Automated Credit), etc. Whatever you put, I’ll be able to work it out …
  • Payee or Payer: This is usually shown as ‘Description’ on your bank statement, but it’s not usually a description of what it is, (although it might also have “ESSO Fuel”); it’s a description of who paid you or who you paid.
  • In: The amount that you received in.
  • Out: The amount that you paid out.
  • Bank Account: If all your transactions are via the same bank account just put the bank account in the top row, no need to fill in all the rows. If all your transactions are via various accounts (including cash), fill in the column to indicate what transactions relate to which bank account.
  • Category: Use one or two words to describe what the transaction is for. e.g. ‘Sales’, ‘Goods’ (for resale), ‘Vehicle Fuel’, ‘Insurance’, ‘Mobile Phones’, ‘Internet Connection’, etc.
  • Group: If you wish you can use the ‘Group’ column to indicate different groups that relate to specific transactions. e.g. If you have income from property, you may want to differentiate what transactions relate to specific properties; or if you do different types of work like roofing, joinery, electrical, etc., you might want to keep a note of what transactions relate to specific job types.

Here is an example of a spreadsheet with boxes filled in:-

Download Spreadsheets

To be as compatible with as many clients as possible, these stencil files are available in MS Excel format (.xlsx) and Open Office format (.ods). Click on the icons to download …

Microsoft Excel Spreadsheet Stencil

Microsoft Excel
Spreadsheet Example

Open Office
Spreadsheet Stencil

Open Office
Spreadsheet Example