Spreadsheet Practice

How to round to significant figures in Excel

Formula (Excel and Google Sheets)

=ROUND(A2,$E$1-1-INT(LOG10(ABS(A2))))

Work out the digit position from the size of the number: =ROUND(A2,$E$1-1-INT(LOG10(ABS(A2)))).

Result on the example
one result per row in B2, filled down to B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

INT(LOG10(ABS(A2))) is the position of the first digit: 5 for 123,456 (hundred-thousands), -3 for 0.0045678. Subtracting it from E1-1 gives the digits argument ROUND needs: -3 for 123,456, which rounds to thousands, and 5 for 0.0045678, which keeps five decimals. ABS lets negative numbers work.

=ROUND(A2,$E$1-1-INT(LOG10(ABS(A2))))

Result123000

  1. A2123456

    The value in A2.

  2. $E$13

    The value in E1.

  3. $E$1-12

    The - operator subtracts.

  4. ABS(A2)123456

    ABS: Absolute value.

    • numberA2123456
  5. LOG10(ABS(A2))5.091512202

    LOG10: Base-10 logarithm.

    • numberABS(A2)123456
  6. INT(LOG10(ABS(A2)))5

    INT: Rounds down to the nearest integer.

    • numberLOG10(ABS(A2))5.091512202
  7. $E$1-1-INT(LOG10(ABS(A2)))-3

    The - operator subtracts.

  8. ROUND(A2,$E$1-1-INT(LOG10(ABS(A2))))123000

    ROUND: Rounds to a number of digits; negative digits round left of the decimal.

    • numberA2123456
    • num_digits$E$1-1-INT(LOG10(ABS(A2)))-3

In Google Sheets

The same formula works in Google Sheets: =ROUND(A2,$E$1-1-INT(LOG10(ABS(A2)))). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ROUND(A2,$E$1-(1+INT(LOG10(ABS(A2)))))123000, 0.00457, 98.8…The same calculation grouped as "figures minus the number of digits before the decimal point".

Common mistakes

  • Zero has no logarithm, so the formula returns #NUM! for 0. Wrap it: =IF(A2=0,0,ROUND(…)).
  • Trailing zeros are a display matter: 7.00 to 3 significant figures is stored as 7. Format the cell with 2 decimals if the zeros must show.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Round to significant figures

In B2, round the value to the number of significant figures in E1, then fill down to B7.

Questions

How do I display a number in scientific notation with 3 significant figures?

Format the cell with the number format 0.00E+00, or use =TEXT(A2,"0.00E+00") for text.

How do I always round up to significant figures?

Use ROUNDUP with the same digits argument.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.