Free Bookkeeping Template - How to Do Bookkeeping in Excel or Google Sheets
This article will walk you step-by-step through how to do your own bookkeeping using our free bookkeeping template.
We’ll first look at the pros and cons of doing your bookkeeping in a spreadsheet and also who this method works well for.
Click the links below to access our free bookkeeping template.
💾 Download the Free Bookkeeping Template 👈
If you’d rather watch a tutorial than read it, you can also check out this video tutorial on how to use our bookkeeping template.
Bookkeeping in a Spreadsheet
Whether you’re using Google Sheets or Excel, doing your bookkeeping in a spreadsheet is a proven method.
There are a few benefits and drawbacks when it comes to keeping your books in a spreadsheet.
Benefits of Bookkeeping in a Spreadsheet
The main benefits of doing your bookkeeping in Excel or Google Sheets are:
- Low Cost - Doing your bookkeeping in Google Sheets or Excel keeps the cost down compared to other options. Google Sheets works great and is a free program to use.
- Simple - Bookkeeping in a spreadsheet is simple data entry. As long as you have a reasonable template to use like ours, it’s not difficult at all.
- Save Time - Doing your bookkeeping in a spreadsheet can help you save time compared to learning a whole new software program. Copy and paste your bank transactions into the sheet and you’re 80% of the way done!
Drawbacks of Bookkeeping in a Spreadsheet
The main drawbacks of doing your bookkeeping in Excel or Google Sheets are:
- Errors - Because spreadsheets are mostly manual entry, it’s easier to make errors than when using bookkeeping software like Xero.
- Cash Basis Bookkeeping - It’s more difficult to do accrual basis bookkeeping in a spreadsheet. That’s not ideal, but is ok for many businesses as you may just need to record A/R and A/P at the year-end to get ready to file your taxes.
- Less Robust Reporting - Getting customized reports is a lot more difficult when using a spreadsheet compared to using bookkeeping software. This means you may only have annual financial data for your business and not monthly.
Who Should Use a Spreadsheet for Bookkeeping
Because spreadsheets are not purpose built for bookkeeping, they’re suited for smaller businesses or those that don’t need custom financial reporting.
If you’re just starting out with your business or if you’re a sole proprietor, our Google Sheets bookkeeping template works really well.
Spreadsheets typically work well for the following businesses:
- Sole Proprietors - Sole proprietors who just need to complete their bookkeeping once in a while for income tax filing or sales tax filing purposes.
- New Businesses - New businesses that don’t have a lot of transactions can make use of spreadsheets for bookkeeping.
- Side Gigs - If you’re just earning a bit of income on the side through a small business, a spreadsheet can work great to do your bookkeeping.
Who Should Not Use a Spreadsheet for Bookkeeping
Because spreadsheets are not inherently built for bookkeeping, they’re not the best bookkeeping tool for every business.
Larger businesses or those needing more robust financial reporting should look at other options.
Spreadsheet bookkeeping typically doesn’t work well for:
- Corporations - Businesses that are balance sheet heavy like corporations will find it difficult to get the info needed out of a spreadsheet. It will be tough to prepare a corporate tax return if the bookkeeping has been done in a spreadsheet.
- Need Custom Financial Reports - Businesses that want to create and view custom financial reports may want to use dedicated bookkeeping software. Viewing your business’ financial statements on a monthly basis can really help see how it’s performing.
- Many Transactions - If your business has a large volume of transactions, it is likely better to use dedicated bookkeeping software. This will keep things on track and help reduce the risk of error.
Bookkeeping Software
If you find that a spreadsheet isn’t the best option for bookkeeping for your business, there are a number of good bookkeeping applications available.
Xero Bookkeeping Software
Xero is our number one choice and the only bookkeeping software that we use at Avalon.
Xero is easy to learn, is very efficient due to automation and allows you to create custom financial reports.
The cost ranges from $17 per month to $54 per month CAD.
Simplify and Automate Your Bookkeeping
If you're looking to solve your bookkeeping problem, it might be a good time to simplify and automate your small business bookkeeping.
Our comprehensive online course gives you Avalon’s proven processes to take the pain out of bookkeeping using modern tools and processes.
In less time than it takes to watch the Lord of the Rings Trilogy,
You’ll learn:
- Our most valuable tips on how to to easily do your own bookkeeping
- How to keep bookkeeping simple so you avoid making mistakes and stay on track with your bookkeeping
- Insider tricks to automate as much of your bookkeeping as humanly possible
- Avalon’s tried and tested bookkeeping procedures to cut your bookkeeping time in half
- Simple steps to keep the tax man happy if you’re ever audited
- How to create and see through your financial statements like Neo in the Matrix so you can understand exactly how your business is performing
- Expert methods to ensure you don’t miss any tax deductions available to your business
And a whole lot more!
For a limited time we're offering our blog readers 25% off the regular price of this comprehensive course.
Just use the coupon code AVABLOG25 to receive 25% off on checkout.
Plus there is a 100% money back guarantee.
If you're not satisfied after completing the course, just reach out and we'll give a full refund with no questions asked.
Quickbooks Online
Quickbooks online is another popular option for bookkeeping software. It has similar capabilities as Xero and is widely used by businesses internationally.
We find it a little bit clunkier to use than Xero, but still an excellent option for those looking for capable bookkeeping software.
The cost ranges from $22 per month to $140 per month CAD. They typically offer a 50% discount for the first three months as well.
Free Bookkeeping Template - Excel and Google Sheets
We’ve created a free bookkeeping template in Google Sheets and Excel that you can use to quickly do your bookkeeping.
If bookkeeping in a spreadsheet is a good option for you, then check out our free template linked below.
Free Boookkeeping Template Link
We’ve set this template up mainly for sole proprietors to use. However, small or new corporations could certainly make use of it when transaction volume is low.
How to Use Our Free Bookkeeping Template
There are only a few steps involved in doing your bookkeeping using our Excel and Google Sheets template.
You can find the bookkeeping template in Google Sheets here.
Make a Copy or Download to Excel
Your first step is to make a copy of the Google Sheet or download it as an Excel file if you’d rather use Excel.
Important Note - If you try and edit the template directly, you’ll receive an error message saying you don’t have permission to edit it. Once you make a copy of the sheet or download it as an Excel file, you’ll be able to edit it however you like.
To make a copy of the template and use it as a Google Sheet
- Click the “File” menu
- Choose “Make a Copy”
You’ll then be prompted to save a copy of the Google Sheet to your Google Drive account. You will need to have a Google / Gmail login for this step to work.
If you don’t have a Google login or prefer to use Excel, you can download the template as an Excel file.
To Download the Template as an Excel File
If you want to use the template as an Excel file, you can do that too.
- Click the “File” menu
- Hover over the “Download” option and choose “Microsoft Excel (.xlsx)"
You’ll be prompted to download the file to your computer. You can then open it in Excel and start doing your bookkeeping.
Bookkeeping Tab - Enter Information and Transactions
Once you have a copy of the Google Sheet or the Excel file downloaded, you’ll see a few tabs at the bottom of the page.
Let’s start with an overview of the main bookkeeping tab on the sheet.
Section 1 - Instructions
You’ll find some quick instructions built into the template that explain how the template is used. They also contain a link to the video tutorial for the template.
These instructions are pasted directly below, but we’ll get into more detail further down the page here.
- Copy this sheet: File > Make a Copy (or Download as Excel).
- Fill in the blue shaded cells in the "Your Information" section below.
- Enter your revenue and expense transactions in the blue shaded cells under the "Bookkeeping" section below.
- You can enter each item on a separate line, or add up similar items and put the group total on one line.
- Select a transaction type from the dropdown menu under "Transaction Type".
- Include descriptions and specific dates for each transaction to help you remember what transactions relate to.
- Enter all transaction amounts as positive numbers.
- If you're not sure of which expenses are eligible to be deducted, check out our guide here .
- Once you've finished entering your transactions, your income statement for the year will be completed on the income statement tab (see green tab below).
- Also check out this instruction video linked here.
Section 2 - Example of Bookkeeping
Next you’ll see a grayed out section that shows an example of what your bookkeeping could look like once you’ve entered some transactions.
It doesn’t need to look like this, but this just shows a few different ways to use the bookkeeping template.
Section 3 - Your Information
In the “Your Information” section, you’ll want to enter your info into the blue cells and follow the instructions that appear.
Answering Yes to GST/HST and Home Office Example
If you choose "Yes" to charging GST/HST, the instructions will ask you to enter both the gross transaction amount and GST/HST paid.
If you choose "Yes" to working from a home office, the sheet will ask you to fill out the “Home Office Tab” to deduct your home office expenses that you paid during the year.
Answering No to GST/HST and Home Office Example
If you choose "No" to the GST/HST question the spreadsheet will tell you to enter the gross amount only for transactions (no GST/HST).
If you answer "No" to the home office question, the spreadsheet will tell you to skip the home office tab.
Section 4 - Bookkeeping Transactions
Section 4 is where you’ll be entering your bookkeeping transactions.
I find it easiest to record expenses and asset purchases first, and then sales afterwards. You don’t have to do it this way, but it’s how I’ll explain it here.
The easiest way to record your expenses and purchases is to first download your bank and credit card transactions from your online banking into a spreadsheet. This is often referred to as downloading transactions as a “.csv” file.
Then you can just copy and paste the expense transaction information from the downloaded file into the bookkeeping template.
Enter Your Expense transactions and Asset Purchases
Transactions should be recorded into the blue shaded cells within the “Bookkeeping Transactions” section.
- Column A “Transaction Description” - Paste the transaction descriptions into Column A.
- Column B “Date” - Paste or type in the date into column B.
- Column C “Gross Amount” - Paste the total transaction amount (gross amount) into column C. Always paste it as a positive amount unless it’s a refund of an expense (refunds would be entered as a negative).
- Column D “GST/HST” - If your business is registered for GST/HST, enter the GST/HST paid into Column D. This will help you record the appropriate expense amount and also help you claim the GST/HST paid when you file your GST/HST return.
- Column E “Net Amount" - This is a calculated cell. You won’t be able to enter anything into this cell. I don’t recommend trying to enter anything in case it breaks the formula somehow!
- Column F “Transaction Type” - Choose a transaction type from the drop-down menu in column F. If you’re not sure what to choose, make your best guess. It doesn’t matter all that much. Check out our bookkeeping guide for some more guidance if you’re not sure.
Once you’ve entered all of the expense transactions from your credit card and bank accounts, you can then enter your sales (aka revenue) transactions.
Enter Your Sales Transactions
This works in the same way as the expense transactions, but you’ll choose “Sales” from the drop-down menu in column F.
Download the sales transaction data from your online banking to enter into the spreadsheet. Or, if you have an invoicing system, you can just get a report for total annual sales and enter that into one line.
- Column A “Transaction Description” - Paste the transaction descriptions into Column A. You can enter each sales transaction separately, or a one-line summary of the whole year.
- Column B “Date” - Paste or type in the date into column B. If you’re entering a summary of the whole year, just enter the year here.
- Column C “Gross Amount” - Paste the total sales amount (gross amount) into column C. Always paste it as a positive amount unless it’s a refund (refunds would be entered as a negative).
- Column D “GST/HST” - If your business is registered for GST/HST, enter the GST/HST that you collected into Column D. This will help you record the appropriate sales amount and also help you prepare your GST/HST return.
- Column E “Net Amount - This is a calculated cell. You won’t be able to enter anything into this cell. I don’t recommend trying to enter anything in case it breaks the formula somehow!
- Column F “Transaction Type” - Choose the “Sales” transaction type from the dropdown menu in column F.
You’ve finished 95% of your bookkeeping once you’ve done this part. Next up we’ll look at the tabs for home office expenses, your income statement, and the GST/HST tab.
Section 5 - Other Tabs
At the bottom of the spreadsheet you’ll find some additional tabs.
- Bookkeeping - This is where you’ve entered your transactions.
- Home Office - If you are claiming home office expenses, you’ll enter information into this tab.
- Income Statement - The financial results from your bookkeeping will show up here. You can use this information to help you file your income tax return.
- GST - If you’re registered for GST/HST, you can use this to help you file your GST/HST return.
We’ll talk through these tabs in detail below.
Home Office Tab - Enter Home Office Information
If you run your business out of your home, you may be able to deduct a portion of your rent, utilities and other home expenses.
Your home office must be used exclusively for business or must be the principal place that you conduct your business. More info here.
The home office tab in the bookkeeping sheet will let you record your home office expenses.
- Home Office Info - Enter the total size of your home and the total size of your home office. This will then be used to calculate the portion of home expenses that are eligible to claim as home office expenses.
- Home Expenses - Enter the full amount of your total annual home expenses into the categories.
- Eligible Home Office Amount - The sheet will then calculate the eligible amount of these expenses and enter it into the income statement as an expense.
The expenses will show up on the income statement tab once you’ve entered them here.
The spreadsheet will prorate the expenses for you based on the numbers you enter for your home size and home office size.
In the example pictured above, the total eligible home office deduction will be $2,881.25.
Income Statement Tab - View Your Income Statement
Once you’ve completed entering your bookkeeping and home office transactions, you can view your income statement.
The spreadsheet will take your sales less your expenses and calculate your net income. You can use this to help you prepare your income tax return.
If you’ve entered home office expenses, they’ll show up here and be included in your net income calculation.
If you recorded any asset purchases, you will see details of those on this tab as well. Assets aren’t deducted the same way as expenses so they are broken out separately.
Check out this section of our bookkeeping guide to learn more about the difference between assets and expenses.
GST/HST Tab - File Your GST/HST Return
If your business is registered for GST/HST and you’ve recorded all of your transactions, you’ll see your GST/HST numbers in this tab.
This is most of what you’ll need to file your GST/HST return.
One common adjustment you may need to make is to reduce the GST/HST paid on meals and entertainment. Only 50% of the GST/HST paid on meals and entertainment is eligible to claim.
Make sure to check out our article on GST/HST or reach out to your accountant before filing your return if you’re not sure how it works.
Avalon’s Bookkeeping Services
Our bookkeeping template works great and doesn’t cost anything to use. If you do find that you want a professional looking after your bookkeeping, give us a shout.
We offer bookkeeping support and full service bookkeeping and we’re good at what we do!
Check out our bookkeeping services page for more info.
