We can define significant figures as the important figures of a certain number that accurately represent the meaning of that number. The concept of significant figures is very important in the measurement process. By counting the significant figures of various numbers, we can determine how precise or accurate those numbers are. In this article, we will show you how to count significant figures in Excel.

**Table of Contents**Expand

## Characteristics of Significant Figures

Before going into our procedure, first of all, we will know about the characteristics of the significant figures. These characteristics, or criteria of significant figures, will help us to count these figures and differentiate them from any other numbers. The characteristics are as follows.

- All digits of a number that are non-zero are significant. For example-
has four significant figures.*8589* - If there are any zeros placed between two non-zero digits, then they are significant. For example-
has four significant figures.*8089* - Zeros which are in the leading position of a number, are not significant. For example-
has two significant figures.*0079* - If the zeros are to the right of the last non-zero digit in a number that has a decimal point, they are significant. For example has
four significant figures.**7500.** - If there is no decimal point, then those zeroes are not significant. For example-
has two significant figures.*7500* - If the zero digits are to the right of a decimal point in a number, then they are significant. For example-
has six significant digits.*7500.00* - If the zeroes are at the beginning of a decimal number, then they are not significant. For example-
has four significant figures.*0.**007500*

## How to Count Significant Figures in Excel: 2 Easy Ways

In this article, you will see two different ways to count significant figures in Excel. In our first approach, we will combine various functions of Excel to count significant figures. As for our second method, we will apply a **VBA** code for the same purpose. To achieve our goal, we will use the following data set.

### 1. Combining Functions to Count Significant Figures

In our first approach to count significant figures, we will combine multiple functions of Excel to create a formula. In the combination, we will use

to verify a statement.*the IF function*to find the absolute value of a number.*the ABS function*to determine the length of a character.*the LEN function*to round a number to its closest integer value.*the INT function*to choose a specific character from the cell value starting from the left side of the value.*the LEFT function*and*the OR function*to state if any condition is true or false.*the AND function*to reverse any statement.**the NOT function**to check if there is any error in the specific cell value.*the ISERROR function*

How we will combine this function and what the outcome will be are given in the following steps.

**Step 1:**

- First of all, type the following formula in cell
.*C5*

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

**Formula Breakdown**

:*ISERROR(FIND(“.”,ABS(B5)))***The FIND function**finds the position of the decimal point in cell**B5**. As there is no decimal point in cell**B5,**it will show**#VALUE!**As a result, the**ISERROR function**sees errors in the result of**the FIND function**so it will show**True**.: As there is no decimal point in cell*ISERROR(FIND(“.”,B5))***B5,**it will find an error and display**True**as a result.:*NOT(ISERROR(FIND(“.”,B5)))***The NOT function**will reverse the result found from the previous step. It will display**False**as a result.: As the first part of this function displays*AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5))))***False**and the second part shows**True**as a result, this**AND function**will show**False**as a result ultimately because both the arguments aren’t true at the same time.: As the result from the previous step is*IF(AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))),-1,0)***False**,**the IF function**will show**0**as a result.:The first character of the value of cell*LEFT(B5,1)=”.”***B5**is not a decimal point. So the result is**False**.:The second character is not a negative sign or a decimal point. That’s why the result will also be*LEFT(B5,2)=”-.”***False**.: As for the above two arguments, both return*OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”)***False**as results, so**the OR function**will also show**False**.: As the result from*IF(OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”),1,0))***the OR function**is false,will display**the IF function**as a result.**0**:*IF(LEFT(B5,1)=”-“,1,0)***The LEFT function**checks if the first left character of cell**B5**is a negative sign. If it’s not, then it will show**False**as a result. Then**the IF function**will show**0**as a result because the statement is not true.: As both of the character lengths of cell value of*LEN(B5)-LEN(ABS(B5))***B5**are equal so the result will be**0**.: The absolute value of cell*-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))***B5**is**40085**and its character length is**5**. After dividing the value by**10^5**it becomes.*0.*40085**The INT function**then returns the nearest integer value, which is**0**. The length of this character becomes**1**. The minus sign makes it**-1**.: The result of the first part of this argument before the multiplication sign is*LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))***40085**. Then, after multiplication, the result becomes**4008500**. So the length of the character is**7**.: The first part of this argument before the first minus sign gives the result of*LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1)))***7**. After adding**-2**to it, the result becomes**5**. The**-2**comes from the**-1**that was found after the calculation of**the INT function**and the**-1**that is already present in the formula.: If the absolute value of cell*IF(ABS(B5)=0,0,LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1)))+LEN(B5)-LEN(ABS(B5))-IF(LEFT(B5,1)=”-“,1,0)+IF(OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”),1,0))+IF(AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))),-1,0)***B5**is**0**then it will show**0**. Otherwise, it will count the number of significant figures by using the above steps.: If the cell*IF(B5=””,””,IF(ABS(B5)=0,0,LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1)))+LEN(B5)-LEN(ABS(B5))-IF(LEFT(B5,1)=”-“,1,0)+IF(OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”),1,0))+IF(AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))),-1,0)***B5**is blank then**the IF function**formula will keep it blank. If it has a value in it, the procedure for counting the significant figure will start. We have found two actual values for the formula, which are**7**and**-2**. After adding them,the result becomes**5**. All the values found from the followingare*IF functions***0**. So, after the final calculation the number of significant figures for the value of cell**B5**is**5**.

**Read More: How to Change Significant Figures in Excel**

**Step 2:**

- Secondly, press
to see the significant figure for the value in cell*Enter*.*B5* - In this case, the significant figure
is*40058*.*5*

**Step 3:**

- Finally, use the
feature of*AutoFill**Excel*to count significant figures for the lower cells.

### 2. Applying VBA to Count Significant Figures in Excel

In our second approach, we will apply a VBA code to count significant figures. We will use a custom function after applying this code. This function will help us to count significant figures based on certain conditions. The detailed steps for this procedure are in the following.

**Step 1:**

- Firstly, go to the
tab of the ribbon.*Developer* - Then go to the
command in the Code group.*Visual Basic*

**Step 2:**

- Secondly, you will see the VBA window after selecting the command.
- Then from the
tab choose*Insert*.*Module*

**Step 3:**

- Thirdly, copy the following
**VBA**code and paste it into the module.

```
'Defining the function name
Function CountSignificant(Rng As Range, Optional xType As Integer = 1)
'Declaring variables
Dim zCell As Range
Dim yText, yText2 As String
Dim xMax, xMin, xDec, x As Integer
'Set the function as volatile
Application.Volatile
'We will assign the cell values in zcell
Set zCell = Rng.Cells
'Check if the cell value is a number or not
If Not IsNumeric(zCell) Or IsDate(zCell) Then
'if the cell value is not a number then it will show error
CountSignificant = CVErr(xlErrNum)
'After finding error the function will exit
Exit Function
'End of the if function
End If
'if any leading space is found then it will be removed
yText2 = Trim(zCell.Text)
yText = ""
'check the position of the decimal points in cell value
xDec = InStr(yText2, ".")
'we wil remove any characters other than numbers
For x = 1 To Len(yText2)
If Mid(yText2, x, 1) >= "0" And _
Mid(yText2, x, 1) <= "9" Then _
yText = yText & Mid(yText2, x, 1)
Next
'After that we will remove any zero that is in the leading position with a while loop
While Left(yText, 1) = "0"
yText = Mid(yText, 2)
Wend
xMax = Len(yText)
'Then using another while loop, we will remove any trailing zeroes if there is no decimal in the cell value
yText2 = yText
If xDec = 0 Then
While Right(yText2, 1) = "0"
yText2 = Left(yText2, Len(yText2) - 1)
Wend
'End of the if function
End If
xMin = Len(yText2)
'defining two cases; where the first and second case will show minimum and maximum significant digits respectively
Select Case xType
Case 1
CountSignificant = xMin
Case 2
CountSignificant = xMax
'If the condition is not met then it will show error
Case Else
CountSignificant = CVErr(xlErrNum)
'End of the select case
End Select
End Function
```

**VBA Breakdown**

- The name of the custom function is
.*CountSignificant* - There are three types of variables here;
is the range type variable;*zCell*,*yText*are the string type variables, and*yText2*,*xMax*and*xMin*are integer type variables.*xDec* : We will assign the cell values of the range in zCell.*Set zCell = Rng.Cells*: This*If Not IsNumeric(zCell) Or IsDate(zCell) Then CountSignificant = CVErr(xlErrNum) Exit Function*will check if the assigned cell value is a number or not. If the value is not a number or date, then this will cause an error and will end the procedure.**IF function**: This command will remove any leading space.*yText2 = Trim(zCell.Text) yText = “”*: This will find out the position of decimal in the numbers*xDec = InStr(yText2, “.”)*: We will use the combination of the for loop and*For x = 1 To Len(yText2) If Mid(yText2, x, 1) >= “0” And _ Mid(yText2, x, 1) <= “9” Then _ yText = yText & Mid(yText2, x, 1)*to remove any characters other than numbers. Because they are not listed as significant figures.**the IF function**: Using this while….wend loop, we will remove any leading zeroes from the cell value.*While Left(yText, 1) = “0” yText = Mid(yText, 2) Wend xMax = Len(yText)*: Now, we will remove any trailing zeroes if there is no decimal in the cell value using this while… wend loop.*yText2 = yText If xDec = 0 Then While Right(yText2, 1) = “0” yText2 = Left(yText2, Len(yText2) – 1) Wend*: Then, we will select two case types. In the first case, the CountSignificant function will show the minimum number of significant figures; in the second case, it will show the maximum number of significant figures. Anything other than this condition will show an error.*Select Case xType*

**Step 4:**

- Fourthly, go to your active worksheet.
- Then, in cell
type the following formula after putting the previously mentioned code into the module.*C5*

`=CountSignificant(B5,1)`

**Step 5:**

- Fifthly, hit the
button and this function will allow you to count the desired significant figure.*Enter* - Then, show the result for the other cells with the help of the
**AutoFill**feature.

**Read More: How to Keep Significant Figures in Excel**

**Download Practice Workbook**

You can download the free Excel workbook here and practice on your own.

## Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to count significant figures in Excel by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below.

## Related Articles

**<< Go Back to Significant Figures | Rounding in Excel | Number Format | Learn Excel**

I was looking for a formula like this for so long. Thank you very much buddy. Although, i need help modifying this formula to suit my needs. For my application, i have same definition of significant digits except for one that is counting zeroes in a number with decimal point that starts with zero.

example –

For example –

0.0036589 has 5 significant digits with your formula.

For me, it should return 8. Counting leading zeroes before numbers if it is after a decimal point. Can you please help me with this?

Hello Nimesh,

Thank you for your query. You can find the answer to your question in the Excel file linked to this reply.

Counting Significant Figures.xlsx

Here is a screenshot of the results in the Excel file.

Hope you this useful. Have a good day.

Regards,

ExcelDemy