Sample Invoice Template: Displaying 'FREE' or '---' for Zero Values
This tutorial creates a sample invoice template that shows a word 'FREE' and a symbol '---' on theand 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 withdoes 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 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-> , go to the tab, check or uncheck the " " option under the . In Excel 2007, you can find the same option by following the steps: click button -> -> , within the group check or uncheck .
- Backup your invoice template. This ensures that you can restore to a working invoice template easily in case the following customization damages the template.
- Open your invoice template.
- Unprotect the Customizing the Invoice Template - Protection for information on how to do it.) worksheet. (See
- Switch to Design Mode. (See Customizing the Invoice Template - Design Mode for information on how to do it.)
- Display gridlines and row/column headers. (See Customizing the Invoice Template - Gridlines and Row/Column Headers for information on how to do it.)
- Push the CTRL key on your keyboard; drag your mouse to select all the cells in the and columns.
- Drag the border of the selected area, to move it outside the Print_Area.
- Make sure the area you moved out is still selected; click the button on the toolbar in Excel 2000/XP/2003. If you are running Excel 2007/2010, the command can be found in the group of the ribbon tab.
- Click the first cell on the
- Now assign formulas to the Unit Price and Line Total columns.
Cell # Unit Price column Line Total column 1 =IF(oknProductName_1="","",
... ... ... 12 =IF(oknProductName_12="","",
- All the cells must be filled with a formula. That means, for example, on the column you need to enter 12 formulas, one for each cell.
- The formulas on the column choose a display value according the content of the corresponding product name (product description) cell, by utilizing 2 commands: If no content is entered into the product description cell, the 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 column work in the similar way. The difference is that it shows a word " " if the line total is zero.
- For more information about how the formulas work, refer to your Excel documents.
- If Excel displays an " " 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 ; go to the tab, check the option.
- Hide gridlines and row/column headers.
- Exit design mode.
- Protect the worksheet.
- Save the invoice template.
Click the following links to download the invoice template created in this tutorial: