Why to show the word 'FREE' in places of prices
This tutorial creates a sample invoice format that shows the word 'FREE' and a symbol '---' on the and 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 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 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/ , go to the tab, check or uncheck the " " option under the . In Excel 2007 and later versions, you can find the same option by following the steps: click button / / , within the group check or uncheck .
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.
- 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.
- Open your template.
- Unprotect the spreadsheet. (How?)
- Switch to Design Mode. (How?)
- Display gridlines and row/column headers. (How?)
- 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.
- Click the button on the toolbar in Excel 2003. If you are running Excel 2007 and later versions, 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.
Assign formulas to cells Line# Unit Price column Line Total column 1 2 ... ... ... 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.
- 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 Excel file.
Click the following links to download the template created in this tutorial.