Display Dashes (-) Instead Of Zeros (0) in MS Excel Worksheet with Example.
Sometimes we may want our report to display dashes instead of zeros, especially those Excel spreadsheets that contain a lot of zeros.
Sometimes we may want our report to display dashes instead of zeros, especially those Excel spreadsheets that contain a lot of zeros.
 |
| Display zeros as dashes in Excel 2007 |
We can do this with a simple change to our current number format.
To do this simple change follow the given below steps:-
1) Select the cells where We want to apply the format;
2) On the Home tab in the Number group, click the dropdown and select More Number Formats... This will display the Format Cells dialog;
3) On the Number tab select Custom in the Category list. If all of the cells We have selected have the same number format, that format will be displayed in the Type field (e.g. #,##0;-#,##0)
Excel number formats can contain up to 4 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. So to display dashes instead of zeros, we have to edit the third section (zeros).
By default, most number formats consist of only two parts: a format code for positive numbers and a format code for negative numbers. When there is no code defined for zeros, the positive number format is applied for zero value cells.
4) To specify a format for zeros other than the default format, we must add (or change) the third section of the number format code. If there is no format for zeros we need to add a semi-colon for the third section and then type a dash. This tells Excel to display a dash in any cell with a value of zero. If We leave the third section (i.e. to the right of the 2nd semi-colon), instead of displaying zeros, the zero-value cells will be blank.
To do this simple change follow the given below steps:-
1) Select the cells where We want to apply the format;
2) On the Home tab in the Number group, click the dropdown and select More Number Formats... This will display the Format Cells dialog;
3) On the Number tab select Custom in the Category list. If all of the cells We have selected have the same number format, that format will be displayed in the Type field (e.g. #,##0;-#,##0)
Excel number formats can contain up to 4 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. So to display dashes instead of zeros, we have to edit the third section (zeros).
By default, most number formats consist of only two parts: a format code for positive numbers and a format code for negative numbers. When there is no code defined for zeros, the positive number format is applied for zero value cells.
4) To specify a format for zeros other than the default format, we must add (or change) the third section of the number format code. If there is no format for zeros we need to add a semi-colon for the third section and then type a dash. This tells Excel to display a dash in any cell with a value of zero. If We leave the third section (i.e. to the right of the 2nd semi-colon), instead of displaying zeros, the zero-value cells will be blank.
 |
Display zeros as dashes in Microsoft Excel 2007, 2010, 2013 |
Optionally, We can type one or more blank spaces following the dash to force Excel to offset the dash from the right edge of the cell by the width of the space(s);
 |
Microsoft Excel 2007, 2010, 2013 Add space to right of dash |
0 comments:
Post a Comment