If you are searching for the solution or some special tricks to** round to significant figures in Excel** then you have landed in the right place. There are several ways to round to significant figures in Excel. This article will show you each and every step with proper illustrations so you can easily apply them for your purpose. Let’s get into the central part of the article.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here:

## 2 Methods to Round to Significant Figures in Excel

Suppose, you have a dataset where you have some sales amount of 7 digits and you want to round these values to certain significant figures. In this section, I will show you 3 quick and easy methods to round to significant figures in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used **Microsoft 365 version** here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

### 1. Using Excel Formula Round to Significant Figures

#### 1.1 Using Formula with ROUND, INT, LOG10 & ABS Functions

You can create an Excel formula using the **ROUND function** with the **INT function**, **LOG10 function**, and **ABS function**. Follow the steps below to use this formula to round the sales values to a certain significant figure.

**📌 Steps:**

- First,
**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:**

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.**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.

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

- As a result, you will get the values of the
**Sales**column rounded to**2 significant**

#### 1.2 Using Excel Formula with ROUND, INT & LOG10 Functions

You can create another formula to round values to significant figures by specifying the significant figure in a cell. So, you can easily change the number of significant figures and make a dynamic worksheet. For this, follow the steps below:

**📌 Steps:**

- First, create a cell to take the input of significant figures.

- Then,
**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 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:**Now, the**ROUND function**will round the fraction number to 3 digits after decimal point.**10^(INT(LOG10(D5))+1) = 10^(6+1) = 10000000:**Then, you have to**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:**This is the rounded value to 3 significant figures of cell**D5**

- Then,
**drag**the**fill handle**icon to the last cell of the column and get the column filled with the output of the same formula. - As a result, the
**Sales Column**values are rounded to 3 significant figures.

- Now, 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**.

### 2. Using a VBA Custom Function

You can also use **VBA code** to create a **custom function** to round values to certain significant figures in Excel. Follow the below steps to do this-

**📌 Steps: **

- For this, first, go to the top ribbon and press on the
**Developer,**and then press on the**Visual Basic**option from the menu.

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

- Now, a window named
**“Microsoft Visual Basic for Applications”**will appear. Here from the top menu bar, press on the**“Insert”**And a menu will appear. From them, select the**“Module’”**option.

- Now, 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. Now, you can use a new function named
**ROUNDSIGNIFICANT**to round values to significant figures in Excel. - Now, paste this formula into cell E5 and it will return the rounded values.

`=ROUNDSIGNIFICANT(D5,3)`

- Now, 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.

## Conclusion

In this article, you have found how to round to significant figures in Excel. I hope you found this article helpful. You can visit our website** ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.