**Linear Regression** is a prevalent statistical method for regression analysis. Statisticians use it to create a linear relationship between a dependent and an independent variable to be able to predict unknown outcomes. The **Confidence Interval** is a crucial parameter while performing linear regression analysis in Excel. This article will highlight** how to calculate the confidence interval** and interpret it using **Excel Charts**.

## Download Practice Workbook

You can download the practice workbook from the download button below.

## What Is Linear Regression & Confidence Interval?

**Linear Regression** is a data analysis method to create a linear relationship between a dependent and an independent variable. The equation for linear regression is **y = mx + b **where **m** and **b** are the **slope** and **intercept** respectively. **Confidence Interval (CI)** is a statistical term referring to a range of values that include a population with an assumed confidence level. It is often used to analyze the statistical significance of a certain estimation. It depends on the sample size, sample variance, and confidence level. Statisticians normally use a **95% confidence level to calculate the confidence interval**. The equation for the confidence interval is

**Â± t**

_{(Î±, df)}Ã— S_{yx}**Ã— âˆš(1/n + (x â€“ xÌ…)**

^{2}/ SS_{x})**where,**

**Î±**= 1 â€“ 95% = 0.05**df**= degree of freedom**S**= Standard error in estimation_{yx}**n**= sample count**xÌ…**= sample mean**SS**= sum of squares of deviations of datapoints from sample mean_{x}

Performing a linear regression analysis with a 95% confidence level suggests that there is a 95% probability that the predicted outcomes may fall within the confidence interval.

## How to Calculate Linear Regression Confidence Interval in Excel

Suppose you have the following example dataset where the **y** variable is dependent on **x**. Now you want to calculate the linear regression confidence interval for this dataset.

There are several ways to perform **Linear Regression** in Excel. But in this article, we will use **Scatter Charts** so that it is easier for you to understand. Follow the steps below for that.

- First, select the entire dataset (
**B4:C13)**. Then go to**Insert >> Insert Scatter (X, Y) or Bubble Chart >> Scatter**as shown below.

- After that, the following chart will be created.

- Now click on the chart Element icon in the upper-right corner and enable linear
**Trendline**. Then go to**More Options**or double-click on the**Trendline**.

- Next check the
**Display Equation on chart**checkbox.

- After that,
**y = -0.0532x + 8.704**will appear on the chart. Comparing it to the linear regression equation yields**m = -0.0532**and**b = 8.704**. You can also use the**SLOPE**and**INTERCEPT**functions respectively to calculate these results.

- Now enter the values of
**Slope (m), Intercept (b), and Observations (n)**in cells**F5:F7**You can use the**COUNT function**to get the sample count or observations to be**9**.

- Then enter the following formula in cell
**F8**to calculate the**Standard Error**. Here theÂ**STEYX function**returns the standard error of the predicted y-value for each x in a regression.

`=STEYX(C5:C13,B5:B13)`

- Next, apply the following formula in cell
**F9**to get the sample mean or average. Here we will use theÂ**AVERAGE function**to do that.

`=AVERAGE(B5:B13)`

- After that, enter the following formula in cell
**F10**to get the sum of squares of the variances. Here theÂ**DEVSQ function**returns the sum of squares of deviations of data points from their sample mean.

`=DEVSQ(B5:B13)`

- Now apply the following formula in cell
**F11**to calculate the t-value. Here we will use the**T.INV.2T function**for that.

`=T.INV.2T(0.05,F7-2)`

- Now itâ€™s time to calculate the confidence interval for each data point. The range of
**x**is**0**to**93**. So we will calculate the confidence intervals for the**x**values**0, 10, 20,â€¦â€¦.,100**as shown in cells**B18:B28**. Enter the following formula in cell**C18**and drag the**Fill Handle**icon below to do that. Here theÂ**SQRT function**returns the square root of values.

`=$F$11*$F$8*SQRT(1/$F$7+(B18-$F$9)^2/$F$10)`

- Then apply the following formula in cell
**D18**to get the**y**values at the**Upper 95% Confidence Interval**.

`=($F$5*B18+$F$6)+C18`

- Finally, enter the following formula in cell
**E18**to get the**y**values at the**Lower 95% Confidence Interval**.

`=($F$5*B18+$F$6)-C18`

**Read More: ****How to Calculate Confidence Interval Without Standard Deviation in Excel**

## Interpreting Confidence Intervals in Linear Regression

Here the **Upper 95%** and the** Lower 95% Confidence Intervals** are **9.16** and **8.25** respectively. So, we can be 95% confident that **y** values from any sample size will fall within this range.

Now we will plot the **y **values for the **95% confidence intervals** to interpret them graphically. Follow the steps below to do that.

- First, right-click on the
**Chart Area**to go to**Select Data**.

- Then, click on the
**Add**button as shown below.

- Next, enter a
**Series name**for the**Upper 95 Confidence Intervals**. Use the**x**column for**Series X Values**and the**y + CI**column for**Series Y Values**. Then click OK.

- Similarly insert the
**lower 95% confidence intervals**in the chart. Next, make sure all the**Data Series**are checked and click OK.

- After that, the regression chart will look as follows.

- Now right-click on any of the confidence interval data series and click on
**Change Series Chart****Type**.

- Then change the chart type to
**Scatter with Smooth Lines**for both of the confidence interval data series and click**OK**.

- Finally, the regression chart will look as follows. You can easily understand from the chart that the assumption of the
**95%**probability of the regression data points falling between the confidence intervals is quite true.

**Read More: ****How to Make a Confidence Interval Graph in Excel (3 Methods)**

## Things to Remember

- Donâ€™t forget to apply absolute references in the formulas when necessary to avoid erroneous results.
- A confidence interval is not a guarantee but rather a probability.

## Conclusion

Now you know how to calculate the linear regression confidence interval in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our **ExcelDemy** blog to explore more about Excel. Stay with us and keep learning.

## Related Articles

**How to Find Confidence Interval in Excel for Two Samples****Calculate Z-Score with 95 Confidence Interval in Excel****How to Calculate Confidence Interval for Population Mean in Excel****Excel Confidence Interval for Difference in Means (2 Examples)****How to Calculate P-Value from Confidence Interval in Excel****Calculate 90 Percent Confidence Interval in Excel****How to Find Upper and Lower Limits of Confidence Interval in Excel**