changing ##### to (blank cell)

[Brown]

I have created a sheet with a lot of data that will be input over time, and there are ##### showing up that I would prefer not be there.
Is there a way to, easily go back and make those go away?
thanks, and just for information there may be more than 200 cells in the sheet showing this.
this link will give you a better idea, sorry the screen shot wouldnt work.

http://i287.photobucket.com/albums/ll127/nu2trout/excel capture_zps6jachbr1.jpg

 

[Paul Ked]

They are probably there because the cell is too small to show the value (or the text is too big!)

Resize either the cell or font.

 

[Jonmo1]

I assume these are time values? Excel cannot format a Negative time value.

Try formatting the cells as Custom with this stirng
h:mm;;h:mm

 

[Paul Ked]

Try formatting the cells as Custom with this stirng
h:mm;;h:mm

Like it

 

[Brown]

when i stretch the cell i get #VALUE .
Ok so new question, how can i remove that and show a blank cell or 0?

 

[Jonmo1]

Whatever your formula is in that cell, wrap it in IFERROR

=IFERROR(yourformula,0)

 

[Brown]

ok so i would have to go back into every cell and type that formula so that none of that things appear in my sheet?
Thanks
Brown

 

[Paul Ked]

Yup!

 

[Jonmo1]

You shouldn't have to manually change every single formula.
If your formulas are written with effective absolute cell referencing, then you should only need to adjust the formula at the top of each column, and fill them down.
And/Or at the left of each row and fill right, depending on your sheet layout.

 

[Brown]

oh my, ok i will give it a try on a dummy sheet i can play with so i dont ruin the entire sheet.
thanks
Brown