Skip to main content
v2026.11,986 entries · CC-BY 4.0

Significant Figures in Excel: Formula and Display

Round Excel results to a chosen significant-figure count, show trailing zeros, and keep source values separate from reporting outputs.

Written and maintained by CASRAI Editorial Board

Published

On this page

To round significant figures in Excel, convert the requested digit count into a decimal-place argument for ROUND. For a numeric value in A2 and a positive integer digit count in B1, use =IF(A2=0,0,ROUND(A2,$B$1-1-INT(LOG10(ABS(A2))))). Keep the source value separately, then format the result to show any significant trailing zeros.

A research workbook needs to distinguish the value supplied to later calculations from the digits printed in a table. The workflow below keeps both visible, adds a difference check, and avoids changing the workbook’s stored inputs through a display setting. The data table below compares the consequences of four rounding and display methods.

What it is used for in research

Significant-figure rounding expresses a nonzero number with a chosen number of digits starting at its first nonzero digit. Decimal-place rounding instead specifies a position relative to the decimal point. Thus, three significant figures can require different decimal-place arguments for values of different magnitudes.

For an illustrative example, 48.276 rounds to 48.3 at three significant figures, whereas 0.0048276 rounds to 0.00483. The first result needs one decimal place; the second needs five. Entering =ROUND(A2,3) for both would request three decimal places, which is a different task.

Microsoft’s ROUND documentation defines the decimal-place argument; its math-function reference defines the functions used here to derive that argument. The composition implements a selected reporting rule. It does not determine how many digits a particular measurement warrants. Record the chosen count alongside the formula rather than treating it as a property Excel can recover from the numeric value alone.

For research reporting, decide explicitly whether subsequent calculations should reference the original value or the rounded result. This is an editorial workflow choice: separate columns make it visible and reversible. Statistical analysis itself is a separate task; the Excel statistical analysis workflow covers analysis tools rather than numerical presentation.

Install or access Excel

The formulas use functions documented by Microsoft for Excel. Open an existing Excel workbook, or use the official Microsoft Excel page’s Open Excel link to reach the web application. Microsoft also lists a free web option. The calculation does not call an Analysis ToolPak command, macro or external add-in.

The ribbon instructions below follow Microsoft’s documentation for the desktop Format Cells dialog. Keep the formula and the display operation separate when using another interface. The essential calculation consists of ordinary cell formulas; changing the appearance of their results is a later step.

Start with numeric inputs and a positive whole-number count in B1. Three is the illustrative choice used below, rather than a default research reporting requirement. Preserve the unrounded source column so that changing B1 can regenerate results from the same inputs.

Step-by-step task: Excel significant figures

Significant figures in Excel with a numeric result

The formula returns a rounded number. Its IF branch returns zero for a zero input; for other inputs, ABS, LOG10 and INT calculate where the leading digit lies before ROUND changes the value.

  1. Enter 3 in B1 as the significant-figure count. Put the heading Source value in A1 and the heading Rounded result in C1. Enter 48.276 in A2. These are illustrative inputs, not observations from a study.
  2. In C2, enter =IF(A2=0,0,ROUND(A2,$B$1-1-INT(LOG10(ABS(A2))))). The expected numeric result is 48.3. The reference $B$1 keeps the chosen count fixed when the formula is copied to another row.
  3. Enter Difference in D1 and =C2-A2 in D2. The illustrative difference is 0.024. This column exposes the numerical change produced by rounding, even when the source and result have similar displays.
  4. For another example, enter 0.0048276 in A3 and the same formula in C3 with A3 replacing A2. Enter =C3-A3 in D3. The rounded value is 0.00483 and the difference is 0.0000024.
  5. Repeat for the rows being reported. Keep the source reference relative, such as A3, and the count reference fixed at $B$1. Check which column any totals, derived values or exported results actually reference.
  6. Apply the desired number format to the rounded-result cells using the output instructions below. Formatting is needed to communicate trailing zeros; it is separate from creating the numeric result.

This layout is deliberately inspectable. A reader can identify the source, the requested count, the rounded output and the change without reconstructing a long chain of formulas. If a report requires a different count, change B1 and review the resulting values and displays together.

How the decimal-place calculation works

For a nonzero input x and requested count n, let e = INT(LOG10(ABS(x))). Then the decimal-place argument is d = n − 1 − e. This follows from the position of the first significant digit: its power of ten is e, and the nth retained digit lies n − 1 positions to its right.

For 48.276, LOG10 is approximately 1.6837, so INT returns 1. With n = 3, d = 3 − 1 − 1 = 1, giving ROUND(48.276,1) = 48.3. For 0.0048276, LOG10 is approximately −2.3163; INT rounds downward to −3. Consequently, d = 3 − 1 − (−3) = 5.

The negative logarithm explains why INT matters. Truncating −2.3163 toward zero would give −2 and request four decimal places, producing 0.0048 instead. Microsoft’s function reference distinguishes INT’s downward rounding from TRUNC’s truncation. ABS supplies a positive magnitude for a negative input, while the original signed input remains the first argument to ROUND.

Output and how to read it

Significant figures on Excel displays

A rounded number and its displayed notation carry different information. The numeric result 48.3 can be displayed in several ways, while a result such as numeric 10 needs an appropriate display to communicate a requested count of three significant figures.

Use Microsoft’s scientific-format controls to make the coefficient’s digits visible:

  1. Select the rounded-result cells.
  2. On Home, in the Number group, select the small More button to open Format Cells.
  3. In the Category list, select Scientific.
  4. Set Decimal places to 2 for three significant figures in a nonzero scientific coefficient, and inspect the Sample preview.

Scientific notation has one coefficient digit before the decimal point. Two digits after it therefore display three significant figures: 48.3 appears as 4.83E+01, and 0.00483 appears as 4.83E-03. Here E indicates multiplication by a power of ten. The digit count in B1 does not automatically update this formatting choice; review Decimal places if you change the requested count.

Microsoft states that number formatting does not change the actual value used for calculation. Formatting the original 48.276 to display 4.83E+01 therefore leaves its original stored value available to dependent formulas. By contrast, a formula referencing C2 consumes the rounded numeric result 48.3. Use the difference column to demonstrate which operation has happened.

Reading a rounding carry and a zero

As an illustrative boundary example, 9.999 rounded to three significant figures gives numeric 10. Displaying that result as 1.00E+01 communicates three digits without relying on an ambiguous unformatted integer. The carry into a new power of ten is a reason to inspect the final display, rather than counting digits in the original input.

Zero is handled separately because the magnitude calculation uses a logarithm, which is undefined at zero. The formula returns numeric zero without that calculation. Its displayed zeros do not establish a measurement’s uncertainty or detection limit. Keep that contextual information separately instead of trying to infer it from the returned number.

Troubleshooting significant figures Excel formulas

Small decimals, negative values and blanks

If a small positive value loses an extra digit, inspect the exponent calculation for TRUNC in place of INT. For an illustrative negative value, −48.276 should round to −48.3 with n = 3: ABS is used only inside LOG10, so the output retains its sign.

The basic formula assumes a numeric, nonblank source cell. If blank source cells must remain visually blank, use a separate IF check before the numeric calculation: =IF(A2=””,””,IF(A2=0,0,ROUND(A2,$B$1-1-INT(LOG10(ABS(A2)))))). Microsoft’s IF documentation supports comparisons, nested conditions and empty-text results. This optional branch returns empty text, so distinguish it from a numeric zero in any later processing.

Do not use the zero branch to represent missing observations. A true zero is a value; an empty cell is an absence that your data workflow must interpret. The distinction is especially visible when the same workbook contains both cases. Check imported cell types before applying a formula intended for numeric data.

Rounding direction and precision settings

ROUND, ROUNDUP and ROUNDDOWN are different operations. Microsoft’s documentation describes ROUNDUP as moving away from zero and ROUNDDOWN as moving toward zero. Thus, a negative number being rounded “down” does not necessarily become more negative. Use the operation specified by your reporting method, rather than substituting a similarly named function.

Check exact halfway cases against the required rule as well. Microsoft’s ROUND examples include ROUND(2.15,1) = 2.2 and ROUND(−1.475,2) = −1.48. Those documented results are useful comparison cases when a collaborator uses another tool. The R rounding and significant-digit functions guide explains the corresponding R workflow; do not assume different tools share every rounding convention.

Avoid using Set precision as displayed as a shortcut for this separate-column workflow. Microsoft states that the setting permanently changes stored values to their displayed precision. Returning to full precision does not restore the original underlying values. Ordinary display formatting and explicit result formulas have different consequences, summarized in the data table below.

Microsoft also documents a limit of 15 significant digits for Excel’s stored calculations. Increasing a display’s digit count does not create additional stored precision. For inputs or calculations near a rounding boundary, inspect both the formula bar and the actual result instead of relying solely on an apparently tidy cell display.

Checks before handing over a research workbook

Use a small set of illustrative checks that exercises the workflow: a value above one, a small decimal, a negative value, a true zero, an empty cell and a carry into a new power of ten. Confirm the chosen count, formula references and displayed coefficient digits for each applicable case.

Then trace a downstream calculation to its source column. A formula using the original input and one using the rounded result answer different numerical tasks, even if their cells currently look identical. When moving the analysis into Python, review the pandas decimal-place rounding guide for that operation’s semantics rather than copying a digit argument between tools without checking it.

Free alternatives to desktop Excel

Microsoft provides a web option, and The Document Foundation provides LibreOffice Calc as part of its free, open-source suite. Their official pages establish access and documented functions. The table compares routes for this rounding task rather than making a general software ranking.

Option Documented route What to check
Excel for the web Use Microsoft’s official Excel page to access the web application. Keep the calculation and display review separate; the desktop ribbon sequence above follows the desktop documentation.
LibreOffice Calc Use ROUNDSIG(Value;Digits). Its help gives =ROUNDSIG(123.456789;5) returning 123.46. Digits must be a positive integer. The help identifies this function as outside the ODF 1.3 OpenFormula standard, so check formulas when exchanging workbooks.

Calc’s direct significant-digit argument differs from Excel ROUND’s decimal-place argument. Treat a migration as a translation of the operation, with the illustrative checks repeated, rather than a function-name substitution. Retain the source inputs and requested count with whichever output you share.

Sources

Data: Significant-digit workflows: numeric results and display effects

Original comparison compiled from Microsoft function, scientific-format and workbook-precision documentation and LibreOffice ROUNDSIG help. It separates each operation's parameter, numeric effect and implication for original inputs; these are documented software behaviors, not measured performance results.

Significant-digit workflows: numeric results and display effects
OperationParameter or controlNumeric effectEffect on original inputSource document
Excel ROUNDnum_digits specifies decimal places; negative values round left of the decimalReturns a rounded numeric resultA result formula in a separate cell leaves the referenced input availableMicrosoft Support: ROUND function
Excel Scientific formatDecimal places controls digits after the coefficient decimal pointChanges the display, not the value used in calculationsUnderlying cell value remains unchangedMicrosoft Support: Scientific notation
Excel Set precision as displayedWorkbook calculation precision settingUses displayed precision and permanently changes stored valuesReturning to full precision does not restore the original valuesMicrosoft Support: Calculation precision
Calc ROUNDSIGDigits is a positive integer count of significant decimal digitsReturns a number rounded to the specified significant-digit countFunction result is a rounded number; function is outside ODF 1.3 OpenFormulaLibreOffice Help: ROUNDSIG

Sources

Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.

Follow CASRAI

Research-administration guidance, standards updates and independent tool reviews.

Ask CASRAI · free to try

Ask about Significant Figures in Excel: Formula and Display

2 free questions a day, no account.

An AI assistant for research: funder rules, compliance, methods and publishing. It links the sources behind its answers and labels answers that come from a web search.

Answers draw on CASRAI's guides and dictionary plus the federal and funder documents we index: Federal Register, Grants.gov, Regulations.gov and UKRI.

Works on this site and inside Claude, Cursor and the AI tools you already use.

Everything CASRAI publishes — this page, the dictionary, the guides and the news — stays open to read, with no account and no card.

Referenced across the research world

University of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logoUniversity of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logo
  • University of Cambridge logo
  • Columbia University logo
  • Crossref logo
  • University of Edinburgh logo
  • Harvard University logo
  • University of Oxford logo
  • Princeton University logo
  • Stanford School of Medicine logo
  • University College London logo
  • ORCID logo

View CASRAI adoption →