# How To Calculate Confidence Interval in Excel (With Example)

Updated October 16, 2023

Microsoft Excel is a valuable tool for data analysis and evaluation. Applying the command functions in the program can help you organize and work with data values quickly and easily. When you have a data set with a sample mean and standard deviation, you can calculate the confidence interval for the sample using the confidence function. In this article, we discuss what the confidence function is, how to calculate the confidence interval in Excel and what to consider when using the program for statistical analysis with an example to guide you.

## What is the confidence function in Excel?

The confidence function in Excel allows you to input a formula that gives you a numerical value that you can use to calculate a confidence interval for the mean of a data set. The confidence interval represents a range of data points that center on a sample mean. When using the confidence function, Excel gives you a number that you can use to calculate the confidence interval for the data you're studying.

As you type the formula for confidence interval into Excel, you apply the syntax =CONFIDENCE(alpha,standard_dev,n), where the alpha value represents the significance level between zero and one, and n represents the sample size. The function also applies the standard deviation of the sample mean. When Excel returns the confidence value, apply it to the data set to get the confidence interval. This is a double-value result, which represents how far from the mean your data can range in either direction without affecting the null hypothesis.

Related: Basic Excel Formulas and How To Use Them

## How to calculate confidence interval in Excel

Use the following steps to calculate the confidence interval using both formats of the =CONFIDENCE() function in Excel:

### 1. Calculate the sample mean

In a separate column, use a cell to record the sample size of 38, such as in column C, cell C2. In column B, label corresponding cells for each value you calculate in column C. So in cell B2, you have the label "sample size" to correspond with the value in cell C2. Then, label cell B3 as "mean" and apply the formula =AVERAGE(A2:A40) in cell C3. Assume the mean value is 44.55 for the sample.

### 2. Find the standard deviation

Apply the =STDEV.P() function to calculate the standard deviation of your data. Label another cell in column B as "standard deviation," under your "mean." Then, apply the standard deviation command for the example data set in column A as =STDEV.P(A2:A40) in cell C4. Assume the program returns a standard deviation of 14 for the example data set.

Related: How To Calculate Standard Deviation: What It Is and How To Use It

### 3. Input the alpha value

The alpha value is a value of probability, which gives a statistical significance factor as a percentage. This represents the probability that the null hypothesis is incorrect. Because inferential statistics aim for high levels of confidence in data relationships, the alpha value is typically between zero and one, where 0 < alpha value < 1.

When inputting the confidence function into Excel, use the alpha value you get when estimating a rate of confidence. For instance, a 96% confidence rate in the null hypothesis would give you an alpha value of 0.04. Create a label for the alpha value under the standard deviation label you have in column B. Then, list the numerical value in column C in the corresponding cell.

### 4. Type in the confidence function

When you have all values for the formula, use a new cell to type in the confidence formula. Label a new cell in column B as "confidence value" under "alpha value" and list the formula in the corresponding C cell. Because you list each value in a cell, you can use the cell labels in the formula. With the sample size n=38 in cell C2, the standard deviation of 14 in cell C4 and the alpha value of 0.04 in C5, this would read as =CONFIDENCE(C2,C4,C5). You can also type in the numeric values, which would read as =CONFIDENCE(0.04,14,38) to get a confidence value of ±4.66.

### 5. Calculate the confidence interval

The confidence value you get from the calculation both adds to and subtracts from the mean to give you a range that your data can distribute and still support the null hypothesis. Using the example confidence value of ±4.66, add and subtract to get the interval. This results in (44.55 + 4.66 = 49.21) and (44.55 - 4.66 = 39.89) for a confidence interval between 39.89 and 49.21. You can also use the confidence interval formula in the spreadsheet as =[44.55]+(1.96*(/SQRT())).

Related: How To Calculate Confidence Interval: Formula and Examples

## Tips when using the confidence function

Consider the following when using Excel to calculate the confidence interval:

• Label your columns for clarity. Give names to each column that you need for your analysis. For instance, label your data set, the values you're listing and the formulas you're using.

• Use the appropriate standard deviation function. For numerical calculations, apply the STDEV.P function rather than STDEVPA, as the STDEV.P command ignores data that isn't numerical.

• Apply the formulas carefully. Ensure you type in the command functions correctly, as each function has specific elements that work in a certain order when typing them into Excel.

Related: 25 Best Excel Tips To Optimize Your Use

Get interview-ready with tips from Indeed
Prepare for interviews with practice questions and tips

## Example of calculating the confidence interval

An academic adviser has a sample of six test scores from a university philosophy class of 432 students. They want to calculate the confidence interval in a spreadsheet to determine whether these scores are close to the normative mean. Listing this data in the spreadsheet gives the adviser:

 .css-1g5t2dl{font-family:"Noto Sans","Helvetica Neue","Helvetica","Arial","Liberation Sans","Roboto","Noto",sans-serif;font-weight:inherit;color:#595959;font-size:1.25rem;line-height:1.5;color:#2d2d2d;margin-bottom:0;font-size:1rem;margin-top:1.5rem;}html[lang=ja] .css-1g5t2dl{font-size:1rem;line-height:1.75;}@media screen and (min-width: 62em){.css-1g5t2dl{font-size:1.25rem;margin-top:2.5rem;}}A B C 1 Test scores Calculation: Formula: 2 81.3 Mean =AVERAGE(A2:A7) = 85.183 3 81.8 Standard deviation =STDEV.P(A2:A7) = 3.010 4 84.5 Alpha value 0.03 5 85.9 Confidence value =CONFIDENCE(C4,C3,C7) = ±2.667 6 88.2 Confidence interval 82.516 - 87.85 7 89.4 Sample size 6

In the spreadsheet, the score values appear under column A, and the labels are under column B. The command functions occur in the cells under column C. In Excel, typing out the command functions returns a numerical value automatically, converting the formula to the result after hitting "Enter." For the test scores, the confidence value is 2.667, which the adviser adds and subtracts to the mean of 85.183 to get a distribution between 82.516 and 87.85. Because the alpha value is 0.03 or 3%, this means the confidence level is 97%.

Share:
Indeed Career Services
Indeed Resume
Get noticed by employers
Interview Practice
Practice interviewing with an expert career coach
Book a session
Resume Services
Get your resume reviewed or rewritten
Resume Samples
Kick start your search with templates
Browse resume samples
Salary Calculator
Company Reviews
Access millions of company reviews
Find companies

## Explore more articles

• 16 Communication Technology Examples To Use at Work
• 15 Examples of Business Functions (With Definitions)
• 46 Ways To Say Farewell to Your Coworkers or Manager
• 8 Types of Employee Recognition (And Why It's Important)
• 17 Benefits of Team Building for Your Organization