Skip to main content
office-kit.com main content

Creating an Invoice Template with Additional Rows

- Document for Excel Invoice Manager

 Share This Page

This version of the document, "Creating an Invoice Template with Additional Rows", was written for Excel Invoice Manager, which was now renamed to Uniform Invoice Software. For the latest version of this document, please download Uniform Invoice Software.

To download free sample invoicing templates, visit free invoice templates with multiple pages.

The default invoice template shipped with Excel Invoice Manager setup program includes 12 rows. This tutorial demonstrates how to add additional rows to create an invoice template that includes more than 12 rows. You can download various pre-customized templates from the online version of this document.

This document is based on the default/standard invoice template included in the Excel Invoice Manager package. The screen shots may vary if you are using other templates.

  1. Backup the invoice template/workbook you want to customize.
  2. Open it in Excel.
  3. Unprotect the Invoice worksheet. For information about how to do this in Excel, see Customizing the Invoice Template.
  4. Display row headers, column headers and gridlines. For information about how to do this in Excel, see Customizing the Invoice Template.
  5. Switch the Invoice worksheet to design mode. For information about how to do this in Excel, see Customizing the Invoice Template.
  6. Push the CTRL key on your keyboard, and click the row-headers of last two rows on the invoice body. This will put the two rows into selected-mode.
  7. Release the CTRL key, right-click any selected row header to display a popup menu, click Copy.

    Create invoice template - copy existing rows
    Copy existing rows (Click to enlarge)

  8. Right-click the row-header of the row below the rows you previously selected. Click the Insert Copied Cells command. This will insert two rows into the invoice body.

    Create invoice template - Insert copied rows
    Insert copied rows (Click to enlarge)

  9. Repeat Step 6 to Step 8 to add more rows to the invoice body.
  10. Push the CTRL key, click and select one or more checkboxes on the invoice body.
  11. Release the CTRL key, right-click any checkbox you selected, click the Copy command on the popup menu.

    Create invoice template - Copy check boxes
    Copy check boxes (Click to enlarge)

  12. Right-click the second cell (column B) of the first row you inserted, click the Paste command. This will add one or more checkboxes to the rows you inserted.
  13. Repeat Step 10 to Step 12 to add checkboxes for all rows you added.

    Create invoice template - Add more check boxes
    Add more check boxes (Click to enlarge)

  14. Push the CTRL key, click and select one or more magnifier icon buttons on the invoice body.
  15. Release the CTRL key, right-click any selected magnifier icon button, and click the Copy command.

    Create invoice template - Copy icon button
    Copy icon button (Click to enlarge)

  16. Right-click the cell on column G of the row you inserted and click the Paste command. This will add one or more magnifier icon buttons to the rows you inserted.

    Create invoice template - Paste icon button
    Paste icon button (Click to enlarge)

  17. Repeat Step 14 to Step 16; add magnifier icon buttons for all rows you inserted.
  18. Push the left mouse button, click and drag from column header B to F to select these columns.
  19. Right-click any column header you selected, click the Unhide command on the popup menu. This will display two hidden columns, column C and column D.

    Create invoice template - Display hidden columns
    Display hidden columns (Click to enlarge)

  20. Name the cells as the following table. For the first row added to the invoice body, add a postfix "_13" to its cell names; For the second row you added, add a postfix "_14"; and so on.
    ColumnCell Name
    CoknTaxable_13
    DoknCost_13
    F (Product ID)oknProductID_13
    H-I-J (Product Description)oknProductName_13
    K-L (Quantity)oknQuantity_13
    M (Price)oknPrice_13
    N (Line Total)oknLineTotal_13

    Create invoice template - Name the cells
    Name the cells (Click to enlarge)

  21. Link checkbox to cell: Right-click the checkbox on the first row you inserted. Click the Properties command to display its Properties window.

    Create invoice template - Set checkbox property
    Set checkbox property (Click to enlarge)

  22. Scroll down the property list and find the LinkedCell property. Change its value to oknTaxable_13. Press ENTER. Close the Properties window.

    Create invoice template - Link checkbox to cell
    Link checkbox to cell (Click to enlarge)

  23. Repeat Step 21 to Step 22 to link checkboxes to appropriate cells. For example, link the checkbox on the second row you inserted to oknTaxable_14, link the third row you inserted to oknTaxable_15.
  24. Right-click the magnifier icon button on the first row you inserted. Select Properties from the popup menu to display its Properties window.
  25. The first property is (name). Change its value to oknCboPID_13 and press ENTER.

    Create invoice template - Name a button
    Name a button (Click to enlarge)

  26. Click the magnifier icon button on the second row you inserted (without closing the Properties window). Now the Properties window displays information about the button you just selected. Change its (name) value to oknCboPID_14.
  27. Repeat the above steps to name all icon buttons which you added.
  28. Click the cell on the SUBTOTAL row / column D. This cell calculates the sum of the taxable line-totals. As you can see from the formula bar, the default formula is "=SUM(oknTaxable_1:oknTaxable_12*oknLineTotal_1:oknLineTotal_12)" (This is an array formula). Now you need to replace the oknTaxable_12 and oknLineTotal_12 with new cell names - the cell names on the last row you inserted into invoice body. For example, if you added 4 rows, the new formula will be "=SUM(oknTaxable_1:oknTaxable_16*oknLineTotal_1:oknLineTotal_16)"

    Note: Press CTRL+SHIFT+ENTER when you finish editing the formula, as it is an array formula.

    Create invoice template - Create an array formula
    Create an array formula (Click to enlarge)

  29. Click the Line Total cell of the first row you inserted. This cell was named as oknLineTotal_13 in previous steps. Replace its formula with: =ROUND(oknQuantity_13*oknPrice_13,2).

    Create invoice template - Create a Linetotal formula
    Create a Linetotal formula (Click to enlarge)

  30. Repeat the above step to set an appropriate formula for all newly added Line Total cells. For example, for the second row you added, its formula will be "=ROUND(oknQuantity_14*oknPrice_14,2)"; for the third row you added, its formula will be "=ROUND(oknQuantity_15*oknPrice_15,2)".
  31. Click the cell next to the SUBTOTAL label. By default, its formula is "=SUM(oknLineTotal_1:oknLineTotal_12)". This is the sum of all line-total values. Now replace the name oknLineTotal_12 with the last Line Total cell name. For example, if you added 4 rows, the new formula will be "=SUM(oknLineTotal_1:oknLineTotal_16)".

    Create invoice template - Create the Subtotal formula
    Create the Subtotal formula (Click to enlarge)

  32. Format the newly inserted rows so that their appearance is consistent with the look of the original invoice. For example, format cell patterns and borders, etc.
  33. Push the CTRL key and click the Headers of column C and column D.
  34. Release the CTRL key, right-click any selected column header, and click the Hide command on the popup menu to hide these two columns.
  35. Hide row / column headers and gridlines.
  36. Click the Design Mode button again to exit design mode.
  37. Protect the Invoice worksheet. For information about how to do this in Excel, see Customizing the Invoice Template.
  38. Save the customized invoice template / workbook.
  39. Test the invoice template / workbook.

 

Please look into the data file folder of Excel Invoice Manager on your computer. Here you will find a file named MoreRows.xls which has been created and is provided to demonstrate this application.

Click the following link to download various pre-customized invoice templates: