Tutorial for Adding New Line to Template

This sample shows the detailed steps required when adding new lines to an existing template, by using the default invoice template shipped with Invoice Manager for Excel as the starting point.

Things to consider when adding new lines to a template

The standard invoice template shipped with Invoice Manager for Excel has 12 lines (or 12 rows) on the detailed section. But the number of lines on the detail section isn't necessary to be 12.

For example, Invoice Template with Long Product Description includes only 6 lines, which enables you to enter long text into the "Description" field. On multiple-page invoice templates, you can find samples that has 36 lines (two-page), 60 lines (three-page), 84 lines (four-page).

Reducing the number of lines could be archived by hiding some bottom lines of an existing invoice design, but increase the number of lines is not as easy as reducing lines.

First, all cells that need to be saved to database must be correctly named, including those put into hidden columns. Secondly, you have to modify the Excel formulas that calculate totals and taxes. Finally, if you find many lines, you have to adjust the page breaks if the template should be printed using multiple pages.

In the following step by step tutorial, we'll add only one new line. So it does not involve the adjustment of page layout and printing. Please refer to printable invoice templates for more samples on this topic.

Steps

This step by step tutorial is based on Windows 10 and Excel 2016.

1. Backup your invoice template.
2. Open the invoice template.
3. Switch to design mode by pushing down the "Design Mode" button on the "Invoice" ribbon tab. This shows Excel gridlines and row / column headings.
4. Point your mouse to the left-border of the heading cell of column C. Drag it to right to unhide column B. Repeat this step to unhide column A.
5. Right-click the row 34 heading cell and choose "Insert". This inserts a new row.

   

6. Drag your mouse to select all cells on row 32, ranging $A$32:$K$32. Click the "Format Painter" button in the "Clipboard" group of the "Home" ribbon tab. This copies the formats of the selected cells on row 32.

   

7. Click the first cell of the newly inserted row i.e. $A$34. This applies the format of row 32 to row 34, including background colors, merged cells.
8. Name the cells of the new row according to the name pattern of the above row. For example, $A$33 is named "oknTaxable_12", so $A$34 should be named "oknTaxable_13"; $B$33 is named "oknCost_12", so $B$34 should be named "oknCost_13".

   Please refer to How to name an Excel cell / range.

   The Excel cell names map to database file name. So for example in the name "oknProductID_13", "okn" is the prefix that must be added to all names that indicate cells saved to / loaded from database; "ProductID" is the database field name in the "Invoice Body" database table; "13" is line number.

9. Right-click the "Taxable" checkbox on row 33 and choose "Copy" from the shortcut menu.

   

10. Click to select the cell where you want to put the new "Taxable" checkbox, i.e. $E$34. Right-click the cell, choose "Paste" from the shortcut menu. This creates a new "Taxable" checkbox by pasting the one copied from $E$33.
11. On Excel formula bar, name this new checkbox "oknWidget_taxablecheck12", assign it this formula to bind the checkbox to the cell named "oknTaxable_13".

    =oknTaxable_13
    

    

12. Click to select the "oknLineTotalTaxable" cell located at $B$35. This cell has an array formula like this:

    {=SUM(oknTaxable_1:oknTaxable_12*oknLineTotal_1:oknLineTotal_12)}
    

    On the formula bar click to edit the formula, change the numbers from "12" to 13", and then press keyboard key CTRL + SHIFT + ENTER to create the array formula. The result should look like this:

    {=SUM(oknTaxable_1:oknTaxable_13*oknLineTotal_1:oknLineTotal_13)}
    

13. Click to select the "oknLinetotal_13" cell located at $K$34. Modify the formula so it looks like this:

    =ROUND(oknQuantity_13*oknPrice_13,2)
    

14. Click to select the "oknSubTotal" cell located at $K$35, modify the formula to be:

    =SUM(oknLineTotal_1:oknLineTotal_13)
    

15. Drag your mouse to select all the printable cells on the newly inserted line, ranging $D$34:$K$34. Right-click one of the selected cells, choose "Format Cells". On the "Format Cells" dialog box, go to "Borders" tab. Set the borders as needed.

    

16. Fine tune borders and colors as needed.
17. Hide column A and B.
18. Exit design mode by clicking the "Design Mode" button on the "Invoice" ribbon tab again.
19. Test the template.

Since we add only one new line to the template, it is still printable one a single A4 or letter paper. In practical situations you may want to add several lines. In this case, you may need to click Excel menu "File" and then "Print" to verify how many pages needed to print the form.

Adding a new line is not as easy as hiding a line. Fortunately you can download ready-to-use templates at templates with multiple pages without going through all these steps.