How to Use Excel to Calculate a Confidence Interval

Excel is a powerful tool for handling large amounts of data, as well as for automating some bits of math that you'd rather not have to slog through by hand. Finding the confidence interval for a mean of some data is one of those tasks that you could do by hand, but – unless you're a glutton for punishment – you'll be happy to learn that you can get Excel to handle much of the hard work. Learn to work out a confidence interval in Excel and save yourself time and effort.

A confidence interval tells you the range of values where the true mean (the average) for a population should fall based on a sample. Confidence intervals are a way to acknowledge the uncertainty in your data in a structured and scientific fashion. In most cases, people use a 95 percent confidence interval, which means that if you repeated the experiment 20 times, the true mean would be within the interval for 19 of them.

When you calculate a confidence interval, you use the result to present your mean value alongside your level of uncertainty. For example, you might have a mean of 130 pounds and write 130 ± 12 pounds, indicating that the true mean value is somewhere between 118 and 142, or 130 pounds (CI: 118 to 142 pounds), where the CI stands for confidence interval.

You don't always have to use a 95 percent confidence interval, though. The value you choose is called your confidence level, and other common confidence levels include 99 percent and 90 percent. The process described here can also be called finding the margin of error in Excel.

You need a few different things to find a confidence interval in Excel. The first is easy, the mean of your data. You can calculate this by adding all of your data points and dividing the answer by the number of data points, or use the Excel Average function, by typing =AVERAGE([range of cells]) into a blank cell, and either manually entering the range of values into the spot where it says range of cells or highlighting with your mouse to select them.

You also need the number of data points in your sample, called n in statistical contexts. You can count the cells or use Excel's Count function to find this. Either type =COUNT([range of cells]) with the range of cells containing your data where it says range of cells or by clicking and highlighting them with your mouse – or simply enter the value into a blank cell.

The most challenging part of this process is finding the standard deviation of your data. However, you can do this using a built-in Excel function. Enter =STDEV([range of cells]) to find the standard deviation of your data. For example, if you have data running from cell A2 to A21, type =STDEV(A2:A1) to find the standard deviation of the data.

As on the previous examples, you can also type =STDEV( and then use your mouse to select the range of cells containing your data. With all of these three bits of information in three separate cells, you are ready to find the confidence interval in Excel.

There is an Excel function specifically designed to help you find the confidence interval for a mean. This is the easiest method you can use to find a confidence interval, but it doesn't do absolutely everything you need to calculate a confidence interval for you. However, with the information in the previous section and some additional guidance, you'll have no problem finishing the process.

The Confidence function in Excel is specifically designed for this task, and it has three arguments (the part inside the brackets) you need to fill in. The format is =CONFIDENCE(alpha,sigma,n) where alpha represents the chosen significance level, sigma is the standard deviation and n is the number of data points.

To find the alpha value, subtract your chosen significance level (as a decimal) from 1, so a 95 percent confidence level in Excel has an alpha of 0.05, a 99 percent confidence level has an alpha of 0.01, and for 90 percent it's 0.1. The sigma value is simply the output of the STDEV field for your data, and the n value is the output of the Count function or the count you completed by hand.

Use the Confidence function according to the format in the last section. First, type =CONFIDENCE( and then enter an appropriate alpha value. For example, if you're calculating a 95 percent confidence interval, type =CONFIDENCE(0.05, and then click on the cell (or type its code) containing your standard deviation followed by another comma. Finally, enter the value for n after the last comma and then close the parentheses.

So, for example, if you want a 95 percent confidence value, your n value is in cell B4 and your standard deviation is in cell B5, type =CONFIDENCE(0.05,B5,B4) into a blank cell to return the value. This value isn't the interval itself, but you can use it to create the interval.

To produce the confidence interval in Excel, take this value and add it to the mean value in one cell, and then subtract it from the mean value in another cell. The range between these two extreme values is the confidence interval for the mean.

There is another way you can use Excel to calculate a confidence interval: with a formula. The confidence interval formula isn't that complicated to understand, and the benefit of learning how to use it is that you aren't dependent on Excel every time you need to calculate one. The basic formula for a 95 percent confidence interval is: mean ± 1.96 × (standard deviation / √n).

This is easy to calculate based on the information you already have. In an empty cell, type =[mean]+(1.96*([standard deviation]/SQRT([n]))) to get the answer for the upper bound. Note that the values in square brackets – [_mean_], [_standard deviation_] and [n] – have to be replaced with cell references for that specific bit of information, without the square brackets. All of the curved brackets need to be kept in position.

The other part of the formula involves the same calculation but subtracted from the mean. So type =[mean]−(1.96*([standard deviation]/SQRT([n]))) to find the lower bound. The confidence interval is between this value and the one above. For a 99 percent confidence interval, use 2.576 in place of 1.96, and for a 90 percent confidence interval, use 1.645.

You can use the formula to calculate the confidence interval in one huge step. You'll need one cell for the lower bound of the confidence interval and one for the upper bound, though.

Imagine you have data from cells A1 to A20, type:

=AVERAGE(A1:A20)−(1.96*(STDEV(A1:A20)/SQRT(COUNT(A1:A20))))

into the first cell to find the lower bound at 95 percent.

Then type:

=AVERAGE(A1:A20)+(1.96*(STDEV(A1:A20)/SQRT(COUNT(A1:A20))))

into the second cell to find the upper bound. You can replace the A1:A20 with the range for your specific data. For example, if your data runs from cells B20 to B50, type B20:B50 in these places instead.