How to Create a One Way ANOVA in Excel

By David Wayne

In Microsoft Excel, install the Analysis ToolPak add-in to gain access to a large collection of data-analysis tools, including t-tests and several analysis of variance tests. The single factor ANOVA function takes several data columns as arguments and generates analysis of variance data on the same spreadsheet or a new one.

Install the Analysis ToolPak

Several add-ins come with Excel, including the Analysis ToolPak, but you must load them from the Excel "Options" menu to make them accessible. Click “File,” “Options” and “Add-Ins,” then select “Excel Add-Ins” from the Manage menu and click “Go.” In the Add-Ins menu, choose “Analysis ToolPak” and click “OK.” A new Analysis section is added to the Data tab, and when you click Data Analysis, it opens a menu containing several statistical tests. The first test in the menu, ANOVA Single Factor, performs a one-way ANOVA test on two or more sample sets.

Set Up a One-Way ANOVA Function

Choose “ANOVA Single Factor” from the "Data Analysis" menu and click “OK.” The resulting pop-up window displays several options in Input and Output sections; click the button next to Input Range to activate the column selection tool, then left-click and drag the cursor over the columns containing the sample sets to compare. Include the column names in the selection and then enable “Labels in First Row” to add these labels to the resulting ANOVA table. Do the same procedure for Output Range, selecting a range of cells in which to place the ANOVA table. When you click “OK,” Excel runs the ANOVA test and displays the sum of squares, degrees of freedom, mean square, F value, P value and F Critical in the chosen output columns. An F value lower than F Critical indicates that all sample sets have the same probability distribution, while an F value higher than F Critical indicates a statistically significant difference.