Why to show the word 'FREE' in places of prices

This "Show the word 'FREE' for zero invoice prices" tutorial was written for Excel Invoice Manager. If you are using the updated version of our Invoicing Software, please refer to Gift Invoice.

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

By default the standard 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 empty lines that don't have a product ID and/or product description.

However, instead of leaving the cell blank, there are times when you like to display something for the zero cells, such as a word "FREE", or a symbol "---".

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 a layout.

Tip: To test the setting of zero values, in Excel 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 and later versions, 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.

Steps of customization

Note: The following description are based on an elder version of Excel. But the basic principle and procedure are suitable for newer Excel versions too.

  1. Backup your Excel file. This ensures that you're able to restore to a working version easily in case something goes wrong during the design of the sheet.
  2. Open your template.
  3. Unprotect the spreadsheet. (How?)
  4. Switch to Design Mode. (How?)
  5. Display gridlines and row/column headers. (How?)
  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.

    Move the Unit Price and Line Total columns.

  8. Click the Format Painter button on the Standard toolbar in Excel 2003. If you are running Excel 2007 and later versions, 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 pastes the formats of the Unit Price and Line Total columns.

    Copy format using Excel format painter

  10. Now assign formulas to the Unit Price and Line Total columns.
    Assign formulas to cells
    Line#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.
  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 worksheet.
  15. Save the Excel file.
  16. Test.

Click the following links to download the template created in this tutorial.