ANOVA in Excel: A Walkthrough of Excel’s Variance Analysis Tool

If you thought there were a lot of functions and features already packed in to Excel, you may be surprised that there are even more available. Both Microsoft and third party developers publish additional tools, called add-ins, that are typically used for specialized number crunching in various fields.

If you are working on statistical analysis and, more specifically, undertaking a variation analysis, there is a tool available through add-ins that should help make the process much easier. Regardless of how complex the research you are working with, the ANOVA tool is simple and user friendly. Here we will briefly take a look at the tool and walk through the steps involved in using it.

If you are interested in learning more about Excel’s advanced analytical capabilities, you might be interested in an online advanced Excel course.

Understanding ANOVA in Excel

The ANOVA function in Excel is the analytical tool used for variance analysis. A form of hypothesis testing, it will determine whether two or more factors have the same mean. Currently, it has three different variations depending on the test you want to perform: Single factor, two-factor with replication and two factor without replication.

Single-factor: This offers a test on data of two or more samples. With it, you can test the hypothesis that each of the samples is drawn from the same underlying probability distribution against the hypothesis that the underlying probability distribution is not the same.

If you are working with only two samples, note that Excel gives you an alternative called T-Test, which is built in to its regular set of functions. If you would like to understand how this and other standard functions operate in Excel, you can take an online course in advanced Excel.

two-factor with replication: when you have two factors on which the variance depends and you are collecting multiple data points for a specified condition, you will want to use this option.

two-factor without replication: When variance depends on two factors and you are collecting a single dat point for a specified condition, you will use this test.

This tutorial assumes that you are familiar with these statistical concepts and will focus on explaining how to use Excel to help you run the tests.  If, however, you need to brush up on concepts in statistics, you can take an online introduction to statistics that should serve as a refresher.

Enable the Analysis Toolpak

The ANOVA function is part of an add-in for Excel, so if you haven’t already, you will need to enable the Excel Analysis Toolpak before you can use it. In addition to ANOVA, this add-in for Excel will give you access to a number of helpful tools for running statistical analysis in your workbooks. If you would like to dive in and understand the power of Excel for working with statistics, you can take a course on Excel’s statistical functions.

Here are instructions for enabling the toolpak in Excel 2010. Depending on the version you are using, the method may vary slightly. So if this does not work for you, you should be able to search for another method or find it on Office.com

1. Go to the file menu

2. Select Options

3. From the options menu, select add-ins from the left column

4. At the bottom of the menu, you will see a label that says “Manage:” followed by a drop-down box. Make sure Excel add-ins is selected from that drop-down and click Go.

5. Another menu will appear, showing you the available add-ins. Check the box next to Analysis Toolpak, and click OK

If the Analysis toolpak is not listed, click browse to locate it. If the program tells you it isn’t installed, click Yes to install it.

You should now have the Analysis Toolpak enabled. To verify, click on the data tab from the main ribbon.  You should see a data analysis option on the right. If you click on that Anova should be among the first options available.

Running ANOVA

Now that you have the Analysis toolpak enabled, you have what you need to complete the test.

Running the ANOVA function

To start with the Anova function, open the workbook containing the data you want to run the test on. Then, follow these steps:

1. click in a cell on your spreadsheet where your output will begin. The results, of course, will cover a range of cells.

2. Click on the Data tab from the main ribbon and select data analysis, which should be in the analysis menu on the right.

3. Select the appropriate Anova test from the options in the Data Analysis menu.

4. The function’s menu will pop up. Start by putting in the range containing the data to be analyzed. If you click the button to the right of the text box (containing a red arrow) you can select your cell range by clicking and dragging.

5. Select the number of rows each sample contains.

6. Specify the alpha (the default 0.05 represents a 95% confidence interval.

7. Specify the output range. Again, you can click the button to the right of the text box to click and drag.  All you really need to consider here is where the first cell of the results will be located. You do not need to indicate the exact number of rows and columns for the result.  Note that the output will be contained in a range of 7 columns by 30 rows.

You now have your result.  All that’s left is interpreting it! If you are a little bit fuzzy on what the numbers mean, don’t worry, you have resources available to help out. In fact you can take an online course that includes a walkthrough of ANOVA and its results.

Hopefully, your test has run smoothly and produced a useable outcome.