How to remove $ and - from unused cells

[MonsterMan]

Hi folks, as the title says, how can I remove the $ and - from unused cells on my spreadsheet? I've uploaded a pic of what I mean - https://imgur.com/a/hsRQyPV

Thanks again,

 

[Rick Rothstein]

Hi folks, as the title says, how can I remove the $ and - from unused cells on my spreadsheet? I've uploaded a pic of what I mean - https://imgur.com/a/hsRQyPV

It looks to me like you have that column formatted as "Accounting" and that you have a formula in all of your cells that display the number 0... am I correct in this? If so, you need to change the formula not to default to a value of 0 but rather to a default value of "" instead.

 

[MonsterMan]

Yes, it was formatted with a custom formula. This is the custom one -> _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)

Which I hope, adds ( ) around a negative number. Is there a way to rewrite this so it omits the '0' as you mentioned?

Thanks

 

[Rick Rothstein]

Yes, it was formatted with a custom formula. This is the custom one -> _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)

Which I hope, adds ( ) around a negative number. Is there a way to rewrite this so it omits the '0' as you mentioned?

Do you have a formula in your cells? If so, show it to us as I think you will want to control things from your formula. I can make your Custom Format display nothing for 0, but it would do that for all 0's, not just the ones below your data (so, if you had an actual 0 value within your data, i think you would want the "$ -" displayed for it).

 

[MonsterMan]

Yes, I have a formula in the cells ---> =IFERROR(IF(G3="Long",M3*L3-I3*L3,I3*L3-M3*L3),"")

 

[Rick Rothstein]

Yes, I have a formula in the cells ---> =IFERROR(IF(G3="Long",M3*L3-I3*L3,I3*L3-M3*L3),"")

Based on the data that you showed us in the link you posted early, try replacing that formula with this one and I think it will solve your problem (I highlighted the changes I made in red)...

=IFERROR(IF(M3="","",IF(G3="Long",M3*L3-I3*L3,I3*L3-M3*L3)),"")

 

[MonsterMan]

That's great. Thanks for your help, it works perfect, just how I wanted.