How to Perform an ANCOVA in Excel

An ANCOVA (“analysis of covariance”) is used to determine whether or not there is a statistically significant difference between the means of three or more independent groups, after controlling for one or more covariates.

This tutorial explains how to perform an ANCOVA in Excel.

Example: ANCOVA in Excel

A teacher wants to know if three different studying techniques have an impact on exam scores, but she wants to account for the current grade that the student already has in the class.

She will perform an ANCOVA using the following variables:

• Factor variable: studying technique
• Response variable: exam score

The following table shows the dataset for the 15 students that were recruited to participate in the study:

Use the following steps to perform an ANCOVA on this dataset:

Step 1: Input the data.

First, input the data in the following format:

Step 2: Calculate the mean and variance for each column.

Next, calculate the mean and variance for each column:

Step 3: Calculate the slopes of the regression lines.

Next, we will calculate the slopes of the regression lines of the exam scores for each studying technique.

Note: Cells B21:E28 display the formulas used to obtain the values in cells B13:E19.

Step 4: Perform a one-way ANOVA on Exam Scores and Current Grade separately.

Next, we will perform a one-way ANOVA on the exam scores:

Reference: How to Perform a One-Way ANOVA in Excel

Then, we will perform a one-way ANOVA on the current grades:

Step 5: Fill in the ANCOVA table.

Next, we will fill in the ANCOVA table.

Note: Cells H39:M43 show the formulas used to obtain the values in cells B39:F43.

Step 6: Interpret the results.

From the ANCOVA table we see that the p-value for study technique is 0.032. Since this value is less than 0.05, we can reject the null hypothesis that each of the studying techniques leads to the same average exam score, even after accounting for the student’s current grade in the class.

May 13, 2024
April 25, 2024
April 19, 2024
April 18, 2024

16 Replies to “How to Perform an ANCOVA in Excel”

1. Pete says:

Zach, in your Step 3: Calculate the slopes of the regression lines, your calculation of bt contains references to blank cells.

2. Peter says:

I am anxiously waiting for a repair to Step 3 above, where the formula references blank cells.

3. J Min says:

Hi

I can’t figure out the “bt” value in step 3, what it’s supposed to be… It points to cells without data. Any pointers would be appreciated.

Thanks

4. Julia says:

Hello,

very nice description but is the formula given in step 3 for the slope (bt) right?
it says use “=SLOPE(B12:D16;F12:H16) but these fields are partly empty and it doesn’t amke sense…

5. Dan says:

When calculating slopes of the regression lines (Step 3), the last formula at the bottom (in cell B2) does not make sense =SLOPE(B12:D16, F12:H16). It links to cells which are empty. Without it, the rest of the calculations are impossible I’m afraid, because you need these results for Step 5.
A pity really…

6. JC says:

bt=0.012 is supposed to be calculated using:
=slope(B12:D16, F12:H16)
but no values in row 12, and no values in column/row F12:H16
have tried to deduce where 0.012 came from, but not possible.

7. Jamie Thompson says:

Hi,
Thanks for sharing this procedure. However, I’m not understanding what “bt” is or what data are being run through the formula, “=slope(b12:d16,f12:h16)”. By selecting b12:d16, you select the text “Slope” and slope+sum of squares statistics, and by selecting f12:h16 you select nothing since there are no statistics in those cells. Maybe I’m missing something obvious. Can you clarify?
Thanks

8. Jared Shorten says:

Hello, sorry, but what if you get a negative bt value?

9. Jared Shorten says:

Sorry, meant negative F, it’s resulting from an unusually high within variation in B23

10. Ivan Reyes says:

Hello Zack,
Thanks for your post is really good, I’m following it to perform an ANCOVA on my dataset but think there’s a mistake in the formulas you used in STEP 3. The equation to use to etimate “bt” =slope(B12:B16,H12:H16), B12 is a title and B16 is empty, same H12:H16 are all empty, it pops out an error.
This is massive as the first equation for the actual ANCOVA uses the bt value as he second term so nothing can actually be performed. Could you clarify this for me? The steps are all good apart form that.

Thank you very much

Hi,
Thanks for the explanation. it is very helpful.
However, i need to know the mean of bw and bt that you have in cells A17 and A18 as well as how you got their respective values in cells B17 and B18

12. Zam Kas says:

Hi,

Thank you for providing such a useful tutorial. I was following through this part of the tutorial but was stucked at how to calculate bt, especially on the function of B12:D16, F12:H16. B12 had slope whereas F12:16 are just empty cells.

Your clarification on this is highly appreciated.

Many thanks.

13. Stu says:

The “Equations Used” in cell B26 is incorrect. I think it should be =SLOPE(B4:D8,F4:H8)

14. Jose says:

Good way to to do it, however there is a big mistake in the Formulae:
“bt” at B18 shall be formulated at B26 as =SLOPE(F4:H8, B4:D8)

15. Raj says:

Hi Zach,

Please recheck formula used for calculation of bt . This slope (B12:D16, F12:H16) does not fit. as

16. Ye Pyae says:

Hello Zach, thanks a lot for comprehensive explanation and also for knowledge sharing. I think the cell references that you mentioned in the Step (3) screen shot picture are not match with the calculation and output value. I am a bit confusing. Please could you explain about it. Many thanks again.