How to calculate standard error in Excel

The standard error of the mean formula

The sheet holds delivery times for 12 orders from a test week. One cell is blank (the order was cancelled) and one says n/a, so there are 10 real times with an average of 36.6 minutes. The question a manager actually asks is how much to trust that 36.6: would another week give 30 or 43?

The standard error answers it. Standard deviation (4.93 minutes here) describes how much single deliveries vary. Standard error describes how much the average itself would vary from sample to sample, and it is the standard deviation divided by the square root of the sample size. =SQRT(VAR.S(B2:B13)/COUNT(B2:B13)) gives the same number.

Because of the square root, more data helps slowly: four times as many orders roughly halves the standard error, it does not divide it by four.

Get n right: COUNT, not COUNTA or ROWS

STDEV.S ignores the blank and the n/a text, so it works on 10 values. The count has to match. COUNT counts only numbers and returns 10. COUNTA counts the n/a as well and returns 11, and ROWS(B2:B13) returns 12 whatever is in the cells.

Either mistake makes the standard error look smaller and the result more certain than it is: 1.49 with COUNTA and 1.42 with ROWS instead of 1.56. The error grows with the amount of junk in the column, and nothing flags it.

Watch for zeros typed in place of missing data. COUNT and STDEV.S both treat 0 as a real delivery of zero minutes, which drags the average down and inflates the spread. Leave missing values blank.

Why STDEV.S and not STDEV.P

A standard error only makes sense when your numbers are a sample of something bigger (all future deliveries, all customers), because it measures how far a sample average might be from the population average. That is exactly when STDEV.S, which divides by n - 1, is the right standard deviation.

Using STDEV.P gives about 1.48 here instead of 1.56. The difference shrinks as the sample grows, but on small samples STDEV.P understates the uncertainty. If your range really is the entire population, there is no sampling error to measure and you do not need a standard error at all. The older names STDEV and STDEVP give the same results as STDEV.S and STDEV.P.

Margin of error and a 95% confidence interval

To turn the standard error into a range, multiply it by a critical value. For an average from a small sample that is a t value, which depends on the degrees of freedom (n - 1) and on the confidence level. With 10 values there are 9 degrees of freedom, and the 95% t value is about 2.262, typed into E2. Get it from a t table, or in Excel with =T.INV.2T(0.05,COUNT(B2:B13)-1).

The margin is about 3.52 minutes, so the 95% interval runs from about 33.08 to 40.12 minutes. In Excel 365, =LET(n,COUNT(B2:B13),se,STDEV.S(B2:B13)/SQRT(n),m,AVERAGE(B2:B13),HSTACK(m-E2*se,m+E2*se)) returns both ends side by side.

Excel's CONFIDENCE.T(0.05,STDEV.S(B2:B13),COUNT(B2:B13)) returns this margin in one step, without the t cell. Do not use 1.96 or CONFIDENCE.NORM on a sample this small: 1.96 is the normal-curve value, and here it gives a margin of 3.05, too narrow. The t value approaches 1.96 as the sample gets large.

Standard error of a proportion

For yes/no data, such as a survey where 247 of 380 people would recommend you, the standard error is SQRT(p*(1-p)/n), with p the share who said yes (65%). If you have the raw answers in a column, get p with =COUNTIF(C2:C381,"Yes")/COUNTA(C2:C381).

Multiply by 1.96 (typed in I2) for a 95% margin: about 0.048, or 4.8 percentage points, so the interval is roughly 60.2% to 69.8%. This uses the normal approximation, which is fine for a survey this size but rough when the yes or no count is small (fewer than about 10 of either). Quadrupling the responses would halve the standard error to about 0.0122.

Error bars and the Analysis ToolPak

Charts have a built-in option: select the chart, click Chart Elements (+) > Error Bars > Standard Error. Be careful, it computes one standard error from all the values in the plotted series and draws the same bar on every point. It does not use the standard error you calculated for each group. For per-group bars, put your standard errors in cells and choose Error Bars > More Options > Custom > Specify Value.

For a quick summary, Data > Data Analysis > Descriptive Statistics (after enabling the Analysis ToolPak add-in) lists Standard Error with the mean, and ticking Confidence Level for Mean adds the margin of error. The output is static values, so it does not update when the data changes; the formulas above do.