To round a number in Excel to a chosen number of significant figures, put the value in A2 and the desired figure count in B1, then use =IF(A2=0,0,ROUND(A2,$B$1-1-INT(LOG10(ABS(A2))))). For example, with 12345 in A2 and 3 in B1, the formula returns 12300; it works for small decimals and negative numbers too.
What significant figures mean
Significant figures, also called significant digits, express the precision of a number rather than a fixed number of decimal places. Starting at the first nonzero digit, count each digit that represents the measured value. In 582.4, all four digits are significant. In 0.005824, the leading zeros only locate the decimal point, so the number has four significant figures. Trailing zeros can be significant when the notation makes that precision clear, as in 5.820.
This distinction matters because a fixed decimal-place rule treats every number at the same scale. Rounding to two decimal places turns 12345 into 12345.00, which adds displayed decimal places but does not reduce the value to two significant figures. Rounding to three significant figures instead gives 12300. For 0.005824, three significant figures gives 0.00582. The number of decimal places needed changes with the size of the input.
How the Excel formula works
Excel’s ROUND function takes a number and a count of decimal places. A positive second argument rounds to the right of the decimal point; a negative argument rounds to the left. To use ROUND for significant figures, Excel first needs to calculate the decimal-place count that corresponds to the number’s size.
ABS(A2)removes the sign temporarily so the size of a negative number can be measured.LOG10(ABS(A2))identifies the number’s order of magnitude. For example, 12345 has a base-10 logarithm between 4 and 5.INT(LOG10(ABS(A2)))gives the whole-number exponent for the value’s magnitude. For 12345 this is 4; for 0.005824 it is -3.$B$1-1-INT(LOG10(ABS(A2)))converts the desired significant-figure count into the decimal-place argument ROUND needs.
The dollar signs make B1 an absolute reference. If you copy the formula down a column, the input reference changes from A2 to A3 and A4, while the requested number of significant figures stays in B1. Enter a positive whole number such as 2, 3, or 4 in B1. The IF part returns zero for a zero input rather than asking LOG10 to evaluate zero, which is undefined.
Set up the worksheet and fill the formula down
A simple layout keeps the precision setting easy to change. Put source values in column A, enter the number of significant figures in B1, and put the formula in C2. Add a heading such as Rounded value in C1 so the result column is clear. When you change B1 from 3 to 2, the formulas recalculate using two significant figures.
- Enter the values to round in A2:A20, or in the range you need.
- Type the desired significant-figure count, such as 3, in B1.
- Enter the formula in C2. With input in A2 and the setting in B1, use
=IF(A2=0,0,ROUND(A2,$B$1-1-INT(LOG10(ABS(A2))))). - Copy C2 down beside the remaining inputs. You can drag the fill handle, or double-click it when the adjacent column has a continuous set of values.
For a fixed precision rather than a setting cell, replace $B$1 with the desired count. With the value in A2 and a fixed target of three significant figures, enter =IF(A2=0,0,ROUND(A2,3-1-INT(LOG10(ABS(A2))))). A cell-based setting is usually more convenient when you need to compare multiple precision levels or update a whole worksheet.
Examples across different number sizes
Here are examples using three significant figures. They show why the formula’s decimal-place argument can be positive, zero, or negative:
- With 12345 in A2 and 3 in B1, the formula returns 12300. ROUND uses -2 decimal places, rounding to the nearest hundred.
- With 12.345 in A2 and 3 in B1, it returns 12.3. ROUND uses 1 decimal place.
- With 0.005824 in A2 and 3 in B1, it returns 0.00582. ROUND uses 5 decimal places; the leading zeros are not counted as significant.
- With -9876 in A2 and 2 in B1, it returns -9900. The magnitude determines the rounding position, while the negative sign remains in the result.
- With 0 in A2 and any positive count in B1, it returns 0. Zero is handled explicitly because it has no first nonzero digit from which to count significant figures.
These are numeric results, not text labels: Excel can still use them in arithmetic. Remember, however, that 12300 alone does not reveal whether the intended precision was two, three, or four significant figures. For reporting, display format or accompanying notation may be needed to communicate the intended precision.
Keep the displayed precision clear
Excel stores the rounded value as a number, and ordinary number formatting does not automatically preserve a chosen count of significant figures. A cell containing 12300 may display as 12,300, even when it was rounded to three significant figures. Conversely, adding zeros after the decimal point with a number format changes only how the value looks; it does not change the stored value or perform significant-figure rounding.
One option is scientific notation. Select the result cells, press Ctrl+1 on Windows or Command+1 in Excel for Mac to open Format Cells, choose the Number tab, select Scientific, and set the decimal places to one fewer than the significant-figure count. For three significant figures, use two decimal places. A value such as 12300 will display as 1.23E+04, which makes its three-digit precision visible. The underlying value remains numeric.
For a text display that adjusts the number of decimal places to the setting, assume the rounded numeric value is in C2 and the significant-figure count is in B1. Use =TEXT(C2,"0"&IF($B$1>1,"."&REPT("0",$B$1-1),"")&"E+00"). With C2 containing 12300 and B1 containing 3, the result is the text 1.23E+04. TEXT creates text, not a numeric value, so keep the numeric result in a separate column if you still need calculations. This display pattern is useful in reports, but it is not a substitute for the ROUND formula.
Common errors and edge cases
The most common mistake is using ROUND(A2,3) and assuming the 3 means three significant figures. It means three decimal places. For 12345, that produces 12345.000, while for 0.005824 it produces 0.006. Both can be very different from rounding to three significant figures.
- Do not use zero as the precision setting. B1 should be a positive whole number. A blank, text value, or negative count is not a meaningful significant-figure target for this formula.
- Do not count leading zeros. In 0.00072, the first significant digit is 7, not one of the zeros. Three significant figures gives 0.000720, though the trailing zero may need explicit formatting or notation to show the intended precision.
- Expect ROUND to change the value. Significant-figure rounding is not merely a display choice. If later calculations should use the original precision, keep the source data in one column and calculate rounded values in another.
- Be aware of halfway cases. Excel ROUND rounds a digit of 5 away from zero: 2.5 rounded to zero decimal places becomes 3, and -2.5 becomes -3. This may differ from a chosen statistical or institutional rounding convention.
- Check imported data near a boundary. Spreadsheet numbers are represented with finite precision. If a value comes from calculations or an import and sits extremely close to a rounding threshold, inspect the source and the unrounded value before treating the last digit as meaningful.
ROUND versus changing decimal places
Use the formula when the rounded value itself must be used in later calculations, exported, or checked. Use a number format when you only need a different visual presentation and want Excel to retain the original value. The Home tab’s Increase Decimal and Decrease Decimal controls change the displayed decimal places; they do not automatically calculate a consistent number of significant figures for values of different magnitudes.
Scientific notation is often the clearest way to show significant figures consistently, especially when a column includes both very large and very small values. A display such as 1.23E+04 indicates three significant digits more clearly than 12300, whose trailing zeros can be ambiguous. If a report requires ordinary decimal notation, consider adding a note stating the precision or using a separate text presentation column, while retaining numeric results for calculations.
Frequently Asked Questions
Can Excel round to significant figures without a formula?
Excel’s standard decimal-place buttons and number formats do not calculate a changing decimal position based on each value’s magnitude. Use ROUND with a magnitude calculation for actual significant-figure rounding, or use scientific notation formatting when you only need to present values clearly.
Does ROUND(A2,3) round to three significant figures?
No. The second argument in ROUND is the number of decimal places, so ROUND(A2,3) rounds to three places after the decimal point. Significant figures require a decimal-place count that depends on the size of A2.
How do I round to two significant figures?
Set B1 to 2 and use the formula from the opening paragraph for values in A2 downward. For example, 9876 rounds to 9900, while 0.005824 rounds to 0.0058.
Can I round negative numbers to significant figures?
Yes. The formula uses ABS to find the magnitude for the rounding position, and ROUND retains the original number’s sign. For example, -9876 rounded to two significant figures becomes -9900.
Why did my rounded result lose trailing zeros?
Excel stores numbers as numeric values and does not treat trailing zeros as part of their numeric identity. Use scientific notation with the required decimal places, or create a text representation for display. Keep the numeric rounded value separately if it will be used in calculations.
Related
- Data Excel Skills: What They Are and How to Build Them
- Excel Data Tips and Tricks for Cleaner, Safer Workbooks
- Record Data in Excel Online: Setup, Sharing and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Split Cell Data After a Comma: ExamplesTo return everything after the first comma in cell A2 in Microsoft 365, use =TEXTAFTER(A2,","). To remove the…
- Excel Formula for Last Updated Date in a Specific CellFor a live current date in Excel, use =TODAY(); for the current date and time, use =NOW(). Neither…
- Excel TRIM LEN Formula: Count Characters After CleanupTo count the characters in text after removing extra spaces, put the text in A2 and enter =LEN(TRIM(A2))…
- Excel Sum Multiple Columns: Formulas, Examples, and TipsTo sum several columns in Excel, use =SUM(B2:D10) for one continuous block of cells. For separate columns or…
- Excel INDEX Function Examples for Everyday LookupsExcel’s INDEX function returns the value at a specified row and column within a range. For example, with…
- Excel Formula for Percentage Increase: Examples and TipsTo calculate percentage increase in Excel, subtract the old value from the new value, then divide by the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.