How to Round to Significant Figures in Excel

Combine ROUND, LOG10 and INT to round any number — millions or thousandths — to a set number of significant figures.

You will learn to

• Count the digits before the decimal point
• Round a value to 3 significant figures
• Round any value to a chosen number of significant figures

How it works

ROUND(number, digits) rounds to a fixed number of decimal places, which works badly for numbers of very different sizes. To round to significant figures, calculate the digits from the number’s magnitude: =ROUND(B2,n-1-INT(LOG10(ABS(B2)))). For 48,263,915 and n = 3, LOG10 gives 7.68, INT gives 7, and ROUND(B2,-5) returns 48,300,000.

ABS keeps LOG10 working on negative numbers, and the same formula handles small values: 0.0048213 to 2 significant figures becomes 0.0048. The only value it can’t handle is 0, where LOG10 returns #NUM! — wrap it in IF(B2=0,0,…) if zeros can appear.

=ROUND(B2,3-1-INT(LOG10(ABS(B2))))