Excel, turning a zero to a different number automatically.

Posted on


What would be the best way to convert a 0 ( zero ) to .01 ?
i have looked at conditional formatting, hex2dec etc. I know there is a simple answer…. just stumped at the moment.

This would need to be for multiple rows/columns….


Select the portion of the worksheet you wish to convert and run this short macro:

Sub NotQuiteZero()
    Dim r As Range

    For Each r In Intersect(Selection, ActiveSheet.UsedRange)
        If r.Text = "0" Then r.Value = 0.01
    Next r
End Sub


enter image description here

and after:

enter image description here

Some options:

  • Find and replace
  • Worksheet_Change event VBA routine
  • entering the value correctly in the first place,

You must realize that 0 and 0.01 are different values and no amount of formatting will turn one value into the other.

If you need more details for a particular approach, edit your question and describe the scenario, then post a comment.

Leave a Reply

Your email address will not be published. Required fields are marked *