How to Calculate a Confidence Interval in Excel

A confidence interval in Excel takes three numbers from your data (the average, the standard deviation and the count) and one function, CONFIDENCE.T, which returns the margin of error. The interval is the average minus that margin to the average plus it. This guide is for students, analysts and anyone reporting survey or test results who needs to say how precise an average is.

What a confidence interval tells you#

A confidence interval is a range around a sample average that likely contains the true average of the whole group. You measure a sample (some of the orders, some of the customers) because you can't measure everything. The interval shows how far off the sample average might be.

It has two parts:

• The point estimate: the sample mean, from AVERAGE.
• The margin of error: how far to go either side, which depends on the spread of the data, the sample size and the confidence level.

A 95% confidence level describes the method. If you took many samples and built an interval from each one, about 95% of those intervals would contain the true mean. It does not mean that 95% of the individual values fall inside the range.

The confidence interval formula in Excel#

The margin of error is the critical value times the standard error. The standard error is the standard deviation divided by the square root of the count:

margin = t × STDEV.S(range) / SQRT(COUNT(range))

CONFIDENCE.T does all of this in one step:

=CONFIDENCE.T(alpha, standard_dev, size)

CONFIDENCE.T works in Excel 2010 and later, including Microsoft 365 and Excel for Mac. If you need a refresher on spread, read how to calculate standard deviation in Excel.

Step by step: a 95% confidence interval for a mean#

Here are eight delivery times in minutes, in B2:B9:

Build the interval in column E, one step per cell:

So you can report: "Average delivery time was 32 minutes (95% CI: 28.66 to 35.34)." AVERAGE ignores empty cells and text, and COUNT counts only numbers, so a blank row won't distort either one. The AVERAGE function guide and the COUNT function guide cover those details, and COUNT, COUNTA and COUNTBLANK compared explains why COUNT is the right count here.

To check the margin by hand, use the t critical value from T.INV.2T:

=T.INV.2T(0.05,E4-1)*E3/SQRT(E4)

T.INV.2T(0.05,7) is about 2.365, and 2.365 × 4 ÷ √8 gives the same 3.344. The E4-1 is the degrees of freedom: the count minus one.

CONFIDENCE.T vs CONFIDENCE.NORM#

Use CONFIDENCE.T when the standard deviation comes from your sample, which is almost always. CONFIDENCE.NORM uses the normal distribution's critical value of 1.96 and assumes you already know the population standard deviation.

With eight values, the difference is large. With hundreds of values, the two functions give almost the same answer. The older CONFIDENCE function still works and matches CONFIDENCE.NORM. Which standard deviation to feed in matters too: STDEV.S vs STDEV.P explains why a sample needs STDEV.S.

Change the confidence level to 90% or 99%#

Change alpha to change the level. Higher confidence needs a wider interval:

Put the level in a cell, for example 95% in H1, and use =CONFIDENCE.T(1-H1,E3,E4). Then the whole sheet updates when you change one number. To halve the margin of error, you need roughly four times as many values, because the count sits under a square root.

Confidence interval for a proportion#

For yes/no data, such as "62% of 200 people said yes", the margin uses the proportion instead of a standard deviation:

=NORM.S.INV(0.975)*SQRT(p*(1-p)/n)

With p = 0.62 in B1 and n = 200 in B2, =NORM.S.INV(0.975)*SQRT(B1*(1-B1)/B2) returns about 0.067. The interval is 55.3% to 68.7%. NORM.S.INV(0.975) is the 1.96 critical value. This simple method works best when the sample is large and the proportion is not close to 0% or 100%. The guide to analysing survey results in Excel shows how to get p from raw responses with COUNTIF.

Common confidence interval mistakes#

These are the errors that give a wrong interval without any warning:

1. Using alpha = 0.95. Alpha is 1 minus the level, so 95% means 0.05. With 0.95, the margin is tiny.
2. Using STDEV.P on a sample. It divides by n instead of n − 1 and understates the spread.
3. Counting with COUNTA. If the range has a header or notes, COUNTA counts them as values. Use COUNT.
4. Reading the interval as the range of the data. The interval is about the mean, not about single values.
5. Skipping the ToolPak check. If you have the Analysis ToolPak, Data > Data Analysis > Descriptive Statistics with Confidence Level for Mean ticked reports the margin of error (3.344 here). On Windows, turn it on in File > Options > Add-ins; on a Mac, use Tools > Excel Add-ins.

Practise it#

A confidence interval rests on two basics: the average and the count. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise COUNT, COUNTA and COUNTBLANK so you always count the right cells. You type each formula and it is checked instantly. For a refresher on summary numbers, see AVERAGE, MIN and MAX explained, or try the lower-limit exercise on this page.