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 UK VAT invoice template for service added a VAT rate drop down list to c4010 Service VAT Invoice Template. This allows you to pick a vat rate from the list on creating invoices, instead of type in manually. The drop down is implemented using Excel data validation feature.
The invoice form has a similar layout as c4010 Service VAT Invoice Template, because the major modifications are made outside of the printable worksheet area defined by the Print_Area range name.
When invoicing your customer, you pick a service item in the detail section as usual by clicking the on-sheet picker button or the static rectangle button below the "Product ID" label. And then, you click the cell below "VAT Rate", which shows a drop-down button when the cell is activated. Click the drop down button to pick a proper rate.
The current UK VAT rate could be found on VAT rates - GOV.UK. For example if you are providing children's car seats and home energy, you should choose reduced rate at 5%; if the invoiced item is food or children's clothes, you should choose zero rate (0%).
This form design was originally published on office-kit.com as C4-010. The following documents disclose design details.
This service vat invoice template does not usefields, but added several new fields on invoice header, such as , , . It also added two new columns, and . For each product or service item, you can set a VAT percentage rate, which is loaded when you pick up a product or service item by clicking one of the icon buttons. For a product where there is no VAT payable there simply will be no figure in the VAT section, therefore, where VAT is zero the price and the total will read the same. The currency symbol of this billing template is set for UK (United Kingdom) format.
In order to create this form layout from the default template shipped withsetup program, there are 3 types of tasks to do, including adding new fields to the invoice header, such as the field, adding new columns to the invoice body, such as the column and the column, and finally, moving cells / fields and controls to create the desired invoice form layout you like, as well as formatting cells.
Adding a new field to the invoice header
If a field appears on the invoice form only once per invoice, you should add the field to thedatabase table (i.e. ). If the field is related to customer and must be loaded on the invoice form when you pick up a customer, the field should be added to database table too. To comply with this rule, we should add the field to both the and database tables.
For a detailed tutorial on adding custom fields, visit this simple example.
After adding the custom field to database, the next step is to specify a cell on your invoice form to put thevalue. That is, the task here is to specify a cell, so that when a customer is selected, the customer's is loaded into this cell; and when you save an invoice, the value of this cell is saved into the field of the database table. This is implemented by naming a cell, as detailed below.
- Open the template in Excel.
- Switch to design mode by clicking the "Design Mode" button on Invoice Manager for Excel ribbon tab.
- Click to select a not-used cell, i.e. the cell where you want to put the information of a customer.
- Name the cell Customizing Invoice Template - Names)
Tip: The link or map between an Excel cell and the database is created by names. You name the cell, automatically loads the value of database field into this cell, and saves the value of this cell into field on saving an invoice. " " is the name prefix of all custom fields. . (See
- Right-click the cell, choose .
- Switch to the
Tip: If the cell of the custom field contains a formula, thebox must be checked to protect the formula. This way, does not write to / alter the value of the cell, but will let Excel calculate the cell value. tab, and then uncheck the box. This makes sure that can write/alter the cell value when the invoice form is protected.
- Exit design mode by clicking the "Design Mode" button again.
- Save your invoice template by clicking Excel menu -> in Excel 2003, or by clicking button -> in Excel 2007, or by clicking -> in Excel 2010 / 2013 / 2016.
Adding a new column to UK VAT invoice form
Just like adding a field to invoice header, adding a new column to invoice body involves two parts - adding a field to database tables, and naming the cells on the invoice body so that when you pick up a product or service, the custom field can be loaded into the named cells, and when you save an invoice, the named cells are saved into the corresponding database field.
Unlike adding a field to invoice header, where you add database field to thedatabase table and if a field is related to customer then it should be added to the customer database table, adding a new column to invoice body requires you to add the field to the ("InvBdy") database table, and if the field is related to product (i.e. the value of the field must be loaded into the invoice form when you pick up the product or service item) it should be added to the database table.
Considering this VAT invoice template, we use two custom columns (i.e. custom fields),and . means the VAT percentage rate of product or service; means VAT amount, which is calculate from VAT percentage rate and Price (as this is a service VAT invoice template and the is assumed to be 1). Based on this usage analysis, it is clear that should be added to both and database tables, and should be added to database table.
The next step is to name the cells on the invoice form to letknow where to read/write the custom fields added to database.
- Open your invoice template.
- Switch the worksheet to design mode.
- Decide where to put the column. In this VAT invoice template, we put the column inside the printable area, and put the column outside the printable Area. People in the UK know the VAT percentage rate; therefore, stating the amount due such as (Fee) + (VAT) = (total) is better than putting a percentage.
- Name the cells. For the column, you name the cells , , ... ; And for the column, name the cells , , ... .
- Drag your mouse to select all the cells.
- Right-click one of the select cells, choose .
- On the tab, uncheck option.
- Drag your mouse to select all the cells.
- Right-click one of the selected cells, choose .
- On the tab, check the option.
- Fill in formula " " to cells. For example, for the cell, the formula should be " "; and for the cell, the formula to fill in should be " ", and so on. This formula calculates VAT amount automatically when you load product or service items.
- Tip: Since this is a service VAT invoice template, the of product or service is filled with 1.
- Once finished, exit the Excel worksheet from design mode.
- Save your invoice template.
Moving cells and controls, formatting cells with UK based currency symbol
It's clear that the invoice format of the VAT invoice template is very different from what offered by the default invoice template installed bysetup program, yet it is still easy to create the new invoice form from the default invoice form. The basic tasks are moving cells and controls, formatting cells.
If you don't need a cell or control to appear on the printed invoice, move it out of the. is an Excel defined range name, which tells Microsoft Excel what range to print when you execute the command. For example, the VAT invoice template does not use the column, so we move it out of the . Moving cells is also necessary if you want a cell/control to appear on a new location.
To move a cell or a control on the invoice template, follow the steps below.
- Unprotect the worksheet.
- Click to select the cell or control you want to move, or drag your mouse to select all the cells you want to move (such as a column), or if the cells / controls to move are not adjacent, push key on your keyboard and click the cells / controls one by one.
- Drag the selected cells / controls to the new location.
- Move other cells or controls as needed.
- Once finished, protect the sheet by exit design mode.
The following procedure describes how to set a cell to use UK currency symbol "", to replace the default US-dollar currency symbol " ".
- Unprotect the worksheet.
- Right-click the cell for which you want to set its format, or drag your mouse to select multiple cells and right-click one of the selected cells, choose command from the menu.
- On the tab, click " " on the list.
- Choose " " from the list.
- Set the other options as required.
- Click to close the dialog box.
- Protect the sheet.
- Save your template.
There are many other options you can adjust/set on thedialog box. Once you are familiar with various formatting tools provided by Microsoft Excel, it is easy to create the UK VAT invoice template based on the default invoice template shipped with setup program.
Format and Specification
|Name||Invoice Template VAT UK|
|Category||Service Invoice Template|
|Release Date||Tuesday, March 21, 2017|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||16.50|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|System||Windows 7 and later, and Excel 2007 and later.|