Significant Figures in Excel: How to Round to a Given Number of Sig Figs

Excel is the world’s most widely used calculation tool, yet it has no built-in significant-figures function. That gap matters for students, engineers, scientists, and educators who need to round to a given number of sig figs without corrupting measurement uncertainty or violating reporting conventions. This reference explains the exact formulas, the standards that govern rounding, and the Excel-specific behaviors that cause errors. It is part of a broader precision and rounding reference, not merely a calculator page.

Rule Statement: Rounding to n Significant Figures in Excel

Significant figures are the digits in a number that carry meaning about its precision. The standard rule is: identify the most significant digit, keep n digits, and round according to the first digit discarded. In Excel, the challenge is that ROUND rounds to decimal places, not significant figures. You must first compute the order of magnitude.

For a nonzero number x in cell A1 and a target of n significant figures, use:

=ROUND(A1, n-1-INT(LOG10(ABS(A1))))

The term INT(LOG10(ABS(A1))) returns the exponent k of the number. For example, 123.456 has k=2 because 10^2 = 100. The number of decimal places to round to is n-1-k. Excel’s ROUND then rounds half away from zero. To handle zero safely, wrap the formula:

=IF(A1=0,0,ROUND(A1, n-1-INT(LOG10(ABS(A1)))))

This formula preserves the sign of negative numbers because ABS is used only inside the logarithm, while ROUND operates on the original value. For reporting, combine it with scientific notation so trailing zeros remain significant. See Scientific Notation and Ambiguous Trailing Zeros for related rules.

Worked Examples

Each example uses the same logic: compute k, subtract k from n-1, and let Excel round. The most common error is skipping the logarithm and using ROUND(A1,n), which rounds decimal places instead of significant figures.

Counter-Examples

• =ROUND(A1,n) rounds to n decimal places, not n significant figures. For 123.456 to 4 sig figs, =ROUND(123.456,4) gives 123.456, not 123.5.
• =ROUND(A1,n-1) fails for numbers of different magnitudes. For 0.004567 to 3 sig figs, =ROUND(0.004567,2) gives 0.00, which is not a three-sig-fig result.
• Ignoring zero: LOG10(0) returns #NUM! in Excel. Use IF(A1=0,0,…).
• Confusing display with value: Changing the number format to show fewer digits does not change the stored value. Rounding changes the value; formatting changes the display.
• Double rounding: Rounding an intermediate result and then rounding again can produce a different final value than rounding once from the original. See Double Rounding Error.
• Assuming Excel matches ASTM E29: Excel ROUND rounds half away from zero, while ASTM E29 and ISO 80000-1 recommend round-half-to-even for exact ties.

Convention Comparison Table

Standards Citation

The relevant standards are clear about rounding once and reporting significant digits. ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, Sections 5 and 6, covers significant digits and rounding. ISO 80000-1:2009, Quantities and units — Part 1: General, Clause 7.3, gives general rounding rules. NIST SP 811, Guide for the Use of the International System of Units (SI), Section 7.9, discusses rounding and significant figures. JCGM 100:2008, the Guide to the Expression of Uncertainty in Measurement (GUM), Clause 7.2.6, addresses significant digits in uncertainty reporting. Excel itself follows ISO/IEC 60559:2020 and IEEE 754 for binary floating-point arithmetic.

Practical rule from the standards: carry extra digits through intermediate calculations, round once at the end, and use scientific notation when trailing zeros must be significant.

Common Mistakes

1. Using ROUND(A1,n) instead of ROUND(A1,n-1-INT(LOG10(ABS(A1)))).
2. Forgetting that INT(LOG10(x)) is not the same as rounding the logarithm. INT floors toward negative infinity, which is correct for powers of 10.
3. Not handling zero or negative inputs. Zero breaks LOG10; negative values need ABS inside the logarithm.
4. Rounding intermediate results and then rounding again, which creates double rounding error.
5. Believing that number formatting changes the underlying value. It does not.
6. Ignoring ambiguous trailing zeros: Excel stores 100 as a number, not as one, two, or three significant figures.
7. Using TEXT to display a rounded value and then performing math on the text result. Keep the numeric value for calculations and use formatting for display.
8. Assuming Excel’s ROUND always matches ASTM E29 or ISO 80000-1 tie-breaking rules.

Software Behavior Note: Excel Floating-Point and Display

Excel stores numbers using IEEE 754 binary floating-point. Many decimal fractions, such as 0.1 and 2.675, cannot be represented exactly in binary. As a result, a value that appears to be an exact half-way case may round in an unexpected direction. This is not a bug in the sig-fig formula; it is a property of binary arithmetic. For critical work, verify tie cases with test values and consider using an add-in or VBA routine that implements ASTM E29 rounding explicitly.

Excel’s Precision as displayed option, found in File > Options > Advanced, permanently changes stored values to match what is displayed. This is rarely appropriate for scientific or engineering work because it can destroy precision in later calculations. Instead, round explicitly with the formula and then format cells as Scientific with the required number of decimal places.

LOG10(0) returns #NUM!, so the zero guard is mandatory. INT floors toward negative infinity, which is why numbers less than 1 produce negative k values and positive decimal-place arguments. For example, 0.004567 has LOG10 = -2.340, INT = -3, so the formula rounds to five decimal places for three significant figures.

Quick Reference Table

Related Rules

• Rounding Rules — the general hierarchy of rounding decisions.
• Multiplication and Division — sig-fig rules for products and quotients.
• Addition and Subtraction — decimal-place rules for sums and differences.
• Scientific Notation — the preferred format for showing significant trailing zeros.
• Rounding vs Significant Figures — why decimal places and sig figs are not the same.
• Banker’s Rounding — round-half-to-even and its role in standards.