Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel 2min Tip #9. How to replace zeroes with dashes. By David Carter

by
14th Sep 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

If your spreadsheet contains a lot of zero (0) values, it will be easier to read if you change them to dashes

To display zeroes as dashes:

Go into Format – Custom, and add ;-; (semicolon – dash – semicolon)

Worked example:
Either open or create a worksheet that contains some zeroes (0).

Highlight the numbers. Then from main menu: Format – Cells – Custom (at the bottom)

In the Type box you’ve probably got “General”

Find the format #,###0;[Red]-#,###0 (in Excel 2003, it’s at the bottom of the screen)

Put it into the Type box.

After the zero at the far right, type: semi-colon - dash – semicolon, so it looks like this

#,###0;[Red]-#,###0;-;

Press OK. All the zeroes are now displayed as dashes. With the zeroes gone, the remaining numbers are much easier to read.

END

Explanation
Excel uses semi-colons to separate the number formats:

- the format before the first semi-colon applies if the number is positive

- the format before the second semi-colon applies if the number is negative

- the format before the third semi-colon applies if the number is zero

[Note, by the way, that Excel has stored this new format – you don’t have to create it each time. To find it, scroll to the bottom of the list of Custom formats].

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By luwi123
30th Jan 2013 09:04

Comparing a list of Values in a column

 

 

ABCDEFGLong HorneHSD3Trad. Brick1919-1944 £ 48,000     67.33Long HorneHSD3Trad. Brick1919-1944 £ 48,000     65.30Long HorneHSD3Trad. Brick1919-1944 £ 48,000     67.33Long HorneHSD3Trad. Brick1919-1944 £ 48,000     67.33Long HorneHSD3Trad. Brick1919-1944 £ 48,000     67.33Long HorneHSD3Trad. Brick1919-1944 £ 48,000     67.33  

I have been trying to use =OR(EXACT(A1, A2:A7) to test if all cells in the column are the same/equal. I am checking for rent anamolies in col G. It seems to say TRUE if only one value is the same as the TestValue

The condition is if cells in each Col (A to F) are the same then the values in Col G should be all be the same. I am working on long spreadsheet (7.5k rows) and have grouped all similar properties together based on values in Col A to F.

I want to check each column to get a True or False answer, that all values are the same/equal.

 

Thanks (0)