In this article, you will learn about confidence interval in Excel. You will learn the formula, calculations, and different functions for confidence.

The confidence interval determines the probability of lying a mean value in a range of values. The major advantage of the confidence interval is that it is not time-consuming just like hypothesis testing. And it is far easier to interpret.

**Download Practice Workbook**

You can download the practice workbook from here.

## What Is Confidence Interval?

The confidence interval determines the probability of lying a mean value in a range of values. It measures the degree of certainty and uncertainty in a sample. There are upper confidence levels and lower confidence levels. If the interval is narrow, that means the prediction is more accurate.

The formula for Confidence Interval is:

**Confidence Interval = Mean ± Confidence Value**

Here,

**Confidence Value = Z × (Standard Deviation ⁄ √Sample Size)**

Where,

**Z **= The **Z Score** for the defined **Confidence Level**.

## Benefits of Calculating Confidence Interval in Excel

There are many benefits of calculating Confidence Interval in Excel. They are:

- It is a more simplified process than calculating Confidence Interval manually.
- It saves a lot of time.
- Excel provides different functions for calculating Confidence Intervals which has made the calculations easier.

## Excel Functions for Confidence Interval

Excel provides different functions for Confidence Interval. They are:

**1. CONFIDENCE Function**

It returns the confidence value for the population mean using the normal distribution. The syntax for this function is.

**CONFIDENCE(alpha,standard_dev,size)**

Here, alpha is the level of significance. The value for **alpha** must be **0** < **alpha** < **1**, **standard_dev** refers to the standard deviation for the population. And, **size** refers to sample size.

**2. CONFIDENCE.NORM Function**

This function calculates the confidence interval for a population mean, using a normal distribution. The syntax of this function is.

**CONFIDENCE.NORM(alpha,standard_dev,size)**

Here, alpha is the level of significance. The value for **alpha** must be **0** < **alpha** < **1**, **standard_dev** refers to the standard deviation for the population. And, **size** refers to sample size.

**3. CONFIDENCE.T Function**

It returns the confidence interval for a population mean and uses a Student’s t distribution for that. The syntax for this function is.

**CONFIDENCE.T(alpha,standard_dev,size)**

Here, alpha is the level of significance. The value for **alpha** must be **0** < **alpha** < **1**, **standard_dev** refers to the standard deviation for the population. And, **size** refers to sample size.

## How to Calculate Confidence Interval in Excel

### 1. Use Generic Formula to Calculate Confidence Interval in Excel

- Select the cell where you want the
**Mean**>> write the following formula.

`=AVERAGE(C5:C12)`

- Press
**Enter**.

- Select the cell where you want the
**Standard Deviation**(here, I wanted the result for sample standard deviation) >> write the following formula.

`=STDEV.S(C5:C12)`

- Press
**Enter**.

- Select the cell where you want the
**Sample Size**>> write the following formula.

`=COUNTA(B5:B12)`

- Press
**Enter**.

- Select the cell where you want the
**Z Score**>> write the**Z Score**for your**Confidence Level**.

**Here, my**

*Note:***Confidence Level**is

**95%**and the

**Z Score**for that is

**1.96**. You have to change that according to your confidence level.

- Select the cell where you want the
**Confidence Value**>> write the following formula.

`=C17*(C15/SQRT(C16))`

- Press
**Enter**.

- Select the cell where you want the
**Confidence Interval**>> write the following formula.

`=TEXT(C14,"#,##0.00")&" ± "&TEXT(C18,"#,##0.00")`

- Press
**Enter**.

**🔍****How Does the Formula Work?**

**TEXT(C14,”#,##0.00″):**Here,**the TEXT function**is used to change the format of the value in cell**C14**to a thousand separator with**2**decimal points.**TEXT(C14,”#,##0.00″)&” ± “&TEXT(C18,”#,##0.00”):**The**Ampersand Operator (&)**joins these formulas with the “**±**” sign.

### 2. Apply Excel CONFIDENCE Function to Calculate Confidence Interval

- Calculate
**Mean**like the previous method.

- Select the cell where you want the
**Standard Deviation**(here, I wanted the result for population standard deviation) >> write the following formula.

`=STDEV.P(C5:C12)`

- Press
**Enter**.

- Calculate the
**Sample Size**like the previous method.

- Select the cell where you want the
**Level of Significance**>> write the following formula.

`=1-0.95`

- Press
**Enter**.

*Here, my Confidence Level is*

**Note:****95%**. For that reason, I used

**0.95**. You will have to change this

**Value**accordingly.

- Select the cell where you want the
**Confidence Value**>> write the following formula.

`=CONFIDENCE(C17,C15,C16)`

- Press
**Enter**.

- Select the cell where you want the
**Confidence Interval**>> write the following formula.

`=TEXT(C14,"#,##0.00")&" ± "&TEXT(C18,"#,##0.00")`

- Press
**Enter**.

### 3. Calculate Confidence Interval Using Excel CONFIDENCE.NORM Function

- Find
**Mean**,**Standard Deviation**,**Sample Size**, and**Level of Significance**like the previous method.

- Select the cell where you want the
**Confidence Value**>> write the following formula.

`=CONFIDENCE.NORM(C17,C15,C16)`

- Press
**Enter**.

- Select the cell where you want the
**Confidence Interval**>> write the following formula.

`=TEXT(C14,"#,##0.00")&" ± "&TEXT(C18,"#,##0.00")`

- Press
**Enter**.

### 4. Use CONFIDENCE.T Function to Calculate Confidence Interval

- Find
**Mean**,**Standard Deviation**,**Sample Size**, and**Level of Significance**like the previous method.

- Select the cell where you want the
**Confidence Value**>> write the following formula.

`=CONFIDENCE.T(C17,C15,C16)`

- Press
**Enter**.

- Select the cell where you want the
**Confidence Interval**>> write the following formula.

`=TEXT(C14,"#,##0.00")&" ± "&TEXT(C18,"#,##0.00")`

- Press
**Enter**.

### 5. Apply Data Analysis Feature to Calculate Confidence Interval

- Go to the
**File**tab.

- Select
**Options**.

- Select
**Add-ins**tab >> select**Go**from**Manage**.

**Add-ins**dialog box appears >> check**Analysis ToolPak**>> check**Solver Add-in**>> select**OK**.

- Go to the
**Data**tab >> select**Data Analysis**.

**Data Analysis**dialog box appears >> Select**Descriptive Statistics**>> select**OK**.

- A
**Descriptive Statistics**dialog box will appear. - Then, select Input
**Range**>> check**Labels in the first row**if your range contains**Labels**>> select**Output Range**>> check**Summary****statistics**>> check**Confidence Level for Mean**and write the**Confidence Level**>> select**OK**.

- Finally, you will see a summary will appear with the
**Confidence Value**.

## Things to Remember

- You have to remember that
**CONFIDENCE**functions only take numeric values. - You must use the correct function for calculating
**Standard Deviation**.

## Frequently Asked Questions

**1. How do you find the 95 percent confidence interval?**

To find the **95 **percent confidence interval you will have to set **alpha** as **(1-0.95)** in the **CONFIDENT **function.

**2. How to calculate Sample Size?**

You can use **the COUNTA function** to calculate the **Sample Size**.

**3. How to find the upper and lower limits of a confidence interval?**

For the upper limit, you will have to **add **the **Confidence Value **with the **Mean**. And for the lower limit, you will have to **subtract **the **Confidence Value **from the **Mean**.

## Conclusion

So, we have reached the end of this article. We have tried to explain how to calculate confidence interval for different types of data and in different situations in Excel. We hope this article was helpful to you. If you have any questions, feel free to let us know in the comment section below.

## Confidence Interval in Excel: Knowledge Hub

**Calculate Confidence Interval****Make Confidence Interval Graph****Find Upper and Lower Limits of Confidence Interval****Find Confidence Interval for Two Samples****Confidence Interval for Difference in Means****Calculate Confidence Interval for Population Mean****Find Confidence Interval Proportion****Calculate 90 Percent Confidence Interval****Calculate 95 Percent Confidence Interval****Determine 99 Percent Confidence Interval****Linear Regression Confidence Interval****Forecast Confidence Interval****Add Confidence Interval Error Bars****Calculate Confidence Interval Without Standard Deviation****Calculate Z-Score with 95 Confidence Interval****Find P-Value from Confidence Interval**

**<< Go Back to Excel for Statistics | Learn Excel**