Rounding to Significant Figures in Excel

value :: the number you wish to round.
sigfigs :: the number of significant figures you want to round to.

Syntax: ROUNDSIG(value,sigfigs)

Example: ROUNDSIG(-0.04589,2) equals -0.046

Function ROUNDSIG(num As Variant, sigs As Variant) 
    Dim exponent As Double 
    If IsNumeric(num) And IsNumeric(sigs) Then 
        If sigs < 1 Then 
            ' Return the  " #NUM "  error 
            ROUNDSIG = CVErr(xlErrNum)
        Else 
            If num <> 0 Then
                exponent = Int(Log(Abs(num)) / Log(10#))
            Else 
                exponent = 0
            End If
            ROUNDSIG = WorksheetFunction.Round(num, _
                       Sigs - (1 + exponent))
        End If 
    Else 
        ' Return the  " #N/A "  error 
        ROUNDSIG = CVErr(xlErrNA) 
    End If 
End Function

See Also: "How to Create Custom Excel Functions"

To ensure that significant trailing zeros are displayed correctly when rounding a number to a certain number of significant digits, you need to work with text formats. The following formula is very confusing, but it gets the job done.

Update 4/19/2016: Fix for numbers such as 99.95 through 99.9999999 rounded to 3 SDs:

Note: If you are okay with leaving the number displayed in a scientific notation (e.g. 0.100E+003 for the number 100), then the formula can be as simple as this:

Another Note: You can convert a text number to a value by multiplying by 1 or adding 0 or using double negation, for example:

I got the idea to use the text format in this way from John McGimpsey's site. However, the above megaformula lets you choose any number of significant digits. Below is a VBA function using this method to round significant digits. The function returns the value as a string, so when using the value in other formulas, you can use VALUE(cell) to convert the string to a numeric value.

Function ROUNDSF(num As Variant, sigs As Variant) As String 
    Dim exponent As Integer 
    Dim decplace As Integer 
    Dim fmt_left As String 
    Dim fmt_right As String 
    Dim numround As Double 
    If IsNumeric(num) And IsNumeric(sigs) Then 
        If sigs < 1 Then 
            ' Return the   "  #NUM  "   error 
            ROUNDSF = CVErr(xlErrNum) 
        Else 
            numround = WorksheetFunction.text(num, "." & _ 
                        String(sigs, "0") & "E+000") 
            If num = 0 Then 
                exponent = 0 
            Else 
                'Round is needed to fix a ?truncation? 
                'problem when num = 10, 100, 1000, etc. 
                exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1) 
            End If 
            decplace = (sigs - (1 + exponent)) 
            If decplace > 0 Then 
                fmt_right = String(decplace, "0") 
                fmt_left = "0." 
            Else 
                fmt_right = "" 
                fmt_left = "0" 
            End If 
            ROUNDSF = WorksheetFunction.text(numround, _ 
                      fmt_left & fmt_right) 
        End If 
    Else 
        ' Return the   "  #N/A  "   error 
        ROUNDSF = CVErr(xlErrNA) 
    End If 
End Function