Skip to main content
office-kit.com main content

Sample Invoice Template: Replacing the Product IDs with Line Numbers

 Share This Page

Tip: Click the following link to visit another tutorial on Sample Invoice Template: Using Line# as Product IDs

This document was written for version 2 of Uniform Invoice Software, which was previously named Excel Invoice Manager. For the documents that are specific to Uniform Invoice Software, please download the help document, or install the program that already has the help document included.

Sometimes Product IDs are not essential for invoicing and one would like to remove/hide the Product ID column included in the default/standard invoice template of Excel Invoice Manager, or simply replace the Product ID column with a Serial# column indicating the line numbers. This tutorial tells you the detailed steps for such customization.

Product ID is an important field in Excel Invoice Manager -- it is the only field that uniquely identifies a product or service. For this reason, we don't recommend you to delete the Product ID cells completely. What we will do is to just move the Product ID cells out of the Print_Area.

  1. Backup your invoice template. This ensures that you can restore to a working template in case the template that you are customizing is damaged.
  2. Open the invoice template.
  3. Unprotect the Invoice worksheet. (How?)
  4. Switch to design mode. (How?)
  5. Display gridlines and row/column headers. (How?)
  6. Drag your mouse to select all the Product ID cells.
  7. Push your mouse key on the border of the selected area, drag the selected area to an empty area, say column A.

    Sample invoice template - Move out Product ID cells
    Move out Product ID cells (Click to enlarge)

  8. Push the CTRL key on your keyboard and click the product-selection icon buttons one-by-one, to select all the product-selection icon buttons.

    Sample invoice template - Select all icon buttons
    Select all icon buttons (Click to enlarge)

  9. Click one of the selected icon buttons, and drag it to an empty area, say column B.

    Sample invoice template - Move icon buttons
    Move icon buttons (Click to enlarge)

  10. Replace the label "Product ID" with "Serial #".
  11. Drag your mouse to select the first two cells below the Serial # label. Click the Merge and Center button on the Formatting toolbar.

    Sample invoice template - Merge cells
    Merge cells (Click to enlarge)

  12. Repeat the above step to merge all the cells used by the Product ID cells and icon buttons, row by row.
  13. Push the CTRL key on your keyboard and click the cell 2, 4, 6, 8, 10, 12 (top to bottom) on the newly created Serial # column.
  14. Right-click one of the selected cell and choose Format Cells from the menu.

    Sample invoice template - format cells
    Format cells (Click to enlarge)

  15. On the Patterns tab, choose the silver gray color from the color table, and then click OK.
  16. Drag your mouse to select all the cells on the Serial # column, right click one of the selected cells, and choose Format Cells.
  17. Go to the Border tab, add the left and bottom border. Click OK.

    Sample invoice template - add borders
    Add borders (Click to enlarge)

  18. Click the first cell on the Serial # column, enter the following formula for it:

    =IF(oknProductName_1="","",1)

    This formula tells Excel that if the first Product Description cell is not empty then display "1" in the first Serial # cell.

    Sample invoice template - create a formula
    Create formula (Click to enlarge)

  19. Repeat the above step, enter one formula for each of the other Serial # cell. When entering the formulas, replace the numbers with the corresponding row numbers. For example, for the second Serial # cell you enter:

    =IF(oknProductName_2="","",2)

    And for the third cell, you enter:

    =IF(oknProductName_3="","",3)

  20. Fine tune the invoice template, by adjusting the fonts, colors, alignments of the cells.
  21. Hide gridlines and row/column headers.
  22. Exit design mode.
  23. Protect the Invoice worksheet.
  24. Test.

Click the following link to download the invoice template we created in this tutorial. Click this link to download a free invoice template.