When you have decimal numbers in Excel, you might not know whether you’re overrepresenting or excluding the important figures during the calculation.

To have the precise number in calculations, you could count only the significant figures. It is the **total number of important digits you count in a numerical value from the left side**.

Here, you must consider the value zero and the decimal when counting them. So, before we dive into the steps directly, let’s quickly recap the rule for Significant Figures.

**Leading Zeros:**It is never significant. Suppose, you have anumber.`0.00703`

**3**numbers`703`

**Between Zeros:**The Zero that lies in between non-zero digits is counted. In number, the significant figure is`705.001`

**6**.**Trailing Zeros:**It is not counted when there are no decimal digits. For example, in, there’s only`700`

**1**Significant figure**7**.

But, if there’s a decimal or leading zeros at the front, it is counted. Example:has`10.0`

**3**significant figures.has`0.005000`

**4**figures which is`5000`

.

Now, let’s count these Significant Figures such that you have the exact required numbers in Excel.

## Count Significant Figures Using Formula

To count, we will use several functions nested together. Depending on how your number is stored in Excel, use Example 1 or 2.

### Example 1: Numbers Stored as Number

Suppose, I have lists of numbers from cell **A3 to A9**. Let’s count the Significant Figures in the **Column B**.

For that, I used this formula and used the + cursor to copy it down to the remaining columns.

**=IF(A3="","",IF(ABS(A3)=0,0,LEN((ABS(A3)/10^LEN(ABS(A3)))*10^(LEN(ABS(A3)/10^LEN(ABS(A3)))-LEN(INT(ABS(A3)/10^LEN(ABS(A3))))-1)))+LEN(A3)-LEN(ABS(A3))-IF(LEFT(A3,1)="-",1,0)+IF(OR(LEFT(A3,1)=".",LEFT(A3,2)="-."),1,0))+IF(AND(NOT(ISERROR(FIND(".",A3))), ISERROR(FIND(".",ABS(A3)))),-1,0)**

Since the formula is long, you might make mistakes when typing it one by one. Instead, just copy-paste the formula on your sheet and replace the **A3 cell reference**.

As per the Significant Figure rule we’ve mentioned above, the formula counted only important digits. It returned **6** for a **2127.81 number**.

### Example 2: Numbers Stored as Text

Let’s say, here I have numbers in text format. We will find out the Significant Figures without changing the cell format.

For that, I used the formula mentioned in the box. Use the copy-paste command to insert the formula in your sheet and change the cell reference.

**=LEN(MID(SUBSTITUTE(SUBSTITUTE(B3,".",""),",",""),AGGREGATE(15,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(SUBSTITUTE(SUBSTITUTE( B3,".",""),",",""))))/(MID(SUBSTITUTE(SUBSTITUTE(B3,".",""),",",""),ROW($XFD$1:INDEX(XFD:XFD,LEN(SUBSTITUTE(SUBSTITUTE(B3,".",""),",","")))),1)<>"0"),1),99))**

In the cell **C3 **for **002127.81 **number, I got **6 **significant figures. Once you get the result, drag down the Auto-Fill handle to update the entire column.

## Count Significant Figures Using VBA

For users who need to use the formula regularly to count significant numbers, I have constructed a VBA to insert a custom function. It’s the best way to avoid redundant tasks and save your time.

The code will add a function named** SignificantFigure**. In the code, I have set two function arguments which are number and case.

Custom Function Name | Syntax | Function Arguments |

SignificantFigure | =SignificantFigure(number, case) | Number: enter a cell referenceCase: Specify to return significant figure.1: Minimum Significant Figure2: Return Minimum Significant Figure |

To create this function, follow the given steps. Check and load the Developer Tab before you start.

- First, open the
**VBA window**in your worksheet using the**Alt + F11** - Click
**Insert**>**Module**. - Copy and Paste Code.

```
Option Explicit
Function SignificantFigure(Rng As Range, Optional xType As Integer = 1)
Dim R As Range
Dim S, SF As String
Dim xMax, xMin, xDec, x As Integer
Application.Volatile
Set R = Rng.Cells
If Not IsNumeric(R) Or IsDate(R) Then
SignificantFigure = CVErr(xlErrNum)
Exit Function
End If
SF = Trim(R.Text)
S = ""
xDec = InStr(SF, ".")
For x = 1 To Len(SF)
If Mid(SF, x, 1) >= "0" And _
Mid(SF, x, 1) <= "9" Then _
S = S & Mid(SF, x, 1)
Next
While Left(S, 1) = "0"
S = Mid(S, 2)
Wend
xMax = Len(S)
SF = S
If xDec = 0 Then
While Right(SF, 1) = "0"
SF = Left(SF, Len(SF) - 1)
Wend
End If
xMin = Len(SF)
Select Case xType
Case 1
SignificantFigure = xMin
Case 2
SignificantFigure = xMax
Case Else
SignificantFigure = CVErr(xlErrNum)
End Select
End Function
```

- Press
**Save**. Excel will send you a warning saying there’s a Macro. You can check out our article to learn how to save Macro containing Workbook.

Now, use the function. Here, I entered ** =SignificantFigure(C2, 1) **in cell D2. The formula returned

**6**.

To apply the formula, drag down the Auto Fill Handle.