Friday, October 1, 2010

Excel 2007: Display Dashes Instead of Zeros

Do you have a spreadsheet that contains a lot of zeros? It's not always easy to read the other numbers when you do. You can accomplish this with a custom number format.
  1. Select the cells where you want to apply the format.
  2. On the Home tab in the Number group, click the dialog box launcher to display the Format Cells dialog.
  3. On the Number tab select Custom in the Category list.
    Excel number formats can contain up to four sections of code separated by semicolons. The first section applies to positive numbers, the second to negative numbers, the third to zeros, and the fourth to text. When formatting, the third and fourth are optional.
  4. To specify a format for zeros, you need to add (or change) the third section of the number format code. If there is no format for zeros, add a semi-colon for the third section and then type a dash(es). This tells Excel to display a your defined format in any cell with a value of zero.
Keep in mind that a custom number format is stored for future use in the current workbook only.

1 comment: