It seems likely that @Brad is correct (so Kevin’s suggestion may not work). That the contents of these cells are zero – it is just that your set up has been instructed to display (format)
- (for example enter
0 somewhere then select that cell and click Home/Number/
’). However, since actually zero, the representation should make no difference to any calculations.
Custom formats are saved with the workbook, so this could be why you are not seeing the format you are used to (ie the format was chosen by the person who sent you the file).
Custom number formats allow independent control of the display of positive, negative, zero and text values (in that order) with use of the semi-colon separator. Hence:
- Positives: in blue, comma separator for thousands, rounded to two
decimal places (with period) and trailing (for alignment with negatives) space.
- Negatives: in red, in parentheses, comma separator for thousands, period for decimal,
rounded to two decimal places.
- Text: Preceded by ‘Annual ’.
Not all four sections need be used, for example
[Blue]#,##0.00_);;-;"Annual "@ would not display negatives, though these would still be available for calculation (which could be very confusing). Similar but probably more useful would be
[Blue]#,##0.00_);[Red](#,##0.00);; which suppresses display of zeros values in any fashion (and also text).