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.
How can you tax items at different rates on the same invoice? This Excel sales invoice template offers a simple sample which allows you to define a tax group / type for each item, multiple tax rates is then applied according to the tax type of each item.
This template was originally published on office-kit.com as C5-016, and here on InvoicingTemplate.com there were already customized layouts created based on C5-016, such as c5034 Mixed Tax Rates (5 Columns), and c5035 Mixed Tax Rates in an Invoice (7 Columns). For more samples and formats related to taxing, visit tax invoice template.
One thing to note is that, since the formulas and relationship of fields on the form are deeply customized, the options on the "Taxes" tab of the "Settings" window may not work. So if you want to further customize such templates / forms, you may need to do that manually -- i.e. without using the options on the "Taxes" tab.
While not as complex and expensive as Quickbooks, Invoice Manager for Excel is able to handle invoices with mixed tax rates. This is a great sample that shows the advantages of Excel invoicing over Word invoicing, as you can use Excel formulas to search and calculate values on the form. With Microsoft Word, that could hardly be done without macro or VBA code.
Unlike c5034 and c5035, this template (c5016) has exactly the same layout as C5-016 on office-kit.com. Of course we made necessary modifications to make it meet the requirements and specifications of Invoice Manager for Excel and InvoicingTemplate.com.
The following description from office-kit.com still provides useful information for you to apply this template to your business.
The default invoice template does allow you to choose tax-type with 3 options: no-tax, one tax and two taxes, but this option is applied to all the items on the invoice form. This slightly-customized invoice template differs from the default invoice template in that it allows you, on the single invoice form, to have some products with no tax, some products with one tax (such as GST) and some products with two (such as GST and PST).
Setting up and using the Invoice Template
When using the customized template for the first time, you should firstly set up the tax names and rates, as detailed below.
- Click the button on the worksheet.
- Go to the tab.
- Modify the tax names and rates as you like. Don't modify any other options on the tab.
- Save the invoice template by clicking Excel menu -> in Excel 2003, or by clicking the button on the ribbon in Excel 2007 and later versions.
To test the template:
- When creating new products, you click the button on the worksheet, and then click .
- On the
tab, specify the tax type for the current product: , , and .
- TAX0 - tax exempt
- TAX1 - only the first tax will be calculated for the product
- TAX2 - Both the taxes will be calculated for the product
- When creating a new invoice, you click the icon button to select a product. The tax type of the product is retrieved automatically.
- The invoice template calculates taxes based on the types of the products.
This tax invoice template uses only one custom field, , which is added to both the and the database tables.
By adding thefield to the database table, you don't have to reenter the tax type of the specified product each time it is picked on creating invoices, because the information is loaded automatically when you pick up the product. By adding the field to the database table, the tax type information is saved along with other product data, as well as customer data, when you click , so that you can easily restore a previously saved invoice at a later time.
You specify the tax type of a product on thewindow, with the value , or . When the invoice template loads such a product into the worksheet, it uses the following formula to calculate the first tax:
=ROUND(SUMIF(oknProductType_1:oknProductType_12,"=TAX1", oknLineTotal_1:oknLineTotal_12)*oknTax1Rate+ SUMIF(oknProductType_1:oknProductType_12,"=TAX2", oknLineTotal_1:oknLineTotal_12)*oknTax1Rate,2)
Thefunction tests the value of , , ... cells, and if a value of the cells is " " or " ", the corresponding " " cell value is added. The sum is then multiplied by to get the amount of the first tax.
The second tax formula works in a similar way.
In the second tax formula, thefunction tests the value of , , ... cells, and if a value of the cells is " ", the corresponding " " cell value is added. The sum is then multiplied by to get the amount of the second tax.
Format and Specification
|Name||Multiple Tax Rates on One Invoice|
|Category||Sales Invoice Template|
|Release Date||Friday, March 17, 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.|
May 15, 2019
- Moved "oknWhoID" cell (the client# cell) out of the "Bill To" section.