How to Calculate a Confidence Interval in Excel: CONFIDENCE.T and CONFIDENCE.NORM

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate a Confidence Interval in Excel: CONFIDENCE.T and CONFIDENCE.NORM - featured image

A confidence interval gives a range of values that is likely to contain the true value of a population figure, such as an average, based on a sample. Instead of reporting only that the average score in your sample is 70, you can say that you are 95 percent confident that the true average lies between 66.1 and 73.9. This shows how precise your estimate is. In Excel you can calculate a confidence interval for a mean with the CONFIDENCE.NORM and CONFIDENCE.T functions, or by building it from the standard error. This guide explains the idea in plain words, shows both functions with a worked example that you can verify, covers different confidence levels and sample sizes, shows an interval for a proportion and lists the common mistakes.

Updated November 2026

In this guide

  1. What a confidence interval means
  2. The formula
  3. Example data
  4. Method 1: CONFIDENCE.NORM
  5. Method 2: CONFIDENCE.T
  6. Build the interval from the data
  7. Choose the confidence level
  8. The effect of sample size
  9. How big a sample do you need?
  10. Confidence interval for a proportion
  11. How to read and report the interval
  12. Show the interval in a chart
  13. Common mistakes
  14. Practice problems
  15. Quick recap

What a confidence interval means

A sample is a small group taken from a larger population. The average of a sample is unlikely to equal the population average exactly, because of random variation. A 95 percent confidence interval is built with a method that, if you repeated the sampling many times, would capture the true population average in about 95 percent of the intervals. It does not mean that there is a 95 percent probability that the true value is in one particular interval after it is calculated, but in practice people read it as the range of plausible values.

The formula

The interval is the sample mean plus or minus the margin of error. The margin of error is a critical value times the standard error. The standard error of the mean is the sample standard deviation divided by the square root of the sample size. For 95 percent confidence with a large sample, the critical value from the normal distribution is about 1.96. So the interval is mean ± 1.96 × (s / √n). For small samples, you use the t distribution, which gives a slightly larger critical value.

Example data

A teacher measures the test scores of a sample of 36 students. The sample mean is 70, and the sample standard deviation is 12. The standard error is 12 divided by the square root of 36, which is 12 divided by 6, so 2. For a 95 percent interval with the normal critical value of 1.96, the margin of error is 1.96 times 2, which is 3.92. The interval is 70 minus 3.92 to 70 plus 3.92, which is 66.08 to 73.92.

Method 1: CONFIDENCE.NORM

The function CONFIDENCE.NORM(alpha, standard_dev, size) returns the margin of error using the normal distribution. Alpha is 1 minus the confidence level, so it is 0.05 for 95 percent. For our example, =CONFIDENCE.NORM(0.05,12,36) returns 3.92. The lower limit is =70-CONFIDENCE.NORM(0.05,12,36), which is 66.08, and the upper limit is 73.92. Use this function when the population standard deviation is known, or when the sample is large, usually at least 30.

Method 2: CONFIDENCE.T

CONFIDENCE.T(alpha, standard_dev, size) uses the t distribution, which is better when you estimate the standard deviation from the sample, especially for small samples. For our example, =CONFIDENCE.T(0.05,12,36) returns about 4.06. The critical value is 2.0301 for 35 degrees of freedom, and 2.0301 times 2 is 4.06. The interval is 65.94 to 74.06, slightly wider than the normal version. The extra width reflects the uncertainty in the standard deviation. For small samples, CONFIDENCE.T is the correct choice.

Build the interval from the data

With raw data in A2:A37, calculate the pieces with formulas. The mean is =AVERAGE(A2:A37). The standard deviation is =STDEV.S(A2:A37). The sample size is =COUNT(A2:A37). The standard error is the standard deviation divided by SQRT(n). The critical value is =T.INV.2T(0.05,n-1), which is 2.0301 for 36 values. The margin is the critical value times the standard error. Showing each step in its own cell makes the work easy to check. Our standard deviation guide explains STDEV.S.

Choose the confidence level

The confidence level sets the width of the interval. A higher level gives a wider interval. For our example with a standard error of 2, the normal critical values give these margins.

Confidence levelAlphaCritical valueMargin of error
90%0.101.6453.29
95%0.051.9603.92
99%0.012.5765.15

The 99 percent interval is wider, because you need to be more sure. The 95 percent level is the most common choice.

The effect of sample size

A larger sample gives a smaller standard error and a narrower interval. If the sample were 100 students instead of 36, with the same standard deviation of 12, the standard error would be 12 divided by 10, which is 1.2. The margin at 95 percent would be 1.96 times 1.2, which is 2.35, compared with 3.92 for 36 students. The interval would be 67.65 to 72.35. Because the standard error uses the square root of the sample size, you need four times as many observations to cut the margin in half.

How big a sample do you need?

You can work backward to plan a survey. For a target margin of error of 2 points with a standard deviation of 12 at 95 percent confidence, the sample size is (1.96 times 12 divided by 2) squared. That is 11.76 squared, which is 138.3, so you need at least 139 observations. In Excel use =ROUNDUP((1.96*12/2)^2,0). This is only an estimate, since you need a guess of the standard deviation from earlier data or a small pilot study. Round up, never down, so the margin is at most the target.

Confidence interval for a proportion

For a percentage, such as the share of customers who are satisfied, the standard error is the square root of p times 1 minus p, divided by n. If 40 percent of 200 respondents answered yes, the standard error is the square root of 0.4 times 0.6 divided by 200, which is 0.0346. The 95 percent margin is 1.96 times 0.0346, which is 0.0679. The interval is 0.4 minus 0.0679 to 0.4 plus 0.0679, which is 33.2 percent to 46.8 percent. In Excel use =SQRT(0.4*0.6/200) and =NORM.S.INV(0.975) for the critical value.

How to read and report the interval

A narrow interval means a precise estimate, and a wide one means a lot of uncertainty. Report the sample size, the confidence level and the interval, for example mean 70, 95 percent confidence interval 65.9 to 74.1, n equal to 36. If two intervals do not overlap, the difference between the groups is probably real. If they overlap a lot, the difference may be due to chance. Use the interval together with the context, since a statistically clear difference may still be too small to matter in practice.

Show the interval in a chart

You can add error bars to a column or line chart to show the interval. Select the chart, choose Chart Elements, Error Bars, More Options, and then Custom, and select the cell with the margin of error for the plus and minus values. The bars show the range around each mean at a glance. This works well for comparing the means of several groups, as in our bar graph guide. Keep the chart simple and state the confidence level in the title or in a note.

Common mistakes

The most common mistake is to use the standard deviation in place of the standard error, which makes the interval much too wide. Another is to use CONFIDENCE.NORM for a small sample. A third is to enter 0.95 as alpha instead of 0.05. A fourth is to forget that the functions return only the margin of error, and not the limits. A fifth is to read the interval as the range of the individual values, when it is a range for the mean. A sixth is to use biased or tiny samples, where no formula can fix the problem.

Practice problems

Try these. Problem 1: a sample of 49 with a standard deviation of 14 has a standard error of 2. Problem 2: with a mean of 100, the 95 percent normal margin is 3.92, so the interval is 96.08 to 103.92. Problem 3: the 90 percent margin with the same standard error is 3.29. Problem 4: the sample size for a margin of 3 with a standard deviation of 12 is (1.96 times 12 divided by 3) squared, which is 61.5, so 62. Problem 5: calculate the interval for your own data.

Quick recap

A confidence interval is the sample mean plus or minus the margin of error. Use CONFIDENCE.T for small samples and an unknown standard deviation, and CONFIDENCE.NORM for large samples. Alpha is 1 minus the confidence level, so 0.05 gives 95 percent. The margin shrinks as the sample grows and widens with a higher confidence level. You can plan the sample size, build an interval for a proportion and show the interval with error bars. Report the level, the size and the limits every time.

Frequently asked questions

How do I calculate a confidence interval in Excel?

Use the mean plus and minus the result of CONFIDENCE.T or CONFIDENCE.NORM.

What is alpha for a 95% confidence interval?

Alpha is 0.05.

What is the difference between CONFIDENCE.T and CONFIDENCE.NORM?

CONFIDENCE.T uses the t distribution, which suits small samples.

What is the margin of error?

The critical value times the standard error.

How can I make the interval narrower?

Use a larger sample or a lower confidence level.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *