 |
How Do I Replace the Product IDs with Line Numbers
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.
- Backup your invoice template. This ensures that you can restore to a working template in case the template that you are customizing is damaged.
- Open the invoice template.
- Unprotect the Invoice worksheet. (How?)
- Switch to design mode. (How?)
- Display gridlines and row/column headers. (How?)
- Drag your mouse to select all the Product ID cells.
- Push your mouse key on the border of the selected area, drag the selected area to an empty area, say column A.
(Click to enlarge)
- 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.
(Click to enlarge)
- Click one of the selected icon buttons, and drag it to an empty area, say column B.
(Click to enlarge)
- Replace the label "Product ID" with "Serial #".
- Drag your mouse to select the first two cells below the Serial # label. Click the
Merge and Center button on the Formatting toolbar.
(Click to enlarge)
- Repeat the above step to merge all the cells used by the Product ID cells and icon buttons, row by row.
- 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.
- Right-click one of the selected cell and choose Format Cells from the menu.
(Click to enlarge)
- On the Patterns tab, choose the silver gray color from the color table, and then
click OK.
- Drag your mouse to select all the cells on the Serial # column, right click one of the selected cells,
and choose Format Cells.
- Go to the Border tab, add the left and bottom border. Click OK.
(Click to enlarge)
- 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.
(Click to enlarge)
- 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)
- Fine tune the invoice template, by adjusting the fonts, colors, alignments of the cells.
- Hide gridlines and row/column headers.
- Exit design mode.
- Protect the Invoice worksheet.
- Test.
Click the following link to download the invoice template we created in this tutorial. Click this link to download a free invoice template.
|
