How to Use the ANOVA Data Analysis Tools in Excel

By Stephen L. Nelson, E. C. Nelson

The Excel Data Analysis add-in provides three ANOVA (analysis of variance) tools: ANOVA: Single Factor, ANOVA: Two-Factor With Replication, and ANOVA: Two-Factor Without Replication. With the ANOVA analysis tools, you can compare sets of data by looking at the variance of values in each set.

As an example of how the ANOVA analysis tools work, suppose that you want to use the ANOVA: Single Factor tool. To do so, take these steps:

  1. Click Data tab’s Data Analysis command button.

  2. When Excel displays the Data Analysis Dialog box, choose the appropriate ANOVA analysis tool and then click OK.

    Excel displays the appropriate ANOVA dialog box. But you can also work with two other versions of the ANOVA tool: a two-factor with replication version and a two-factor without replication version.

    image0.jpg

  3. Describe the data to be analyzed.

    Use the Input Range text box to identify the worksheet range that holds the data you want to analyze. Select from the Grouped By radio buttons — Columns and Rows — to identify the organization of your data. If the first row in your input range includes labels, select the Labels in First Row check box. Set your confidence level in the Alpha text box.

  4. Describe the location for the ANOVA results.

    Use the Output Options buttons and boxes to specify where Excel should place the results of the ANOVA analysis. If you want to place the ANOVA results into a range in the existing worksheet, for example, select the Output Range radio button and then identify the range address in the Output Range text box. To place the ANOVA results someplace else, select one of the other Output Options radio buttons.

  5. Click OK.

    Excel returns the ANOVA calculation results.