BoostExcel.com main content

Download

License: Private Use (not for distribution or resale). See our Terms of Use.

You are safe to download the resources. We've added our digital signatures to the files.

DescriptionSizeDownload

Download this if you have already installed the Microsoft Store edition of Invoice Manager for Excel, and are looking for additional customized templates.

555 KBDownload

Download this if you want this design to be set as the default template by the installer program.

6.08 MBDownload

Screenshots and Images

The printed version.

Simple Sample - Discount Amount on Sales Report

Open the template in Excel.

Simple Sample - Discount Amount on Sales Report (IMFE Edition)

Detail

Most samples and tutorials here talks about how to customize the invoicing form. This simple invoice template is a bit different - it details how to add a custom field to Sales Report.

The template created in c4051 Simple Invoice Template - Discount Amount Field adds a discount amount field to the bottom of the invoice format and creates formulas to apply the discount. However this discount is not clearly shown on reports.

The default invoice template shipped with Invoice Manager for Excel, as well as all the simple invoice templates created in this serial of tutorials, have the following reports that are based on the "Invoice Header" database table - Sales Report, Customer Report, Sales Rep. Report. All custom fields defined in the "Invoice Header" database table could be added to these reports.

In this sample of simple invoice template customization, we'll look at how to add the Discount Amount field created in c4051 Simple Invoice Template - Discount Amount Field to the Sales Report.

The steps of adding the discount amount field to Sales Report are as follows.

  1. Backup the invoice template.
  2. Open the template.
  3. Activate / switch to the "Sales Report" worksheet.
  4. Activate / Switch to Excel "View" ribbon tab.
  5. Check "Gridlines" and "Headings" in the "Show" ribbon group.
  6. Click to select the "Balance Due" report heading cell ($N$11).
  7. Click the "Format Painter" button in the "Clipboard" group on Excel "Home" ribbon tab.
  8. Click the cell next to "Balance Due" on the same row - in this sample, it is $R$11. This copies the forma the "Balance Due" cell to the new / empty cell.
  9. With the new cell still selected, enter text "Discount" into this cell, and then enter the cell name "oknRsDiscountAmount" into the name box on the left-hand of Excel formula bar. This assign the specified name to the cell. In this name, "oknRs" is the prefix of the name, "DiscountAmount" is the database field name.

    When adding a custom field to the Invoice worksheet, we leant that the mapping between an Excel cell on the Invoicing form and the backend database field is established by names. For example, the database field "DiscountAmount" in the "Invoice Header" database table is mapped to the cell name "oknDiscountAmount" on the "Invoice" worksheet.

    Likewise, a report column is mapped to a database field using names. In this sample, we name the report heading cell "oknRsDiscountAmount", which maps the report column to the database field "DiscountAmount".

    "oknRs" is the name prefix of the "Sales Report". Each report has its own name prefix, as detailed below.

    • Sales Report - oknRs
    • Customer Report - oknRc
    • Product Report - oknRp
    • Customer Rep. Report - oknRr
  10. The default Sales Report is designed to be printable on standard A4 paper. Adding the Discount field makes it too wide on the horizontal direction. To make sure it fits into one single page on horizontal direction, we need to hide a column. To do this, click the "Columns" button on the taskpane, and then uncheck the column that you want to hide. In sample, we hide the "Sales Rep." column. You can also adjust the column widths to make the report suitable for the paper size.
  11. Once everything is done, go to Excel "View" ribbon tab and uncheck "Gridlines" and "Headings".
  12. Save the template by clicking the "Save" button on Excel quick access toolbar.

Invoice Manager for Excel provides flexible reporting features that allows you to add new user-defined fields (i.e. custom fields). The simple invoice template created in this tutorial provides a detailed sample on how to do this. Visit Product Names and Shipping Address on Aging Report for information on how to show invoiced items on the "Aging" report sheet.

Format and Specification

Template#c4055
NameSimple Sample - Discount Amount on Sales Report
CategorySales Invoice Template
Release DateSunday, January 31, 2016
Format (XLS or XLSX).xlsx
Columns4
Lines12
Line Height (Points)18.00
Print Area$D$2:$K$47
Papaer Size / OrientationPortrait
Default Margins (Points)
Left22.68
Right22.68
Top45.35
Bottom45.35
PriceFree (0.00USD)
SystemWindows 7 and later, and Excel 2007 and later.

Update History

November 13, 2018

  • Changed the number of taxes to one and the default tax name from "GST" to "Sales Tax". See Types of Taxes for more information.

Related

Thumbnail for Simple Sample - Total Quantity on Sales Report
Thumbnail for Simple Sample - Moving Balance and Payment Cells
Thumbnail for Simple Sample - Moving Item# Column
Thumbnail for Simple Sample - Using Currency Symbol
Thumbnail for Simple Sample - Discount Amount Field
Thumbnail for Simple Sample - Moving Taxable Controls
Thumbnail for Simple Sample - Changing Paper Size