How to Round to Significant Figures in Excel (2 Suitable Methods)

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.


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.

How to Round to Significant Figures in Excel


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.

Rounding to Significant Figures using ROUND, INT, LOG10, and ABS function

Drag fill handle icon

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

How to Round to Significant Figures in Excel


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.

Using Excel Formula with ROUND, INT & LOG10 Functions - Dataset

  • 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

Using Excel Formula with ROUND, INT & LOG10 Functions

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

Using Excel Formula with ROUND, INT & LOG10 Functions - Output

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

Open Microsoft Visual Basic for Applications

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

Insert Module

  • 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

VBA Code to Create Custom Function of Rounding to Significant Figures

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

Using VBA Code to Create Custom Function - Output


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.

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo