香港金光佛论坛

Bookkeeping

Free Bookkeeping Template - Bookkeeping in Excel or Google Sheets

Paul Sharpe, CPA, CA
/
August 19, 2022

Affiliate disclosure

This article will walk you step-by-step through how to do your bookkeeping using our free Google Sheets bookkeeping template. It also works as a free Excel bookkeeping template.

Awesome! Your checklist is on its way to your inbox.
Oops! Something went wrong while submitting the form.

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鈥檒l 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鈥檇 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鈥檙e 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鈥檚 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鈥檙e 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鈥檚 easier to make errors than when using bookkeeping software like 香港金光佛论坛.
  • Cash Basis Bookkeeping - It鈥檚 more difficult to do accrual basis bookkeeping in a spreadsheet. That鈥檚 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鈥檙e suited for smaller businesses or those that don鈥檛 need custom financial reporting.

If you鈥檙e just starting out with your business or if you鈥檙e 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鈥檛 have a lot of transactions can make use of spreadsheets for bookkeeping.听
  • Side Gigs - If you鈥檙e 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鈥檙e 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鈥檛 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鈥檚 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鈥檛 the best option for bookkeeping for your business, there are a number of good bookkeeping applications available.

香港金光佛论坛 Bookkeeping Software

is our number one choice and the only bookkeeping software that we use at 香港金光佛论坛.听

香港金光佛论坛 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.

香港金光佛论坛 Pricing

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 香港金光佛论坛鈥檚 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鈥檒l 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
  • 香港金光佛论坛鈥檚 tried and tested bookkeeping procedures to cut your bookkeeping time in half
  • Simple steps to keep the tax man happy if you鈥檙e 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鈥檛 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.

Click here to learn more

Quickbooks Online

Quickbooks online is another popular option for bookkeeping software. It has similar capabilities as 香港金光佛论坛 and is widely used by businesses internationally.

We find it a little bit clunkier to use than 香港金光佛论坛, 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.

Quickbooks Pricing

Free Bookkeeping Template - Excel and Google Sheets

We鈥檝e 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鈥檝e 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鈥檇 rather use Excel.

Important Note - If you try and edit the template directly, you鈥檒l receive an error message saying you don鈥檛 have permission to edit it.听 Once you make a copy of the sheet or download it as an Excel file, you鈥檒l be able to edit it however you like.

To make a copy of the template and use it as a Google Sheet

  1. Click the 鈥淔ile鈥 menu
  2. Choose 鈥淢ake a Copy鈥
Bookkeeping Template Copy

You鈥檒l 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鈥檛 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.

  1. Click the 鈥淔ile鈥 menu
  2. Hover over the 鈥淒ownload鈥 option and choose 鈥淢icrosoft Excel (.xlsx)"
Bookkeeping Template Download

You鈥檒l 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鈥檒l see a few tabs at the bottom of the page.

Let鈥檚 start with an overview of the main bookkeeping tab on the sheet.

Bookkeeping Template Google Sheet

Section 1 - Instructions

You鈥檒l 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鈥檒l get into more detail further down the page here.

  1. Copy this sheet: File > Make a Copy (or Download as Excel).
  2. Fill in the blue shaded cells in the "Your Information" section below.
  3. Enter your revenue and expense transactions in the blue shaded cells under the "Bookkeeping" section below.
  4. You can enter each item on a separate line, or add up similar items and put the group total on one line.
  5. Select a transaction type from the dropdown menu under "Transaction Type".
  6. Include descriptions and specific dates for each transaction to help you remember what transactions relate to.
  7. Enter all transaction amounts as positive numbers.
  8. If you're not sure of which expenses are eligible to be deducted, check out our guide here听.
  9. 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).
  10. Also check out this instruction video linked

Section 2 - Example of Bookkeeping

Next you鈥檒l see a grayed out section that shows an example of what your bookkeeping could look like once you鈥檝e entered some transactions.

Bookkeeping Template Example of Bookkeeping

It doesn鈥檛 need to look like this, but this just shows a few different ways to use the bookkeeping template.

Section 3 - Your Information

In the 鈥淵our Information鈥 section, you鈥檒l 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 鈥淗ome Office Tab鈥 to deduct your home office expenses that you paid during the year.

Bookkeeping Template - GST/HST and Home Office

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.

Bookkeeping Template - No GST/HST or Home Office

Section 4 - Bookkeeping Transactions

Section 4 is where you鈥檒l be entering your bookkeeping transactions.听

Bookkeeping Template - Transactions

I find it easiest to record expenses and asset purchases first, and then sales afterwards. You don鈥檛 have to do it this way, but it鈥檚 how I鈥檒l 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 鈥淏ookkeeping Transactions鈥 section.

  1. Column A 鈥淭ransaction Description鈥 - Paste the transaction descriptions into Column A.
  2. Column B 鈥淒ate鈥 - Paste or type in the date into column B.听
  3. Column C 鈥淕ross Amount鈥 - Paste the total transaction amount (gross amount) into column C. Always paste it as a positive amount unless it鈥檚 a refund of an expense (refunds would be entered as a negative).
  4. Column D 鈥淕ST/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.
  5. Column E 鈥淣et Amount" - This is a calculated cell. You won鈥檛 be able to enter anything into this cell.听 I don鈥檛 recommend trying to enter anything in case it breaks the formula somehow!
  6. Column F 鈥淭ransaction Type鈥 - Choose a transaction type from the drop-down menu in column F.听 If you鈥檙e not sure what to choose, make your best guess. It doesn鈥檛 matter all that much. Check out our bookkeeping guide for some more guidance if you鈥檙e not sure.

Once you鈥檝e 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鈥檒l choose 鈥淪ales鈥 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.

  1. Column A 鈥淭ransaction Description鈥 - Paste the transaction descriptions into Column A. You can enter each sales transaction separately, or a one-line summary of the whole year.
  2. Column B 鈥淒ate鈥 - Paste or type in the date into column B. If you鈥檙e entering a summary of the whole year, just enter the year here.
  3. Column C 鈥淕ross Amount鈥 - Paste the total sales amount (gross amount) into column C. Always paste it as a positive amount unless it鈥檚 a refund (refunds would be entered as a negative).
  4. Column D 鈥淕ST/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.
  5. Column E 鈥淣et Amount - This is a calculated cell. You won鈥檛 be able to enter anything into this cell.听 I don鈥檛 recommend trying to enter anything in case it breaks the formula somehow!
  6. Column F 鈥淭ransaction Type鈥 - Choose the 鈥淪ales鈥 transaction type from the dropdown menu in column F.听听

You鈥檝e finished 95% of your bookkeeping once you鈥檝e done this part. Next up we鈥檒l 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鈥檒l find some additional tabs.

Bookkeeping Template Google Sheet Tabs

  1. Bookkeeping - This is where you鈥檝e entered your transactions.
  2. Home Office - If you are claiming home office expenses, you鈥檒l enter information into this tab.
  3. 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.
  4. GST - If you鈥檙e registered for GST/HST, you can use this to help you file your GST/HST return.

We鈥檒l 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 .

The home office tab in the bookkeeping sheet will let you record your home office expenses.

  1. 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.
  2. Home Expenses - Enter the full amount of your total annual home expenses into the categories.
  3. 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鈥檝e 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.

Bookkeeping Template - Home Office Tab

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鈥檝e completed entering your bookkeeping and home office transactions, you can view your income statement.

Bookkeeping Template - 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鈥檝e entered home office expenses, they鈥檒l 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鈥檛 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鈥檝e recorded all of your transactions, you鈥檒l see your GST/HST numbers in this tab.

Bookkeeping Template - GST/HST

This is most of what you鈥檒l 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鈥檙e not sure how it works.

香港金光佛论坛鈥檚 Bookkeeping Services

Our bookkeeping template works great and doesn鈥檛 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鈥檙e good at what we do!

Check out our bookkeeping services pagefor more info.

What is your goal today?

Hire 香港金光佛论坛

1. Hire 香港金光佛论坛

We offer a range of comprehensive bookkeeping and accounting services to support your small business in Canada. From tax compliance to expert financial guidance, we're here to provide clarity and help you make informed decisions.

View Our Services
Learn New Skills

2. Learn New Skills

Looking to gain the skills needed for bookkeeping and accounting? Enroll in our online courses to benefit from our years of experience working with small business owners.

View Our Courses
Share Our Knowledge

3. Expand My Knowledge

Overcoming the challenges of running a small business is our priority. Gain valuable insights and answers to your burning questions by exploring our:

Article by
Paul Sharpe, CPA, CA
.
Originally published
August 19, 2022
.
If you enjoyed this article, please consider sharing!

Let's Get Started

Please reach out using the form below and we'll get back to you right away!