# How to round to significant figures in Excel

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

**Excel:** `=ROUND(A2,$E$1-1-INT(LOG10(ABS(A2))))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Value | Rounded |  | Significant figures | 3 |
| 123456 |  |  |  |  |
| 0.0045678 |  |  |  |  |
| 98.765 |  |  |  |  |
| -1534.2 |  |  |  |  |
| 7.0049 |  |  |  |  |
| 250 |  |  |  |  |

Formula in B2, filled to B7: `=ROUND(A2,$E$1-1-INT(LOG10(ABS(A2))))`

Result: 123000; 0.00457; 98.8; -1530; 7; 250

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

## Other ways

- `=ROUND(A2,$E$1-(1+INT(LOG10(ABS(A2)))))`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/round-to-significant-figures