## Method 1 – Using Excel Formula Round to Significant Figures

### 1.1 Combining ROUND, INT, LOG10 & ABS Functions

** Steps:**

**Paste**the following formula into cell**E5**to round the value of cell**D5**to**3 significant digits.**

`=ROUND(D5,3-(1+INT(LOG10(ABS(D5)))))`

** Formula Breakdown:**

The breakdown of the formula for the cell E5:

**D5 = 7611250**: It’s the cell value that will be rounded to significant figures.**ABS(D5) = ABS(7611250) = 7611250**: The**ABSOLUTE function**will return a value in positive sign.**LOG10(ABS(D5)) = LOG10(7611250) = 6.88145**: The**LOG10 function**will return the 10 based logarithmic result of 7611250 which 6.88145.**INT(LOG10(ABS(D5))) = INT(6.88145) = 6**: The**INT function**will retire the nearest lower integer of value 6.88145 which is 6.**3-(1+INT(LOG10(ABS(D5))) = 3-(1+6) = -4**: It gives the remaining digits of the number excluding the first 3 digits.**ROUND(D5,3-(1+INT(LOG10(ABS(D5))))) = ROUND(7611250,-4) = 7610000**: The**ROUND function**will round the last 4 digits to zero of the value.

- Drag the
**Fill Handle**icon to paste the formula to the other cells of the column, or use**Excel keyboard shortcuts****Ctrl+C**and**Ctrl+V**to copy and paste.

- Get the values of the
**Sales**column rounded to**2 significant.**

### 1.2 Using Excel Formula with ROUND, INT & LOG10 Functions to Round to Significant Figures

** Steps:**

- Create a cell to take the input of significant figures.

**Paste**the following formula into cell E5:

`=ROUND(D5/10^(INT(LOG10(D5))+1),$G$5)*10^(INT(LOG10(D5))+1)`

** Formula Breakdown:**

Here, showing the breakdown of the formula for the cell E5:

**D5 = 7611250**: It’s the cell value that will be rounded to significant figures.**LOG10(D5) = LOG10(7611250) = 6.88145**: The**LOG10 function**will return the 10-based logarithmic result of 7611250, which is 6.88145.**INT(LOG10(D5))) = INT(6.88145) = 6**: The**INT function**will retire the nearest lower integer of value 6.88145 which is 6.**D5/10^(INT(LOG10(D5))+1) = 7611250/10^(6+1) = 0.761125:**It converts the**D5 cell**value to a fraction.**ROUND(D5/10^(INT(LOG10(D5))+1),$G$5) = ROUND(0.761125,4) = 0.761:**The**ROUND function**will round the fraction number to 3 digits after the decimal point.**10^(INT(LOG10(D5))+1) = 10^(6+1) = 10000000:****Multiply**the rounded fraction value with this.**ROUND(D5/10^(INT(LOG10(D5))+1),$G$5)*10^(INT(LOG10(D5))+1) =0.7611*10000000 = 7610000:**Is the rounded value to 3 significant figures of cell**D5**

**Drag**the**fill handle**icon to the last cell of the column and get the column filled with the output of the same formula.- The
**Sales Column**values are rounded to 3 significant figures.

- Change the value of the significant figures in cell
**G5**to**4**and you will see the**Rounded Figure**column is changed automatically to**4 significant figures**.

## Method 2 – Applying VBA Custom Function to Round to Significant Figures

** Steps: **

- Go to the top ribbon, press on the
**Developer,**and then press on the**Visual Basic**option from the menu.

Use **ALT + F11 **to open the **‘Microsoft Visual Basic for Applications’ **window if you don’t have the Developer tab added.

- A window named
**“Microsoft Visual Basic for Applications”**will appear. From the top menu bar, press on the**“Insert”**a menu will appear. Select the**“Module’”**option.

- A new
**“Module”**window will appear, and**Paste**this VBA code into the box.

```
Function ROUNDSIGNIFICANT(Value As Variant, Digits As Variant)
Dim exp As Double
If IsNumeric(Value) And IsNumeric(Digits) Then
If Digits < 1 Then
' Please Insert the " #NUM " error
ROUNDSIGNIFICANT = CVErr(xlErrNum)
Else
If Value <> 0 Then
exp = Int(Log(Abs(Value)) / Log(10#))
Else
exp = 0
End If
ROUNDSIGNIFICANT = WorksheetFunction.Round(Value, _
Digits - (1 + exp))
End If
Else
' Please Insert the " #N/A " error
ROUNDSIGNIFICANT = CVErr(xlErrNA)
End If
End Function
```

- After inserting the code in the module, come back to the worksheet. Use a new function named
**ROUNDSIGNIFICANT**to round values to significant figures in Excel. - Paste this formula into cell E5 and it will return the rounded values.

`=ROUNDSIGNIFICANT(D5,3)`

- Drag the
**Fill Handle**icon to paste the used formula respectively to the other cells of the column or use**Ctrl+C**and**Ctrl+V**to copy and paste.

## Things to Remember

- Using the first formula shown in this article, If you want to change the number of significant figures then you have to change the formula in each cell.
- But using the 2nd formula, to change the number of significant figures, you have to insert the value in the G5 cell.
- While using the VBA code, you don’t have to run the code. Just paste it into the module code and come to the Excel worksheet.

**Download Practice Workbook**

You can download the practice workbook from here:

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