You are here: HOME  >>>  Excel Invoice Manager

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

Online-Only Article

Online-only articles are documents that are not included in the help file of the current version of Excel Invoice Manager. However, they provide useful information and may be included in the next version of Excel Invoice Manager.

Related Links:

Download the billing software powered by Excel Now!

A quick tutorial about the software

What's new in the latest version

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, 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 column Line 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:
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 Working with Multiple Companies or Currencies Tutorial: Adding New Fields to Reports Sample Invoice Template: Displaying 'FREE' for Zero Values Customizing Header and Footer of Printed Invoices
Home Contact Us Privacy Link to Us
copyright © Office-Kit.com, all rights reserved