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.
To give customers detailed discount information for each sales / service item on their bills and invoices, you need a "Discount" column on the invoice layout. This template demonstrates how to add the column to an existing invoice format.
By adding the "Discount" column, we meant not only re-design the Excel spreadsheet form so that it has an additional column, by also make the newly added column work with Invoice Manager for Excel -- i.e. when you click the "Save Invoice" button, the content on the "Discount" column should also be saved along with each invoicing item; if "Clear & New" command is clicked, the "Discount" column should get cleared too. This is implemented by using custom fields (user-defined fields) in Invoice Manager for Excel.
We've already released several templates here on InvoicingTemplate.com that are focused on discounting. For example, this tutorial is based on the result template built in Simple Invoice Template - Discount Amount Field that demonstrates how to add a discount amount field to the bottom of the billing form. And, there are also bill / receipt designs that offer discount column, such as Sales Invoice Template with Discount Amount Column and Sales Invoice Template with Discount Percentage Column. There are also samples on uniformsoft.com, at Template with Discount Amount Column, and on office-kit.com, at Invoice Template with Discount Column (Updated September 2017: All templates hosted on office-kit.com will be moved to InvoicingTemplate.com). All these templates works with Invoice Manager for Excel well.
This tutorial differs from others in that it focus on the steps that we followed to add the "Discount" column. The basic template that we used as the origin and starting point of this tutorial already has a "Discount" field added, which is a manually fillable custom field. After adding the "Discount" column, we'll also use Excel formula for automated calculation of discount amount.
Let's see how to add the new column, step by step.
- First, back up the Excel invoice template and database file. In this sample, we'll need to modify the database, so creating a backup of the database is necessary.
- Open the template.
- Push down the "Design Mode" button on the "Invoice" ribbon tab.
- This is not a required step - but you may like to try it. Go to Excel "Page Layout" tab. In the "Themes" group, click to open the "Colors" list, choose "Blue".
As you can see, the whole invoice form now has a new color theme now. Like the themes feature in Microsoft Word, this is the quick and best (easy) way to change the look & feel of the form.
- Drag your mouse to select all cells on the "Description" column, including the heading, ranging from $D$21 to $D$33. Click the "Merge and Center" button on Excel "Home" ribbon tab. This unmerges all the cells on the "Description" column.
- Drag your mouse to select all the cells on the "Quantity" column, including the heading, ranging from $I$21 to $I$33. Push the mouse key on the border of the selected area. Without releasing the mouse key, move the entire selected area on column left (Destination: $H$21:$H$33).
- Now select the entire "Unit Price" column, including the heading. Drag the select area one column left. The new location of the "Unit Price" column is now $I$21:$I$33.
- With the new "Unit Price" column still selected, click the "Format Painter" button on Excel "Home" ribbon tab.
- Click the first empty cell left by moving out the "Unit Price" column, i.e. $J$21. This applies the format of the "Unit Price" column to the empty column.
- Drag your mouse to select all the cells on the empty column, except the heading cell, ranging from $J$22 to $J$33. Right-click one of the selected cell, choose "Format Cells" from the shortcut menu. On the "Format Cells" dialog box, go to the "Protection" tab, make sure the "Locked" property is cleared. Click OK to close the dialog box. This makes sure that the "Discount" column is manually fillable when the sheet is protected.
- Click to select heading cell of the empty column, i.e. $J$21. Enter the column heading text "Discount".
- Name the following empty cells. The names should be "oknItemDiscount_1" ($J$22), "oknItemDiscount_2" ($J$23), "oknItemDiscount_3" ($J$24) ... "oknItemDiscount_12" ($J$33).
- Drag your mouse to select all the cells on the "Description" heading, ranging from $D$21 to $G$21. Click "Merge and Center" on Excel "Home" ribbon tab.
- Repeat the above step for each line of the "Description" field. For example, select $D$22:$G$22, click "Merge and Center"; select "$D$23:$G$23", click "Merge and Center", and so on. If you are very familiar with Microsoft Excel, you can also use "Format Painter" to speed up the processing of merging.
- Select all the "Description" cells, ranging from $D$22 to $G$33. Click "Align Left" icon button in the "Alignment" group on Excel "Home" ribbon.
Click to select the "Discount (Amount)" cell, $K$35. Enter a formula for this cell, "=sum (J22:J33)". Right-click the same cell $K$35 again, and choose "Format Cells" from the dialog box. Go to the "Protection" tab, checked the "Locked" property. This makes sure that the cell is not manually fillable once the sheet is protected.
- Now we need to create the definition of the custom field "ItemDiscount" in the database. To do this, we need to use the "Custom Field Manager" provided by Invoice Manager for Excel. In order to use this tool, we have to first exit design mode by clicking the "Design Mode" button again on the "Invoice" ribbon.
- Click "Invoices" on the ribbon.
- Click "Custom Fields".
- From the "Database table" list, choose "Invoice Body".
- Click "Add Field".
- Enter the field definition as follows - Name: ItemDiscount; Type: Decimal; Precision: 10; Scale: 2.
The map between an Excel cell and a database field is established by using names. For the invoice body section, all "oknItemDiscount_*" fields are mapped to the "ItemDiscount" field defined in the "Invoice Body" database table. That is, a cell should be named with "okn + Database Field Name", where we call "okn" is a prefix of the cell name.
- Save the template by clicking the "Save" button on Excel quick access toolbar.
- Now you can test the template to see if it works in the way met our plan.
Giving discounts to customers / clients is a common practices in many businesses, for example It is common for attorney / legal, medical, catering, restaurant, law firms (whether small or big), health insurance billing or statement / timesheet, car / vehicle sales, graphic designs, freelance, plumbing, marketing, business plans, accounting, house rent (yearly or monthly), or even home businesses for monthly bill.
This sample does not change the tax settings implemented with the base template. It still has GST and PST taxes set on the bottom of the form. So this is a GST-compliant tax invoice template. If you need to change the tax settings, click the "Settings" button on the "Invoice" ribbon tab, and then go to the "Taxes" tab where you can find the overview of all tax settings.
Format and Specification
|Name||Simple Sample - Adding Discount Column|
|Category||Sales Invoice Template|
|Release Date||Sunday, February 7, 2016|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||18.00|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|System||Windows 7 and later, and Excel 2007 and later.|