Uncertainty in Excel: Standard Error and Confidence

To estimate uncertainty from repeated measurements in Excel, calculate the mean with =AVERAGE(A2:A6), the sample standard deviation with =STDEV.S(A2:A6), and the standard error by dividing the standard deviation by the square root of the number of readings. For a confidence interval, use Excel’s CONFIDENCE.T function. These steps quantify how precisely your sample estimates an underlying average; the right measure depends on what you mean by “uncertainty.”

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web, though ribbon locations can differ slightly.
A set of repeated, numeric measurements in one column. The example uses five readings in cells A2:A6.
If your readings come from an instrument, its stated accuracy or tolerance may also be useful. Statistical variation among readings and instrument accuracy are different sources of uncertainty.

Step-by-Step

Decide what you want to describe. “Uncertainty” is not one universal Excel statistic. The standard deviation describes how spread out individual readings are. The standard error describes how uncertain the sample mean is. A confidence interval gives a range of plausible values for the population mean under stated assumptions. Instrument tolerance, such as ±0.2 units, is a separate specification. For the worked example, the goal is a 95% confidence interval for the mean of repeated readings.
Enter and check the measurements. Put one independent reading in each cell from A2 through A6, with no heading or text in that range. Use these example values: 10, 12, 11, 13, and 12. Check that all values use the same units and were collected using a consistent method. Do not include a summary, blank placeholder, or text such as “12 mm” in the measurement range; put units in a heading or a neighboring column instead.
Calculate the sample mean. With the readings in A2:A6, enter this formula in B2: =AVERAGE(A2:A6). It returns 11.6. This is the central estimate, in the same units as the readings. The mean is useful for summarizing the data, but it does not show how much the readings vary or how precisely the mean is estimated.
Calculate the sample standard deviation. With the readings in A2:A6, enter this formula in B3: =STDEV.S(A2:A6). It returns approximately 1.1402. This describes the spread of the individual readings around their mean. Use STDEV.S when your measurements are a sample from a larger process or population, which is the usual case. STDEV.P is for a complete population, not simply because you have several observations.
Count the valid readings. With the readings in A2:A6, enter this formula in B4: =COUNT(A2:A6). It returns 5. COUNT includes numeric cells and ignores blanks and text. Check the result against the number of measurements you intended to analyze; a smaller count may mean some entries are stored as text or missing.
Find the standard error of the mean. With the sample standard deviation in B3 and the number of readings in B4, enter this formula in B5: =B3/SQRT(B4). It returns approximately 0.5099. This is the estimated standard error (SE) of the mean. It is smaller than the standard deviation because averaging multiple readings generally makes the estimated mean more precise. It does not mean that most individual measurements fall within 0.5099 of the mean.
Calculate the 95% confidence interval margin. With the sample standard deviation in B3 and the count in B4, enter this formula in B6: =CONFIDENCE.T(0.05,B3,B4). It returns approximately 1.4157. The first argument, 0.05, means a 5% significance level, corresponding to a two-sided 95% confidence interval. Excel uses the t distribution, which accounts for estimating the population standard deviation from a sample. This function returns the margin of error, not either endpoint.
Calculate the lower and upper endpoints. With the mean in B2 and the margin of error in B6, enter =B2-B6 in B7 for the lower endpoint; it returns approximately 10.1843. With the same values in B2 and B6, enter =B2+B6 in B8 for the upper endpoint; it returns approximately 13.0157. Report the result as a mean of 11.6 and a 95% confidence interval of about 10.18 to 13.02, in the original measurement units. Round to a precision that makes sense for the measuring method.
Interpret the result in context. The interval describes uncertainty about the population mean, assuming the readings are independent and reasonably representative and the t-based method is appropriate. In repeated sampling, this procedure would produce intervals that contain the true population mean about 95% of the time. It does not say there is a 95% probability that this particular fixed mean lies inside this already calculated interval, and it does not claim that 95% of individual readings fall inside the interval.
Optional: show uncertainty on a chart. Create a chart of the means you want to compare, select it, then use Chart Design > Add Chart Element > Error Bars > More Error Bars Options. In the Format Error Bars pane, choose a built-in option such as Standard Error or, where appropriate, Custom and Specify Value to select cells containing your positive and negative error amounts. Label the chart and its error bars clearly: an error bar is only informative when readers know whether it represents standard deviation, standard error, a confidence interval, or another quantity. On Mac, the Chart Design tab is also available, but its ribbon layout may differ. Excel for the web may offer fewer chart-formatting controls; open the workbook in the desktop app if you cannot find the custom error-bar settings.

Common Mistakes

Mixing up standard deviation and standard error. Standard deviation describes variation among readings; standard error describes the precision of an estimated mean. They answer different questions, so do not label one as the other.
Treating a confidence interval as a range for individual observations. The interval above concerns the population mean. To describe the spread of future individual values, you need a prediction interval or another method, not just the confidence interval for the mean.
Using the wrong standard deviation function. Most experimental or survey data are samples, so use STDEV.S. Use STDEV.P only when the data contain the entire population you intend to describe.
Ignoring data quality or dependence. Duplicate entries, transcription errors, changing measurement conditions, or repeated readings that are not independent can make the calculated uncertainty misleading. A formula cannot correct a flawed sampling method.
Confusing statistical uncertainty with instrument tolerance. A device’s stated accuracy is not automatically the same as the spread in repeated readings or the standard error. If both matter, document them separately and use a suitable uncertainty-propagation method for your measurement model.
Reporting too many digits. Excel may display many decimals, but that does not mean the measurements support that precision. Round the interval and estimate consistently, and keep the unrounded values in the calculation cells.

Tips

Give columns clear headings such as “Reading (mm),” “Mean,” “Standard deviation,” and “95% CI margin.” Put units in headings rather than typing them into numeric cells.
Keep the raw readings unchanged and place calculations in separate cells. This makes it easier to check the work and update results when another reading is added.
Use a confidence level that fits the purpose of the analysis. For example, a 90% or 99% interval uses a different alpha value; explain the selected level rather than presenting it without context.
More observations often reduce the standard error, but adding measurements does not eliminate systematic bias, poor calibration, or an unrepresentative sample.
Excel formulas here use commas between arguments. Some regional settings expect semicolons instead; if Excel rejects a formula, use the argument separator configured for your installation.
For results that will be shared, state the number of readings, the statistic, the confidence level, the units, and any important assumptions. For example: “Five readings; mean 11.6 mm; 95% t confidence interval 10.18–13.02 mm.”

Troubleshooting

Excel shows #DIV/0! or an error in the confidence formula: Check that B4 is greater than 1 and that B3 contains a numeric sample standard deviation. A confidence interval cannot be estimated from one reading.
The result is unexpectedly zero or the count is too low: Check whether measurements were pasted as text. Select a suspicious cell and inspect the formula bar; re-enter the value as a number or use Data > Text to Columns to convert a consistently formatted column. Confirm that decimal separators match your regional settings.
The confidence interval looks extremely wide: Review the raw values for outliers or mixed units, then check the sample size and standard deviation. A wide interval may be a valid warning that the mean is imprecise; do not narrow it by deleting inconvenient readings without a defensible reason.
The formula name is not recognized: Confirm that the function is typed as CONFIDENCE.T and that the formula begins with an equals sign. In older Excel versions, the legacy CONFIDENCE function may be available, but it uses the normal distribution rather than the t distribution; use current Excel where possible, especially with small samples.
Error bars do not appear or look wrong: Make sure a chart is selected before using Chart Design. For custom bars, check that the selected positive and negative value ranges contain nonnegative numeric margins and have the appropriate number of entries for the plotted points. If Excel applies a single standard error to a chart whose points have different uncertainties, select custom ranges instead.
There is no meaningful variation in the readings: If all measurements are identical, the calculated sample standard deviation and interval margin may be zero. That can reflect rounding or limited instrument resolution rather than perfect certainty. Check the original precision and instrument specifications before interpreting a zero-width interval.

Frequently Asked Questions

Does Excel have an uncertainty function?
Excel does not have one universal function called UNCERTAINTY. Use functions such as STDEV.S, CONFIDENCE.T, and arithmetic formulas according to whether you need spread, standard error, a confidence interval, or a different uncertainty measure.

Is standard error the same as margin of error?
No. Standard error is an estimated standard deviation of the sample mean. A margin of error multiplies that error by a critical value for a chosen confidence level. In this example, the standard error is about 0.5099 and the 95% margin is about 1.4157.

How many measurements do I need?
There is no single number that works for every experiment. The needed sample size depends on the expected variation and the precision you need. A small sample can still produce a valid t interval under suitable conditions, but it may be wide and sensitive to unusual observations.

Can I calculate uncertainty from just one measurement?
One value is not enough to estimate variation among repeated readings with a sample standard deviation. You may be able to use a known instrument specification or a model-based uncertainty, but that requires information beyond the single value.

Should I use 1.96 times the standard error?
That is a common large-sample normal approximation for a two-sided 95% interval. For a small sample with an estimated standard deviation, use a t-based interval such as CONFIDENCE.T, as in the example, because its critical value accounts for the sample size.

Can Excel calculate uncertainty for a measurement made with several inputs?
Excel can calculate a result from multiple inputs, but the uncertainty of that result depends on how the inputs affect it and whether their errors are related. Write down the measurement model and uncertainty assumptions first; a simple standard error formula for repeated readings is not automatically appropriate for every calculation.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.