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

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.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Comparing a list of Values in a column

luwi123 | | Permalink

 

 

A B C D E F G
Long Horne HSD 3 Trad. Brick 1919-1944  £ 48,000      67.33
Long Horne HSD 3 Trad. Brick 1919-1944  £ 48,000      65.30
Long Horne HSD 3 Trad. Brick 1919-1944  £ 48,000      67.33
Long Horne HSD 3 Trad. Brick 1919-1944  £ 48,000      67.33
Long Horne HSD 3 Trad. Brick 1919-1944  £ 48,000      67.33
Long Horne HSD 3 Trad. Brick 1919-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.