Why you need a longer description field?
Sometimes you may need to enter long product descriptions. For example, if you are in the service business you may need 2 or more lines of space to show the detailed information about your service. You may also want to cut down the number of rows shown a page.
Tip: You can download the sample template from the bottom of this page (for Excel Invoice Manager only).
This tutorial goes through the steps of customizing the standard invoice form to create a sample invoice formats that supports long/multiple-line product description.
We will also move all the Product ID cells out of the Print_Area to leave more space for the Product Description cells, and hide several rows on the worksheet to fit the form into standard A4 papers. More information about page layout and printing could be found at printable invoice formats.
Steps of customization
Note that the screen shots and figures are captured at the time our invoicing software was still called Excel Invoice Manager. So you may find they are not up-to-date as since 2013 we rebuilt it and renamed it to Invoice Manager for Excel (namely Uniform Invoice Software).
- Copy the standard invoice template (INVOICE.xls) in the installation folder of Excel Invoice Manager, or copy your customized version. Name the new workbook file LongProductName.xls.
- Open the LongProductName.xls template in Microsoft Excel.
- Unprotect the sheet by clicking Excel menu Tools / Protection / Unprotect Sheet.
Tip: Please find updated document at Customizing.
- Switch the spreadsheet into design mode by clicking the Design Mode button on the Control Toolbox toolbar. If the Control Toolbox toolbar is not displayed, click Excel menu View / Toolbars / Control Toolbox.
In design mode, Excel Invoice Manager does not react to the click events on the ActiveX objects, allowing you to move, resize, add or delete ActiveX objects in the worksheet.
- Display gridlines and row/column headers by clicking Excel menu Tools / Options and then checking the corresponding options on the View tab.
- The default template has 12 rows on the detail section. To make sure the customized template can be fit into one page, we need to hide several rows. In this example, we will hide 6 rows and leave 6 rows visible / printable.
Note: Please hide appropriate number of rows to make sure there are enough rows leaved on the sheet for you to create all your bills. However, the rows you hide must be the last ones since Excel Invoice Manager validates the form from top to bottom. For example, if you want to hide 3 rows and leave 9 rows on the printable form, then hide row 10, 11, 12.
To do this, firstly we need to move out all the ActiveX controls (Taxable checkboxes, Product-Selection icon buttons) belonging to the last 6 rows. Push the CTRL key, click each of them, release the CTRL key, drag and drop all the 12 controls to an unused area, such as column U.
- Drag your mouse on the row headers of the last 6 rows to select them, right click one of the row headers and choose Hide.
- Push the CTRL key, click to select all the Product-Selection icon buttons on row 1 to row 6. Release the CTRL key, move all the icon buttons to the left of the checkbox controls.
- Drag your mouse to select all the Product ID cells. Push down your left mouse key on the border of the selected area and drag it to an unused area. In this example, we want to be able to view the product id once a product is selected, so we put the Product ID cells in column A.
- Select all the Product Description cells below the Description label. Click the Merge and Center button on the Formatting toolbar to unmerge them.
- Select all cells whose name prefixed with oknProductName_ (below the Description label). Push down your left mouse key on the border of the selected area and drag it to the first column (column F).
- Push your left mouse key on the cell named oknProductName_1 and drag to select all the cells you want to use for the new Product Description cell. Click the Merge and Center button on the Formatting toolbar to merge it, and then click the Align Left button on the same toolbar.
Note: After merging, make sure the cell name "oknProductName_1" is displayed in the name box of the formula bar. If it is not, click Excel menu Insert / Name / Define to point the "oknProductName_1" name to the new merged product description cell.
- Repeat the above step to create (merge) the other 5 product description cells.
- Drag your mouse to select all product description cells. Right click one of them and choose the Format Cells command. Switch to the Alignment tab, check the Wrap Text option. Click OK to close the Format Cells window.
- Now drag your mouse on the row headers to select all 6 rows on the detail section. Right click one of the row headers and choose Row Height. By default, the row height is 20. Now we set it to 40.
- Though all the Taxable checkboxes and Product-Selection icon buttons have their "Move but don't size with cells" options checked, sometimes Excel still fails to move the ActiveX controls when the row height is increased. Drag and drop the ActiveX controls to the proper location manually if you encounter this problem.
Tip: Use the keyboard arrow keys to move the ActiveX controls slightly. To accurately position a control, you can also right-click the control and choose Properties, and then set the Left and Top properties.
- Now the customization work is almost done. All the things left to do is to format the template - set colors, borders, labels, fonts, etc. You can do most of the job with the Format Cells dialog, which can be opened by right-clicking a cell and choose the Format Cells command from the menu.
- Exit design mode and protect the worksheet again.
- Don't forget to print-preview the template by clicking Excel menu File / Print Preview. Make sure the form can be printed on the paper size you choose. For more information on printing, see the topic at Printable invoice forms.
Here is an example of a printed invoice. The PDF invoice looks like it too.
Note: In the default database definition, the size of the Product Description (Product Name) field is 100 characters. This means you can save up to 100 characters in the Product Description field to the backend database.
If this is still not enough for you, with Invoice Manager for Excel (namely Uniform Invoice Software) you can use the advanced field manager.
If you are using Excel Invoice Manager, click here to download the sample invoice template created in this tutorial.
If you are using Invoice Manager for Excel (namely Uniform Invoice Software), please visit template with long product description, in the Sales Invoice Format category.