Spreadsheet How-To
How to Calculate a Confidence Interval in Excel and Google Sheets
Use =CONFIDENCE.T(0.05, STDEV.S(A1:A5), COUNT(A1:A5)) to get the margin of error of a 95% confidence interval for a mean, then subtract it from and add it to =AVERAGE(A1:A5). For the values 4, 8, 6, 5 and 12 the margin is 3.9265 and the interval is 3.0735 to 10.9265. Use CONFIDENCE.NORM only when the population standard deviation is known.
Confidence interval for a mean, step by step
- Enter the data in A1:A5: 4, 8, 6, 5 and 12.
- Mean:
=AVERAGE(A1:A5)returns 7. - Margin of error:
=CONFIDENCE.T(0.05,STDEV.S(A1:A5),COUNT(A1:A5))returns 3.9265. - Lower limit:
=AVERAGE(A1:A5)-CONFIDENCE.T(0.05,STDEV.S(A1:A5),COUNT(A1:A5))returns 3.0735. - Upper limit: the same formula with a plus sign returns 10.9265.
The result reads: we are 95% confident that the population mean lies between 3.07 and 10.93. The interval is wide because the sample is tiny; with the standard deviation 3.1623 and only 5 values the standard error is 1.4142. The confidence interval calculator shows the same numbers with every step.
Without the CONFIDENCE.T function the same margin comes from the t distribution directly:
=T.INV.2T(0.05,COUNT(A1:A5)-1)*STDEV.S(A1:A5)/SQRT(COUNT(A1:A5))
= 2.7764 × 3.1623 / √5 = 3.9265
Excel's help text calls the second argument the population standard deviation. In practice, enter the sample standard deviation STDEV.S: the t multiplier exists to pay for the extra uncertainty of estimating it.
Effect of the confidence level and the sample size
| Confidence level | α to enter | t multiplier (4 df) | Margin of error | Interval |
|---|---|---|---|---|
| 90% | 0.10 | 2.1318 | 3.0149 | 3.9851 to 10.0149 |
| 95% | 0.05 | 2.7764 | 3.9265 | 3.0735 to 10.9265 |
| 99% | 0.01 | 4.6041 | 6.5112 | 0.4888 to 13.5112 |
The t multiplier for 95% confidence shrinks as the sample grows: 2.7764 with 5 values, 2.2622 with 10, 2.0452 with 30 and 1.9842 with 100, approaching the normal value of 1.96. The standard error also falls with √n, which is why quadrupling the sample halves the margin. For a target margin use the sample size calculator.
CONFIDENCE.NORM and the small-sample trap
CONFIDENCE.NORM(alpha, standard_dev, size) uses the normal multiplier, so it is right when the population standard deviation σ is known. If a test is designed so that σ = 15, a sample of 36 has =CONFIDENCE.NORM(0.05,15,36) = 4.8999, and a sample mean of 100 gives the interval 95.10 to 104.90.
Feeding CONFIDENCE.NORM the sample standard deviation is a common mistake. For the five values above it returns 2.7718 instead of 3.9265, an interval about 29% too narrow, and so an overstatement of how well the mean is known.
The Descriptive Statistics tool
In the Analysis ToolPak (see t-test in Excel for how to load it), choose Data Analysis, Descriptive Statistics, tick Summary statistics and Confidence Level for Mean, and enter the level. The output row Confidence Level(95.0%) is the same margin of error as CONFIDENCE.T, 3.9265 for the example. Despite the label it is the margin of error, not the confidence level or the interval.
Confidence interval for a proportion
Excel has no dedicated function for a proportion, but the formula p ± z × √(p(1 − p)/n) needs only NORM.S.INV. If 48 of 400 visitors convert, p = 0.12:
=NORM.S.INV(0.975)*SQRT(0.12*(1-0.12)/400) returns 0.0318
Interval = 0.12 ± 0.0318 = 0.0882 to 0.1518
This is the Wald interval. It works when np and n(1 − p) are both large, but it is unreliable for small samples or proportions near 0 or 1, where the Wilson interval is better; the Wilson interval for this example is 0.0917 to 0.1555. Use the proportion confidence interval calculator for other cases.
Mistakes to avoid
- Entering the confidence level instead of α. CONFIDENCE.T(0.95,…) asks for a 5% confidence level and returns a very narrow interval. The first argument is 0.05 for a 95% interval.
- Using STDEV.P. For a sample use STDEV.S; see standard deviation in Excel.
- Mixing up standard deviation and standard error. The interval uses the standard error, s/√n, which the function computes for you; see standard error vs standard deviation.
- Reading the interval as containing 95% of the data. It is an interval for the mean, not for individual values; see confidence intervals explained.
Try the Confidence Interval Calculator
Get a confidence interval for a mean from raw data or summary statistics, with every step shown.
Frequently Asked Questions
What does CONFIDENCE.T return?
It returns the margin of error, the half-width of the interval, and not the interval itself: t* × s/√n. Subtract it from the sample mean for the lower limit and add it for the upper limit. For the values 4, 8, 6, 5, 12 it returns 3.9265, so the 95% interval is 3.0735 to 10.9265.
What is the difference between CONFIDENCE, CONFIDENCE.NORM and CONFIDENCE.T?
CONFIDENCE is the older name of CONFIDENCE.NORM. Both multiply the standard error by a normal (z) multiplier, which is right when the population standard deviation is known. CONFIDENCE.T multiplies by a t multiplier with n − 1 degrees of freedom, which is right when, as usual, the standard deviation is estimated from the sample.
How do I get a 90% or 99% confidence interval?
The first argument is α, which is 1 minus the confidence level: 0.10 for 90%, 0.05 for 95% and 0.01 for 99%. For the values 4, 8, 6, 5, 12 the margin grows from 3.0149 at 90% to 3.9265 at 95% and 6.5112 at 99%. A higher confidence level always gives a wider interval.
Does a 95% confidence interval mean there is a 95% probability the true mean is inside it?
Not for one computed interval. The 95% describes the method: if you repeated the study many times and built an interval each time, about 95% of those intervals would contain the true mean. Any single interval either contains it or it does not, and the probability language only applies before you collect the data.
Why is my confidence interval so wide?
Because the margin of error is t* × s/√n, so a large standard deviation, a small sample or a high confidence level all widen it. To halve the width you need about four times as many observations. The sample size calculator shows how many you need for a target margin.
Are the functions the same in Google Sheets?
Yes. Google Sheets has CONFIDENCE.T(alpha, standard_deviation, size) and CONFIDENCE.NORM(alpha, standard_deviation, size) with the same meaning, and the older CONFIDENCE for the normal version. The Descriptive Statistics tool in the Analysis ToolPak is an Excel-only add-in.