Skip to main content
office-kit.com main content

Sample Invoice Template: Displaying 'FREE' or '---' for Zero Values

- Document for Excel Invoice Manager

 Share This Page

This version of the document, "Sample Invoice Template: Displaying 'FREE' or '---' for Zero Values", 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.

This tutorial creates a sample invoice template that shows a word 'FREE' and a symbol '---' on the Line Total and Unit Price columns when the values are zero. The customized invoice template can be downloaded from the bottom of this page.

By default the invoice form in the standard invoice template shipped with Excel Invoice Manager does not display zero values. This is necessary because if you choose to display zero value, all the line totals calculated by Price * Quantity, will show 0.00 or something similar, including those lines that don't have a product ID and/or product description.

However there are times when you like to display something for the zero cells, such as a word "FREE", or a symbol "---", instead of leaving the cell blank. For example, you may choose to list the gifts on the invoices you send to a customer. The following tutorial describes the steps required for creating such an invoice template.

Tip: To test the setting of zero values, in Excel 2000/XP/2003, click Excel menu Tools -> Options, go to the View tab, check or uncheck the "Zero values" option under the Window options group. In Excel 2007, you can find the same option by following the steps: click Office button -> Excel options -> Advanced, within the Display options for this worksheet group check or uncheck Show a zero in cells that have zero value.

  1. Backup your invoice template. This ensures that you can restore to a working invoice template easily in case the following customization damages the template.
  2. Open your invoice template.
  3. Unprotect the Invoice worksheet. (See Customizing the Invoice Template - Protection for information on how to do it.)
  4. Switch to Design Mode. (See Customizing the Invoice Template - Design Mode for information on how to do it.)
  5. Display gridlines and row/column headers. (See Customizing the Invoice Template - Gridlines and Row/Column Headers for information on how to do it.)
  6. Push the CTRL key on your keyboard; drag your mouse to select all the cells in the Unit Price and Line Total columns.
  7. Drag the border of the selected area, to move it outside the Print_Area.

    sample invoice template that shows FREE for zero cells - move existing cells.

  8. Make sure the area you moved out is still selected; click the Format Painter button on the Standard toolbar in Excel 2000/XP/2003. If you are running Excel 2007/2010, the Format Painter command can be found in the Clipboard group of the Home ribbon tab.
  9. Click the first cell on the Unit Price column. This restores the formats of the Unit Price and Line Total columns.

    sample invoice template that shows FREE for zero cells - formatting cells.

  10. Now assign formulas to the Unit Price and Line Total columns.

    Cell #Unit Price columnLine Total column
    1=IF(oknProductName_1="","",
    IF(oknPrice_1=0,"---",oknPrice_1))
    =IF(oknProductName_1="","",
    IF(oknLineTotal_1=0,"FREE",oknLineTotal_1))
    2=IF(oknProductName_2="","",
    IF(oknPrice_2=0,"---",oknPrice_2))
    =IF(oknProductName_2="","",
    IF(oknLineTotal_2=0,"FREE",oknLineTotal_2))
    .........
    12=IF(oknProductName_12="","",
    IF(oknPrice_12=0,"---",
    oknPrice_12))
    =IF(oknProductName_12="","",
    IF(oknLineTotal_12=0,"FREE",
    oknLineTotal_12))

    Note:

    • All the cells must be filled with a formula. That means, for example, on the Unit Price column you need to enter 12 formulas, one for each cell.
    • The formulas on the Unit Price column choose a display value according the content of the corresponding product name (product description) cell, by utilizing 2 IF commands: If no content is entered into the product description cell, the unit price cell shows a blank string; otherwise, the unit price cell shows the actual price value, or a symbol "---" if the price is zero.
    • The formulas on the Line Total column work in the similar way. The difference is that it shows a word "FREE" if the line total is zero.
    • For more information about how the formulas work, refer to your Excel documents.
  11. If Excel displays an "Unprotected Formula" flag on the unit price cells, follow the steps to remove the flag: drag your mouse to select all those cells, right-click one of the cell, choose Format Cells; go to the Protection tab, check the Locked option.
  12. Hide gridlines and row/column headers.
  13. Exit design mode.
  14. Protect the Invoice worksheet.
  15. Save the invoice template.
  16. Test.

Click the following links to download the invoice template created in this tutorial: