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

Share this content
1

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 youve probably got General

Find the format #,###0;[Red]-#,###0 (in Excel 2003, its 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

...

Please Login or Register to read the full article

Replies

Please login or register to join the discussion.

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)