You are here: HOME  >>>  Excel Invoice Manager

Tutorial: Adding Extra Rows to Invoice Body

     - How to make an invoice that has more than 12 rows

Starting from Version 2.1 Build 1007, Excel Invoice Manager allows you to add more rows to the invoice body. The following information gives you a detailed demonstration of how to add extra rows.

This document is based on the 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.

    8. Adding more rows to invoice body: click to enlarge
    (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.

    9. Adding more rows to invoice body: click to enlarge
    (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.

    12. Adding more rows to invoice body: click to enlarge
    (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.

    14. Adding more rows to invoice body: click to enlarge
    (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.

    16. Adding more rows to invoice body: click to enlarge
    (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.

    17. Adding more rows to invoice body: click to enlarge
    (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.

    20. Adding more rows to invoice body: click to enlarge
    (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.
    21. Column Cell Name
    C oknTaxable_13
    D oknCost_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

    Adding more rows to invoice body: click to enlarge
    (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.

    22. Adding more rows to invoice body: click to enlarge
    (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.

    23. Adding more rows to invoice body: click to enlarge
    (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.

    26. Adding more rows to invoice body: click to enlarge
    (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.

    29. Adding more rows to invoice body: click to enlarge
    (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).

    30. Adding more rows to invoice body: click to enlarge
    (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)".

    32. Adding more rows to invoice body: click to enlarge
    (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:
Excel Invoice Manager Free Download and Try (4.17 MB)
Learn More Excel Invoice Manager Home What's New Difference between Editions Quick Start Introduction Before Using Invoice Template Working with Excel 2007 Installing / Upgrading Settings Workbook Taxes Misc Data Interface Database In-Cell Lookup Customers Creating Customer Editing Customer Customer Detail Products Creating Product Editing Product Product Detail Invoices Creating Invoice Editing Invoice Invoice Detail Payments Adding Payment Editing Payment Payment Detail Reports Generating & Printing Reports Setting Visible Columns Extracting Reports Common Tasks Using the Search Bar Using Data List Windows Importing Data into Excel Invoice Manager Networking Working with Form Properties File Free Invoice Form with Support for Incomplete Lines on Invoice Body Using Credit Note or Credit Memo Custom Fields Custom Fields - Overview Custom Invoice Fields - Tutorial Customizing the Invoice Template Customizing the Invoice Template Tutorial: Adding Extra Rows to Invoice Body Tutorial: Adding a Notes Field to Your Invoice Template Tutorial: Adding or Removing the "Same As Bill To" Button Tutorial: Adding a Clear button to the Report Worksheets Tutorial: Displaying Summary Information on Customer Statement More Topics Registration and Services Reference
Online-Only Articles Billing Software Powered by Excel Buttons in the Template Don't Work Sample Invoice Template: Using handwriting Signature Sample Invoice Template: Using Drop-Down List Sample Invoice Template: Long Product Descriptions Sample Invoice Template: Using Line# as Product IDs How to: Password-Protecting an Invoice Template How to: Transferring Excel Invoice Manager to a New System How to: Removing Auto-created Macros from Your Invoice Template Creating a Packing Slip Template Modifying Standard Fields with Database Designer How Do I Replace the Product IDs with Line Numbers Invoice Template with Support for Product-Type Specific Tax Rates Editing Paid Invoices
Home Contact Us Privacy Link to Us
copyright © Office-Kit.com, all rights reserved