How to Round to Significant Figures in Excel

To round to significant figures in Excel, you combine ROUND with a calculation that works out how big the number is, because Excel has no function for it. The standard formula is =ROUND(A2,3-1-INT(LOG10(ABS(A2)))) for 3 significant figures. This guide explains what each part does, gives a simpler version for everyday numbers, and covers zero, negatives and showing trailing zeros. It is useful for science, engineering and any report where precision should match the data.

What are significant figures?#

Significant figures are the digits in a number that carry meaning, counted from the first non-zero digit. Leading zeros don't count: they only show where the decimal point is.

This is different from decimal places. ROUND's second argument counts decimal places, so =ROUND(A2,3) always keeps 3 digits after the decimal point, whatever the size of the number. To round to significant figures, that argument has to change with the number. The guide to ROUND, ROUNDUP and ROUNDDOWN covers the basics of the second argument.

The significant figures formula in Excel#

This formula rounds the number in A2 to the number of significant figures in B2:

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

Read it from the inside out:

1. ABS(A2) removes a minus sign, because LOG10 can't handle negative numbers.
2. LOG10(...) gives the power of ten. For 48,213 it is about 4.68, because 10⁴ is 10,000.
3. INT(...) keeps the whole part: 4. This is the position of the first significant digit.
4. B2-1-4 works out the decimal places. For 3 significant figures, 3 − 1 − 4 = −2.
5. ROUND(A2,-2) rounds to the nearest hundred: 48,200.

A negative second argument rounds to the left of the decimal point. That is the same trick used to round to thousands or millions.

For 0.004567, LOG10 is about −2.34 and INT gives −3 (INT always rounds down). Then 3 − 1 − (−3) = 5, and ROUND(0.004567,5) returns 0.00457. One formula handles big and small numbers.

A simpler formula for numbers of 1 or more#

If every number is 1 or more, you can count the digits before the decimal point with LEN instead of using LOG10:

=ROUND(A2,3-LEN(INT(ABS(A2))))

INT(48213) is 48213, and LEN counts its characters: 5. So 3 − 5 = −2, and ROUND gives 48,200 again. For 7.891, INT gives 7, LEN gives 1, and 3 − 1 = 2 decimal places: 7.89.

This version is easier to explain to colleagues, but it fails for numbers below 1. For 0.004567, INT gives 0, LEN gives 1, and the formula rounds to 2 decimal places and returns 0, which loses all the meaning. Use the LOG10 formula when small numbers are possible. The LEN function guide explains how LEN counts characters, and the ROUND function guide covers the second argument.

Handle zero, blanks and negative numbers#

The LOG10 formula needs one fix for real data: zero. LOG10(0) has no answer, so Excel returns #NUM!, and an empty cell counts as zero too. Wrap the formula in IF:

=IF(A2=0,0,ROUND(A2,3-1-INT(LOG10(ABS(A2)))))

Negative numbers already work, because ABS is used only to measure the size. ROUND still rounds the original value, so −1,234.5 becomes −1,230.

A cell that mixes a number with a unit, such as 48213 g, gives #VALUE!. Keep only the number in the cell and put the unit in the column header.

Round up or down to significant figures#

Swap ROUND for ROUNDUP or ROUNDDOWN to always round away from zero or toward zero. The digits calculation stays the same:

=ROUNDUP(A2,3-1-INT(LOG10(ABS(A2))))

For 48,213, ROUNDUP gives 48,300 and ROUNDDOWN gives 48,200. To round to a fixed step, such as the nearest 5 or 0.25, rather than a number of significant figures, see rounding to the nearest number in Excel.

Show significant figures without changing the value#

Sometimes you only want the display to show 3 significant figures, while the cell keeps the full value. Use the Scientific number format:

1. Select the cells and press Ctrl+1 (Mac: ⌘1).
2. Choose Scientific and set Decimal places to 2.

With 2 decimal places, every number shows 3 significant figures: 48,213 appears as 4.82E+04. To get the same look as text, use =TEXT(A2,"0.00E+00").

Formatting also solves the trailing-zero problem. Rounding 1.999 to 3 significant figures gives 2, and Excel shows just "2", not "2.00". The value is right, but the display hides the precision. Set the cell to two decimal places, or use the Scientific format, to show 2.00.

Excel's own 15-digit limit#

Excel stores numbers with up to 15 significant digits. If you type a 16-digit number, such as a long card or account number, Excel changes the last digit to 0. That is why long IDs should be typed as text, with a leading apostrophe or in cells formatted as Text before you type.

The way Excel stores decimals also causes tiny differences. For example, =1*(0.5-0.4-0.1) returns a very small number instead of exactly 0. Rounding to a sensible number of significant figures, or decimal places, keeps reports clean. If you then work with percentages of the rounded values, the guide to increasing a number by a percentage shows the next step.

Practise it#

Every significant-figures formula is ROUND with a calculated second argument, so the key skill is understanding that argument. Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you round to decimals, whole numbers and hundreds, and each answer is checked instantly. Then try the significant-figures exercise on this page.