Screenshots and Images
The printed version.
Open the template in Excel.
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.
This Excel invoice template is a revisited version of c4005 "Service Bill with Discount Percentage". It contains a horizontal rectangle logo, instead of the usual square logo. It also demonstrates how to use custom fields.
If you are only interested in using the template, then all you need to do is just download the template, extract / install it, and then use. If you would like to learn more on how we customize an invoice format, read on.
The basic rules of create a customized invoice template is detailed here.
First, it is always recommended to back up the template that you want to modify. This allows you to easily go back to the original version if you need to start again. You can also back up partially before any major changes in case you are unsure of what to do next. To make a backup, simply copy the Excel workbook file in Windows Explorer; or open the template as usual and execute the "Save As" command in Excel.
- Open the Excel workbook file (i.e. the template file) as usual.
- Set tax type to No taxes. To do this, click the Settings button on the "Invoice" worksheet, go to the "Taxes" tab, choose "No Taxes", and then click "Apply".
Two custom fields, Company and Fax#, are added to the "Bill To" section.
These custom fields must be added to both the Customer and Invoice Header database tables. They must be added to the Customer database table because you need them to be auto-populated when you pick a customer by click the "select-a-customer" button. They must be added to Invoice Header (InvHdr) database table because you want them to be saved on a per-invoice basis, so that the full invoice can be restored later by clicking the "Edit In Excel" command in the Invoices data list window.
To add the custom fields to database:
- Click the "Customers" button on the Invoice Manager for Excel ribbon tab; or click the "Customers" button on the "Invoice" worksheet.
- Click "Custom Fields" on the toolbar.
- Make sure "Database Table" shows "Customer". Click "Add Field" to open the "Add New Custom Field" dialog box.
Add the custom field "CustomerCompany" with the following specification. Click "OK" to add the field.
- Name: CustomerCompany
- Type: Text
- Size: 100
- Repeat the above step to add "Fax" field, with the following specification.
- Name: Fax
- Type: Text
- Size: 30
- After clicking "OK" button on the "Add New Custom Field", you are returned to the "Custom Field Manager" dialog box.
- Choose "Invoice Header" from the "Database Table" list.
- Repeat the above steps to add the following 3 custom fields.
Name Type Size Fax Text 30 CustomerCompany Text 100 InvDiscount Decimal (6,3)
- Click "OK" to close the "Add New Custom Field" dialog box. Click "Close" to close the "Custom Field Manager" dialog box.
- Close the "Customers" data list window. You are returned to the Excel "Invoice" worksheet.
Now we need to modify the Excel form to use the newly added custom fields. To do this, we need to firstly switch the template into design mode. With Excel Invoice Manager, you have to do this manually by clicking several buttons on the Excel ribbon. With Invoice Manager for Excel, you need to only push down the "Design Mode" button the Invoice Manager for Excel ribbon tab, which will unprotect sheet, shows gridlines and row / column headings.
To map or link the newly added fields from the backend database to the Excel invoice form, simply create two cell names on the invoice form.
- Insert a new row above the "Address" line in the Bill To section.
- Add the text label "Company" to the cell above the "Address" label.
- Merge the cells above oknWhoAddress. To do this, drag your mouse to select J12:L12, and then the click the "Merge and Center" button on Excel "Home" ribbon tab / "Alignment" group.
- Name the newly merged cell "oknCustomerCompany". Here "CustomerCompany" is the name of the field that was added to the Customer and "InvHdr" database tables. As you can see, the link or map between Excel cells and database table fields is created by names -- a cell named oknCustomerCompany will be linked to the field named CustomerCompany in the backend database.
If the field CustomerCompany exists in the Customer table in database, then the value of oknCustomerCompany cell will be saved to the customer record when you click "Save As New Customer" button.
If the field CustomerCompany exists in the "InvHdr" table in database, then the value of oknCustomerCompany cell will be saved along with the other invoice data when you click "Save To DB".
- Repeat the above step to add another line to the invoice form. Add the label "Fax#", name the new cell oknFax.
This template does not include the "Ship To" section. You can move the entire "Ship To" section outside the Print_Area. Click here for instructions on how to move fields / cells.
The standard/default invoice template has the ProductID column appear on the main invoice body, whereas on the customized version, the ProductID column is outside the Print_Area. To implement this, simply drag your mouse to select the entire ProductID column, and then drag the border of selected area to place it on the new location.
After moving out the ProductID column, expand the Description column so that it occupies the free space left by moving out the ProductID column. To do this, first drag your mouse to select the cells to merge on one line, and then click the "Merge and Center" button twice (first click unmerges the cells that are already merged; the second click merges all the selected cells). Please note you'll need to do this line by line because the "Merge and Center" command will merge all the selected cells.
You'll also need to move the "pick-a-product" buttons. To do this, make sure the invoice template is in design mode, push the CTRL key on your keyboard and then click the icon buttons successively, release the CTRL key, drag one of the selected buttons and move the button to the new location.
Now look at the bottom part of the invoice form, there is a custom field Discount. The discount is a percentage that is applied to subtotal.
To do this, add a new line above the "TOTAL" line and name the newly added cell oknInvDiscount. Format the newly added oknInvDiscount cell as a percentage: right-click the cell and choose "Format Cells", choose the proper format from the Number tab.
Modify the formula of the oknTotal cell so that it appears as the following:
This formula means that if the oknInvDiscount value is zero (not filled in), then total is equal to subtotal. Otherwise, total is equal to oknInvDiscount * oknSubtotal.
The last step is to fine-tune the invoice template - set colors, fonts and borders as you like. Most of the work can be done by right-clicking a cell and then clicking Format Cells.
That's it! This brief tutorial go through steps we take to create a customized service invoice template. Go through the steps once you'll find it is not so hard to create a customized template for Invoice Manager for Excel.
Format and Specification
|Name||Service Invoice with Horizontal Logo|
|Category||Service Invoice Template|
|Release Date||Sunday, March 19, 2017|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||19.50|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|Price||Free (0.00 USD)|
|System||Windows 7 and later, and Excel 2007 and later.|