Skip to main content
office-kit.com main content

Sample Invoice Template: Using Line# as Product IDs

 Share This Page

Sample Invoice Template: Using Line# as Product IDs was revised and published on our new site InvoicingTemplate.com, at Sample Sales Invoice Template: Using Line Number Instead of Item# in the Sales Invoice Template category, and Sample Service Invoice Template: Using Line Number Instead of Item# in the Service Invoice Template category, as both a free invoice template and a template that works with Uniform Invoice Software. Please visit our new site for the updated documents and download links of this template (Sample Invoice Template: Using Line# as Product IDs).

Tip: You can find another tutorial on the same topic: Sample Invoice Template: Replacing the Product IDs with Line Numbers.

There are cases you don't need the product management feature of Excel Invoice Manager, but want to display a Line # number in the Product ID cells of the standard invoice template - the line # must appear automatically when you enter a product description on a line of the invoice body.

The following tutorial shows you how to do it in the standard invoice template included in Excel Invoice Manager.

Note: The Product Report won't work if you customize the template in this way, since product reports are generated based on Product IDs.

  1. Make a backup copy of your invoice template using Windows explorer. This makes sure that you can easily return to the currently working template if any mistake is made during customization.
  2. Open your template.
  3. Unprotect the Invoice worksheet. For information about how to do this in Excel, see Customizing the Invoice Template.
  4. Switch the invoice template into design mode. For information about how to do this in Excel, see Customizing the Invoice Template.
  5. Drag your mouse to select all Product ID cells.
  6. In Excel 2000/XP/2003, click menu Data -> Validation.

    Sample invoice template: Using line number as product id - excel 2003
    Sample invoice template: Using line number as product id -
    Excel 2003 (Click to enlarge)

    Or in Excel 2007/2010, go to the Data tab and choose Data Validation from the Data Tools group.

    Sample invoice template: Using line number as product id - excel 2007
    Sample invoice template: Using line number as product id -
    Excel 2007 (Click to enlarge)

  7. On the Data Validation window, click Clear All. This removes all the data validations applied to the Product ID cells. Click OK to close the Data Validation window.
  8. Click the Product ID cell on the first line of the invoice body to select it. Enter the formula =IF(oknProductName_1="","",1) into the formula bar and press Enter.

    Sample invoice template: Using line number as product id - enter formula
    Enter a formula (Click to enlarge)

  9. Repeat the above step for each line of the invoice body. For example, on the second line you enter =IF(oknProductName_2="","",2) for the Product ID cell; on the third line, you enter =IF(oknProductName_3="","",3) for the Product ID cell. Note the numbers in the formula are replaced with the corresponding line #.
  10. Since the template does not utilize the product management feature of Excel Invoice Manager, you can safely delete all product-selection icon buttons. To delete a button, click it to select it, and then hit the Delete key on your keyboard.
  11. Drag your mouse to select all the Product ID cells, right click one of them and choose Format Cells from the menu.
  12. Go to the Protection tab and check the Locked option.

    Sample invoice template: Using line number as product id - lock formula
    Lock the formula (Click to enlarge)

  13. You may also like to replace the original "Product ID" label with a more meaningful one. For example, "Line #" or "#" are all suitable for this template.

    Sample invoice template: Using line number as product id - enter new label
    Enter the new label (Click to enlarge)

  14. Now you have finished your customization. Exit design mode and protect the template. For information about how to do this in Excel, see Customizing the Invoice Template.

Tip: The free invoice template can also be customized in the same way.

Click the following links to download the template created in this tutorial: