You are safe to download the resources. We've added our digital signatures to the files.
Download this if you have already installed the Microsoft Store edition of Invoice Manager for Excel, and are looking for additional customized templates.
Download this if you want this design to be set as the default template by the installer program.
Screenshots and Images
The printed version.
Open the template in Excel.
This free Canadian invoice template charges both GST (Goods and Services Tax) and PST (Provincial Sales Tax). If the item being invoiced is a service, GST is charged; if the item is a material, then both GST and PST is charged. This billing format is suitable for business who sells products or provides service in Canada.
This template was originally published on office-kit.com as C7-006. Here on InvoicingTemplate.com there are several customized version for this format: c7006 Handyman Invoice Template, c5055 Handyman Invoice Template (Sales Tax), and Handyman Bill Sample (No Tax). If you charge HST (Harmonized Sales Tax), visit our sample invoice template in Excel format at Canadian Invoice Template with HST. All of our templates come with a PDF sample invoice which you can download for free too.
There are online GST and PST invoice template for Canada / Ontario in Microsoft Word too, which you can download for free or a small fee. However Microsoft Excel is better on doing calculations. As you can see from this sample GST and PST invoice template, all you need to do is to enter the basic values, all calculable values are shown automatically when the basic values are entered. If you install Invoice Manager for Excel, this template can be a complete and easy to use GST and PST invoicing software.
The following description was originally published on office-kit.com for C7-006 "Tax Invoice Template That Charges One Tax (GST) on Services and Two Taxes' (GST & PST) on Materials". We republish it here on InvoicingTemplate.com to provide with you additional information about this template.
The default template and database shipped withalready have an " " field defined for products/items, which can be set by checking the " " box on the window. If we load this " " on to the invoice form along with the other product/item data, it is then simple to use Excel formulas to choose and calculate GST/PST taxes based on the " " value - If the current product/item is a service, we calculate only GST; if the current product/item is not a service, we calculate both GST and PST.
Custom fields added to the tax invoice template
In order to display GST, PST on the invoice detail section, we need to add two custom fields to the "InvBdy" database table. To make sure we can restore a saved invoice completely after it is saved, we also need to add the "" field to the "InvBdy" database table. The following table shows all the custom fields added to implement the GST / PST template as required.
|Table name||Field name||Field type|
Unlike Excel Invoice Manager, which uses a separate program "Database Sesigner" to create and modify custom fields; Invoice Manager for Excel has a "Custom Field Manager" built in. Visit Simple Sample - Discount Amount Field for a detailed tutorial on using custom fields.
Load "" values along with the other product/item data
The "" field appears as a checkbox on the window, and its underlying value stored in the backend database is an integer - For a service whose box is checked on the window, its underlying value is ; otherwise it is .
To load the "" value into the invoice form when a product/item is selected, all you need to do is to name a column of cells on the invoice form, in the format , , ..., , where " " is the prefix for all the cell/range names used by ; and " " is the field name in both the and database tables - it is this name created the link between the invoice form and backend database, i.e. depends on this field name to find out where to display a value loaded from database; and the last part, " ", " ", ..., " ", is the line number.
The following are the brief steps to implement thecolumn on the invoice sheet.
- Backup the template.
- Open it as usual.
- Switch to design mode by pressing the "Design Mode" button on the ribbon tab.
- Drag your mouse to select a column of cells at the right side of the invoice body and out of the print area.
- Right-click one of the selected cells, choose Locking/Unlocking Cells. . On the tab, set a background color for the cells; on the tab, clear the box - If you don't clear the box, will not write data to the cells once the invoice template is protected, as detailed at
- Name the cells oknIsService_1, oknIsService_2... oknIsService_12.
Show GST, PST taxes on the invoice body
This tax invoice template also has GST and PST tax columns on the invoice body, so that the tax charged on each product/item line is clearly listed. To implement this, you need to add two columns to the invoice body section.
- Add two columns to the invoice body. In this implementation, the GST and PST tax columns are added to the left of the column.
- Name the cells. The first column will store GST tax, so the cells are named , ... ; the second column will store PST tax, so the cells are named , ... .
- Fine tune the invoice form, such as settings colors, adjusting the sizes of cells, etc., to make the newly added column looks natural to the other part of the invoice form.
- Now create formulas for the two tax columns. For the GST tax cells, the tax is charged both on services and materials, so the formula should be:
And, PST tax is charged on materials only, so the formula should be:
=IF(oknTaxable_?,IF(oknIsService_?=1,0, ROUND(oknPrice_? * oknQuantity_?* oknTax2Rate,2)),0)
In above formulas, "_?" is the line number of the current item/product.
- The formulas of the two tax cells on the bottom of the invoice form should also be modified. First, for the GST tax cell, it sums all the GST taxes calculated on the invoice body, so the formula should be:
Similarly, the formula for the PST tax cell should be:
Format and Specification
|Name||GST and PST Invoice Template|
|Category||Sales Invoice Template|
|Release Date||Monday, March 20, 2017|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||19.50|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|System||Windows 7 and later, and Excel 2007 and later.|