BoostExcel.com main content

Summary

CALCULATION

An invoice (template) that calculates total is helpful in that it is not necessary to do that manually. This simplifies creation of invoices and void mistakes.

Our templates include formulas that calculate total and taxes. For example the default template shipped with Invoice Manager for Excel uses the formula like this:

=ROUND(oknSubTotal + oknShippingCost + IF(oknTaxType=0, 0, IF(oknTaxType=1, oknTax1, oknTax1+oknTax2)), 2)

And, uses this formula for calculating total due:

=ROUND(oknTotal-oknPayments,2)

However, as shown with many samples here on InvoicingTemplate.com, the formulas could be modified to meet your business requirements, for example to include discount.

Here are our templates closely related to Invoice That Calculates Total.

Simple Sample - Manually Fillable Line-totals
c2028
Simple Sample - Manually Fillable Line-totals

In this sample of simple invoice template, we look at how to move out the Quantity and Unit Price column, remove the formulas of all the "Line-total" cells to make it manually fillable. With this design, the line totals will not be calculated automatically, but be filled in manually. This is useful businesses, such as service providers, who charge customers on a project-basis.

Sales Invoice with Total on Top (2 Columns)
c2043
Sales Invoice with Total on Top (2 Columns)

This is another sample invoice form that is created from general sales invoice template. There are two columns on the invoice body within the print area, 'Description' and 'Amount', both are editable. The most interesting part regarding this free invoice format is that the 'Total' section (i.e. the summary section) was moved to the heading section of the invoice form, where we usually find the 'Shipping Address'. We cannot say which layout is the better one - to put the summary on the bottom, or to put the summary on the heading - what we can say is that, this is just a different invoice format.

Sales Invoice with Total on Top (3 Columns)
c3027
Sales Invoice with Total on Top (3 Columns)

This invoice template sample was created based on the general sales invoice template. The most notable characteristic of this free invoice template is that the summary fields are moved to the top of the invoice form. There are 3 printable columns, line#, description and amount, reside within the print area. The line# column is filled in automatically when you enter an amount; both the description and amount columns are editable.

Simple Sample - Total Quantity on Sales Report
c4103
Simple Format - Total Quantity on Sales Report
  1. First, make sure you have created a backup copy of the template.
  2. Open the template / spreadsheet file.
  3. Click "Invoices" on the "Invoice" ribbon tab.
  4. Click "Custom Fields" on the toolbar to open the "Custom Field Manager" dialog box.
  5. Make sure "Database Table" is showing "Invoice Header".
  6. Click "Add Field" to open the "Add New Custom Field" dialog box.
  7. Enter: Field Name - TotalQuantity; Type - Decimal; Precision - 10; Scale - 2.
  8. Click "OK" to create the new "TotalQuantity" field.
  9. Close all dialog boxes and return to Excel form.
  10. Switch to design mode by clicking the "Design Mode" button on the "Invoice" ribbon. This allows you to modify the invoice design.
  11. Click the cell where you want to place the quantity total. In this sample, we choose $I$34 - i.e. the cell below the "Quantity" column.
  12. Enter the formula to calculate the sum of all quantities:
    =sum(oknQuantity_1:oknQuantity_12)
  13. On the formula bar, click to activate the name box located at the left-end, enter the cell name "oknTotalQuantity". Note this name must be the combination of the prefix "okn" and the database field name "TotalQuantity". This name creates the map between the Excel cell and the backend Access database field to store the value when you save an invoice.
Proforma invoice format that helps selling across boards
c7035
Proforma invoice format that helps selling across boards

If you upgrade from earlier versions of Invoice Manager for Excel, the installer program removes the add-in, which is installed by the previous version of our invoicing software. To make the proforma invoice template compatible with this new release of Invoice Manager for Excel, all calls to the macro add-in are moved from the buttons on the "Invoice" worksheet. Instead, we added a hyperlink to each button.

Advanced Sample - Invoiced Total in English Words
c5102
Advanced Bill Sample - Invoiced Total in English Words

As of the current implementation, Invoice Manager for Excel does not contain a built in function for converting a numeric value into equal English words. Fortunately, Invoice Manager for Excel is built upon Excel. Like other Office programs, Microsoft Excel supports VBA (Visual Basic for Application) macros, which you can use to extend the functionalities of Excel itself, including creation of user-defined formulas (functions).

Simple Sample - Using Currency Symbol
c4050
Simple Billing Format - Using Currency Symbol

This simple invoice template details the steps required to show a specific currency symbol on all the currency-related fields, for example "Unit Price", "Line Total", "Subtotal", "Total", "Paid" and "Total Due".

Blank Service Invoicing Template
c3008
Blank Service Invoicing Template

The template has two taxes set on the bottom of the form, GST and PST. You can modify the tax names and rates easily by simply type in what meets your suitation once the spreadsheet is unprotected. If you need only one tax or need no tax, install UIS, and modify the taxing rules by several simple mouse clicks.

Simple Sample - Discount Percentage Field
c4054
Simple Invoicing Template - Adding Discount Percentage Field

The implementation of c4051 Simple Invoice Template - Discount Amount Field adds the discount amount field on the bottom of the form at J35 and K35. In this new sample, we'll add percentage fields on the same row but before the discount amount fields, at G35 and H35.

Netherlands Sales Invoice Template 2
c5050
Netherlands Sales Billing Form 2
  • date of issue;
  • a specific number;
  • where necessary, the VAT identification number of the taxable person and his customer;
  • full name and address of the taxable person and his customer;
  • description of the goods or services;
  • quantity of goods supplied or, if necessary, of services rendered;
  • date of supply of goods or rendering of services;
  • place of supply of goods or rendering of services;
  • taxable amount per rate;
  • VAT rate;
  • VAT amount payable;
  • total amount payable;
  • where an exemption is involved, reference to the provision which justifies the exemption.
Excel Quotation Template
c9008
Excel Quotation Format with Special Discounting Model

This free quotation and estimation template in Microsoft Excel ".xlsx" format provides a sample of customizing the default invoice template shipped with Invoice Manager for Excel. It implements a special / complex discounting model to meet practical business requirements.

Simple Welding Invoice Template
c5064
Simple Welding Invoicing Form

The template has 5 columns on the detail section within the printable invoice form - the 4 usual columns, Qty, Item, Price and Line Total are standard columns that could be found on the default template that is shipped with Invoice Manager for Excel. The other column, titled with "Unit", is a custom field defined in both the Product and Invoice Body database table.

Proforma (3rd Sample of Customization)
c7013
Proforma Invoicing Form

Tip: The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

Multiple Tax Rates on One Invoice
c5016
Multiple Tax Types on One Invoice

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.