BoostExcel main content


Customizing invoicing templates means creating your own templates that can interact with Invoice Manager for Excel and connect to databases to store and retrieve data. All templates here on are created based on these rules.

Please read this document carefully before customizing your template.

Create New Invoice Templates

An invoice template uses a number of hidden properties and cells to identify itself as a valid invoice template so that the Invoice Manager for Excel can and will interact with it.

Because of this, generally it is not recommended to create a template or workbook from scratch using Microsoft Excel. It's better to create an invoice template based on an existing valid invoice template. To do this, copy an existing invoice template using your Windows Explorer, and customize that copy to create your own invoice workbook.

However if you still want to integrate your existing template with Invoice Manager for Excel, or create a new format from scratch, the steps are detailed with the sample at c4056 Simple Sample - Building & Remodeling Invoice.

Design Mode

An invoice template has two states: design mode and running mode. In design mode, Invoice Manager for Excel stops interacting with the invoice template, so that you can move, add, or delete controls or cells.

To switch to design mode, click the Design Mode button on the Invoice ribbon tab. Click this button again to exit design mode.


The protection of the "Invoice" worksheet prevents accidental modifications to layouts, formulas and other form designs. If you have Invoice Manager for Excel installed, it could even display a warning message when you close a template without protecting the worksheet. However for the purpose of this customizing a template, we should firstly unprotect the worksheet.

Tip : If you have Invoice Manager for Excel installed, the "Design Mode" button unprotects an sheet automatically.

By default, the Invoice worksheet is protected. You need to unprotect it for most of the customization work. To unprotect the worksheet, in Excel 2007/2010/2013 click the Unprotect Sheet button in the Changes group of the Review tab.

It is strongly recommended that you protect the worksheet again after your customization. To protect a sheet, just click the same button again.

Locking/Unlocking Cells

If a worksheet is protected, locked cells and those cells that contain formulas are not manually editable. To lock or unlock a cell:

  1. Unprotect the worksheet if it is already protected.
  2. Right-click the cell, choose Format Cells.
  3. Click the Protection tab.
  4. Check or uncheck the Locked option.

Gridlines and Row/Column Headers

When customizing a workbook, it is very helpful to display gridlines and row/column headers. To show gridlines and row/column headers in Excel 2003:

  1. Click and activate the worksheet you want to display gridlines and row/column headers for.
  2. On the Tools menu, click Options, and then click the View tab.
  3. Under Window options, check the Gridlines and Row & Column Headers check boxes.

After your customization, go back to the Tools menu, Options, View tab. Under Window options, REMOVE the checks you previously placed by the two check boxes to hide gridlines and row/column headers. This will restore the appearance of your workbook.

To show/hide gridlines and row/column headers in Excel 2007/2010/2013:

  1. Click and activate the worksheet you want to display gridlines and row/column headers for.
  2. Go to the View tab of the Ribbon.
  3. Click Show/Hide, and then check or uncheck the Gridlines and Headings options.


All objects in an invoice workbook, including ActiveX controls, drawing objects and cells, are identified by names.

To distinguish from other names, all names used by Invoice Manager for Excel are prefixed with okn ('s Name).

Names defined this way by defalt are available to any sheet. For example, if the name oknID refers to the range A20:A30 on the first worksheet in a workbook, you can use the name oknID on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.

To name a drawing object or cell:

  1. Click and select the cell or drawing object.
  2. Click the Name box located at the left end of the formula bar.

    Formula bar and name box

  3. Type the name in the Name box.
  4. Press ENTER.

To name an ActiveX control object:

  1. Switch your workbook to design mode.
  2. Right click the ActiveX object.
  3. Select Properties from the menu.
  4. In the Properties window, enter the name in the Name field.

Note that Excel does not allow you to reuse an existing name in this way. For example, if a cell has already been named "oknTaxType", and you click and select an empty cell then enter the name "oknType" into the name box, Excel activates the cell with the name "oknTaxType", instead of naming the empty cell "oknTaxType".

To name a new (empty) cell with an existing name, you have to firstly delete the exiting cell name, and then use this name to name the new cell. To delete an existing cell name, in Excel 2003 click Excel menu Insert > Name > Define to open the "Define Name" dialog box; in Excel 2007, 2010, 2013 and 2016, the Name Manager can be launched by going to the "Formulas" ribbon tab, "Defined Names" group.

Excel Name Manager

ActiveX Controls

Note: Starting from Invoice Manager for Excel version 5, all on-sheet buttons are created as shape objects. Don't use ActiveX objects unless you're using Invoice Manager for Excel prior to version 5.

Microsoft Excel has two types of controls. ActiveX controls and form controls. Forms controls are compatible with earlier versions of Excel, beginning with Excel version 5.0, and can be used on XLM macro sheets.

To add an ActiveX control:

  1. Switch your workbook to design mode.
  2. In Excel 2007 - 2013, go to the Developer tab, click Insert in the Controls group, and then choose the ActiveX control you want to add.
  3. Click on the worksheet at the location where you want to place the control.
  4. Drag the control to the size you want.
  5. Right click the control and select Properties from the menu.
  6. Enter the name, caption, and other properties if needed.
  7. Close the Properties window.
  8. To quit design mode and enable the ActiveX control, click Exit Design Mode on the Control Toolbox toolbar if you are running Excel 2000 - 2003; or click the Design Mode button in the Controls group of the Developer tab if you are running Excel 2007/2010.


If you like the invoices and receipts created by, you may find that some Excel shapes and Smart Art objects easily dress up a business document.

Simple Sample - Using Shape Object provides a basic example on using shapes.

Here is how to use Excel shapes or SmartArt objects in your own template. In Microsoft Excel, you can add shapes, such as boxes, circles, and arrows, to your documents, email messages, slide shows, and spreadsheets. To add a shape, click Insert, click Shapes, select a shape, and then click and drag to draw the shape. After you add one or more shapes, you can add text, bullets, numbering, and Quick Styles to them. To add a shape:

  1. Open your Excel template. Unprotect spreadsheet.
  2. On the Insert tab, in the Illustrations group, click Shapes.
  3. Click the shape you want, click anywhere in the workbook, and then drag to place the shape.
  4. To create a perfect square or circle (or constrain the dimensions of other shapes), press and hold Shift while you drag.

Note the additional shapes are removed when executing the "Extract/Email" command. To prevent this, name the object with "oknWidget" prefix.



If a worksheet has defined a PRINT_AREA name and it refers to a range of cells, Excel will print the specified area instead of the whole sheet when you click the Print command on the Excel menu. (See our template gallery at printable invoice template)

In the invoice workbook, each worksheet contains a PRINT_AREA name. You can view what the name refers to by selecting PRINT_AREA from the Name box at the left end of the formula bar. If you want to redefine the PRINT_AREA name, follow the steps below:

  1. Select the area you want to print.
  2. In Excel 2007/2010/2013, switch to the Page Layout tab, click Print Area in the Page Setup group, and then choose Set Print Area.

Printing ActiveX Objects and Drawing Objects

By default, all objects added to a worksheet are printable. You may want to exclude some of them from printing, such as the Customer Selection button on the Invoice worksheet (seen in the default invoice template shipped with Invoice Manager for Excel version 3 and earlier versions).

To exclude a Drawing Object from printing in Excel 2007/2010/2013:

  1. Right click the drawing object, select Size and Properties....
  2. Go to the Properties tab.
  3. Uncheck the Print Object option.

To exclude an ActiveX control from printing:

  1. Switch the workbook to design mode.
  2. Right click the ActiveX control, and select Properties from the menu.
  3. Scroll down the properties list and find the PrintObject property.
  4. Change the PrintObject property to False.

Moving Cells/Controls

Since all the cells/fields and controls are identified by names, you can easily redesign the layout by moving cells/fields/controls. For example, if you don´t want to show the PAID, TOTAL DUE cells/fields on your printed invoice, you can move them out of the Print_Area:

  1. Unprotect the Invoice worksheet.
  2. Drag your mouse to select all the cells/fields you want to move.
  3. Push your mouse key on the border of the selected area; drag it to the new location.
  4. Once finished, protect the Invoice worksheet.
  5. Save the template.

To move a control:

  1. Unprotect the Invoice worksheet.
  2. Switch to design mode.
  3. Click and drag the control to the new location you like.
  4. Once finished, exit design mode.
  5. Protect the Invoice worksheet.
  6. Save the template.

Extracting ActiveX Controls and Drawing Objects

To make the extracted worksheet as clean as possible, all ActiveX controls and drawing objects will be removed from the extracted worksheet. If you want to keep an ActiveX control or a drawing object, add a prefix oknUser_ or oknWidget_ to its name. For example, a logo image could be named oknWidget_logo, instructing Invoice Manager for Excel to keep the logo image on the extracted document.

Resizing columns and rows

Resizing columns is easy in Microsoft Excel. For example, let's say you want to change the width of column A.

  • Make sure the template is unprotected.
  • Place the mouse pointer on the line between columns A and B in the column header. The pointer will change to a double - headed arrow.
  • Click with the left mouse button and drag the double - headed arrow to the right to widen column A or to the left to make it narrower.

See Microsoft video: Resize rows and columns.

Most templates on are printable / fit into one page horizontally. Expanding a column width may break this. To fix this you may need to narrow other columns.

Color schema and palette

Most template here on adopt the new theme-based color scheme. To change a color theme, follow the steps below.

  • Push down the "Design Mode" button on the "Invoice" ribbon tab.
  • Go to Excel "Page Layout" ribbon tab.
  • In the "Themes" group, pull down "Themes" to choose a new theme (this changes colors, fonts and effects), or pull down "colors" to use a new group of colors.
  • To create a new theme color, pull down "Colors" and choose "Customize Colors".
  • Once done, exit design mode by clicking the "Design Mode" button on the "Invoice" ribbon tab again.
  • Save the template.

If you are using an elder template that still uses palette colors, to replace a color with another one you have to adjust the color palette. To do this follow the steps below.

  • Push down the "Design Mode" button on the "Invoice" ribbon tab.
  • Click Excel menu "File", and then click "Options".
  • Click "Save".
  • Next to "Choose what colors will be seen in previous versions of Excel", click "Colors".
  • Click the color that you want to change - for example, the fourth on the last line, and then click "Modify".
  • Specify the new color. Click "OK" to close the "Colors" dialog box.
  • Click "OK" to close the "Colors" dialog box. Click "OK" again to close Excel "Options" dialog box.
  • Exit design mode by clicking the "Design Mode" button on the "Invoice" ribbon tab again.
  • Save the template.

Currency symbols

Invoice Manager for Excel itself is currency-neutral which means, with the default database configurations, it does not record currency symbols in the database, and you can change your template to show any currency symbol you like.

Most templates here on does not show currency symbols. To display a currency symbol, follow the steps below.

  • Switch to design mode by pushing down the "Design Mode" button on the "Invoice" worksheet.
  • Select the cells (fields) for which you want to modify the currency symbol, by dragging your mouse to select multiple cells. You can also push CTRL key on keyboard and click each cell one by one.
  • Right-click one of the selected cell and choose "Format Cells".
  • From the "Number" tab, you can choose either "Currency" or "Accounting" from the Category list. Note that currency formats are used for general monetary values, use accounting formats to align decimal points in a column.
  • Once you have done setting currency symbols, exit design mode by clicking the "Design Mode" button again.
  • Save the template.

For living / practical samples, visit Simple Sample - Using Currency Symbol.

Inserting a new column

In our day to day design work of invoice forms, it is noticed that inserting a new column to the printable form is not as easy as inserting a new row. This is because the horizontal space of the form is limited, and inserting a new column usually means rearranging / adjusting existing columns. On the contrast, inserting a new row usually does not expanding the one-page template to two pages.

To adjust the existing columns to leave space for a new column or columns inside the printable invoice form, we first unmerge the existing column, such as the description column. Then select proper cells and execute the "Merge and Center" command again to create a less-wider "Description" column.

Now you have a new / empty column created by narrowing the "Description" column. Name the cells on the empty column properly to map them to a database field.

A step by step detailed example could be found at Simple Sample - Adding Discount Column.

Adding 'Next Invoice#' button

To add a "Next Invoice#" button to your invoice form:

  1. Open the invoice template as usual.
  2. Push down the Design Mode button on the "Invoice" ribbon tab.
  3. On the "Invoice" worksheet, right-click any button to make it selected, push down CTRL + C on keyboard to copy it, push down CTRL + P on keyboard to paste it.
  4. Select the new button you created (pasted), name it "oknCmdGetNextInvoiceID" by entering the name into the name box at the left-side of the formula bar.
  5. Change the button text to "Get Next Invoice#".
  6. Move the button a proper location, usually near the "Invoice#" cell.
  7. Exit design mode by clicking the button on the ribbon again.
  8. Now if you click the oknCmdGetNextInvoiceID button, it retrieves the next invoice#.

Note that if you click this button, the newly generated invoice# is seen as used. So if you generate a new invoice# and later discard it (for example close the invoice template without saving it), there might be incontinuity with the invoice numbers that you save to database.