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.
The default product report contains fields defined in the "Invoice Body" database table. This simple invoice template sample demonstrates how to add fields in the "Customer" or "Invoice Header" database tables to the "Product" report by using custom fields.
In this sample, we'll take the result template built in c4051 Simple Invoice Template - Discount Amount Field as a starting point, create two custom fields, "CustomerName" and "CustomerContact" in the invoice body database table, and then add them to the product report. Note that in order to follow this tutorial, you should have Invoice Manager for Excel installed, which is our invoicing software app for automated invoice processing. Download the fully functional trial version for free.
- As always, first you need to back up the files that will be modified. In this case, we'll modify both the template and database files. So back up both files.
- Open the invoice template as usual.
- Click the "Invoices" button on the ribbon tab.
- Click "Custom Fields" on the toolbar. This opens the Custom Field Manager.
- From the "Database table" drop-down list, choose "Invoice Body".
- Click "Add Field". This opens the "Add New Custom Field" dialog box.
- Fill in these values - Name: CustomerName, Size: 100. Click OK. This create a custom field named "CustomerName".
- Click "Add Field" again. Create another custom field "CustomerContact", set field size to 100.
- Click "Close" to close the Custom Field Manager and return to the invoice spreadsheet form.
- Click the "Design Mode" button on the "Invoice" ribbon tab to switch the billing form into design mode.
- Now choose a place to populate the customer name and contact number on the invoice body. Note these information will not be printed, so we choose column W and column X. Enter the column heading label "Customer Name" into $W$21, and then enter "Contact" into $X$21.
- Name the "CustomerName" cells, ranging from W22 to W33. The names should like these - oknCustomerName_1, oknCustomerName_2, oknCustomerName_3, ... oknCustomerName_12.
Name the "CustomerContact" cells in the same way too, ranging from X22 to X33. The names should like these - oknCustomerContact_1, oknCustomerContact_2, oknCustomerContact_3, ... oknCustomerContact_12.
- Assign formula to the first customer name cell (oknCustomerName_1): =if(D22="","",oknWhoName). This formulas means that, if the first Product Description cell (D22) is empty, then the current cell is empty; if the first Product Description cell is not empty, fill the current cell with the value of customer name (oknWhoName).
- Make sure oknCustomerName_1 ($W$22) cell is selected, push left mouse key on the fill handler (right-bottom corner) of the selection box, drag down till W33. Excel fill formulas automatically into all the other oknCustomerName_* cells.
- Click to select the first customer contact number cell (oknCustomerContact_1). Assign it a formula: =IF(D22="","",oknWhoPhone).
- Make sure oknCustomerContact_1 ($X$22) cell is selected, push left mouse key on the fill handler (right-bottom corner) of the selection box, drag down till X33. Excel fill formulas automatically into all the other oknCustomerContact_* cells.
As you can see from this sample, it is easy to populate and calculate data using Excel formulas. While there are other tools like Microsoft Word, Adobe Acrobat or even Photoshop and some online apps like Google Docs, that could create beautiful invoice and billing forms, one of the best / big advantages of Excel is that it simplifies calculations.
- Click and activate the "Product Report" worksheet.
- Click the "Unit Cost" column heading. Quickly click "Format Painter" twice on Excel "Home ribbon tab.
- Click the next two cells to the right of the "Unit Cost" cell - in this case, J10 and K10 . This paste the format of the "Unit Cost" cell to J10 and K10. Press ESC on keyboard to exit the format painter.
- Click to select the J10 cell. Name it "oknRpCustomerName". Fill column heading into this cell like "Cust. Name". You may need to resize the column to make it wider.
- Click to select the K10 cell. Name it "oknRpCustomerContact".Fill column heading into this cell like "Cust. Contact". Again, you may need to resize to column to make it wider.
- Exit design mode by clicking the "Design Mode" button again on the "Invoice" ribbon tab.
- Now if you create and save a new invoice, the customer name and contact number will be saved along with each item. And if you generate a product report, the customer name and contact number will be shown on report too. Note that after adding two new columns, the "Product Report" will not be printable with the default paper size and orientation. You may like to change paper size or orientation, or hide some columns by clicking the Columns button on the Product Report worksheet.
The definition of the database structure in Invoice Manager for Excel established the "one-to-many" relationship between the "Invoice Header" (i.e. "InvIndr") and "Invoice Body" (i.e. "InvBdy") database tables. This means that, for each record in the invoice header database table, there might be one or many items belong to it.
On the other hand, the main content of the "Product" report is retrieved from the invoice body database table, adding the fields of the invoice header table to the invoice body table will create many redundant data, as they must be repeated for each item. One exception though - the definition of invoice body items must include the invoice# (InvoiceID) field, since it is the primary key of the invoice header table that identifies each uniquely.
However there are still situations where you prefer to know which customer purchased what product. For example, if you are service provider you may want to know what service offered to what customer from the product report. If you are selling products, the advantages of adding customer information to the product report is that you will be able to easy find where (or to whom) the products were sold to. 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.
Format and Specification
|Name||Simple Sample - Customer Name on Product Report|
|Category||Sales Invoice Template|
|Release Date||Friday, February 5, 2016|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||18.00|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|Price||Free (0.00 USD)|
|System||Windows 7 and later, and Excel 2007 and later.|