Formatting for a dash instead of a zero

Question

Good day, 

I am working in a large workbook and I have some worksheets that are returning a zero instead of a dash.  I have formatted all of the sheet the same; however, for some reason on some of the worksheets the formatting wants to return a (0.00) instead of the dash even though the same formula is being used in the format cells (under number) section.  

Here are a couple of examples:

and here is the other example

It is the same formula, but for whatever reason one of them shows as zeros and the other a dash even though it is set up to show as a dash.  

What can I do to get all of my formulas to set up as a dash?  

Thanks for your help in advance.

Answer 1

Hi RebeccaPI,

Thanks for reaching out. My name is Ali. I'm an Independent Advisor and a Microsoft user like you. I'll be happy to help you out today.

You are using the correct format to convert Zero's into the dash (-) and the reason for getting 0.00 could be the cells that contain a positive number like 0.0003 will always return 0.00 instead of dash (-).

Please refer to the link below regarding the format on our tech forum.

https://techcommunity.microsoft.com/t5/excel/ad...

So if you are getting these numbers through formula then you can wrap your formula into the ROUND function to get values up to two decimal places. In this way, once you apply the same format then you will get dash (-) instead of 0.00.

You can found more about the ROUND function here;

https://support.microsoft.com/en-us/office/roun...

I hope it worked out well for you. If not, please reply back to this forum to let me know or mark this as resolved along with your feedback. :) I will be glad to follow up and help you.

Thanks,

Ali

Answer 2

In this image, you can see the ROUND function applied in the cell E31 referencing the same value (0.0003) from Cell D31 and the result is a dash (-)

Please reply back to this forum to let me know or mark this as resolved along with your feedback. :)

Thanks,

Ali

Answer 3

Please check the same issue here where the cell contains 0.0003 and the format is the same you shared from your end that's why I am getting 0.00 instead of a dash (-).

Hope it helps

Thanks,

Ali

Answer 4

Hi Ali, 

This is the issue, but the digit is .000000000016 out.  I just have to learn how to wrap the Round, which I am working on now.  

Thank you so much for helping me with my questions!

Becca