How to Find Anova in Excel for Macby David WeedmarkUpdated September 28, 2017
Anova is a type of data analysis available in Microsoft Excel including Excel for Mac computers. Anova is an acronym for "Analysis of Variance" and is useful for analyzing data between two data sets such as performing tests with one or two variables. While the Anova analysis may seem complicated – and it probably is – activating this feature in Excel requires only a couple clicks of the mouse once your data has been arranged properly in an Excel worksheet.
Launch Excel on your Mac and create a new workbook by pressing “Command-N” on the keyboard.
Type your data into the workbook, entering each set of data in its own column. For example, if you were testing two new medications the results of each medication would be placed in its own column. An Anova Single Factor analysis could then be performed on these two sets of data.
Arrange the data to take into consideration a second variable if you have one in your data. For example, if the gender of each subject was known, the data in both columns would have to be arranged by gender. You would then add a third column showing the sex of each subject beside the data. Note that the second variable cannot be mixed in these columns when using Anova. If you had ten male and ten female subjects, for example, the first ten rows would have to be of one gender and the second ten of the other gender. The number of males and females must also be the same if you plan to use Anova analysis with replication.
Click the “Tools” menu and select “Data Analysis.” Three types of Anova analysis are listed at the top of the window.
Select “Anova: Single Factor” to test a hypothesis for a single analysis on two columns of data only such as one medication compared to the other without any other variables.
Select “Anova: Two-Factor With Replication” when two different variables are present in the samples. In the above example, this would be to factor in the effect of gender on the medication.
Select “Anova: Two-Factor Without Replication” when there are two sets of variables but the second variable is not to be analyzed. For example, gender in the subjects taking the medication is known but is not to be analyzed.
Click “OK” after selecting the appropriate Anova analysis. The Anova dialog box opens. Drag the cursor across the cells to be analyzed in the workbook. This is automatically placed in the “Input Range” field.
Leave the “Alpha” field at its default “0.5” value unless you have calculated a different alpha risk for your data analysis.
Note that there is an additional “Rows Per Sample” field if you selected “Anova: Two-Factor With Replication.” In the example used above you would type “10” in this field for ten females and ten males.
Select “New Workbook” in the Output Options section to have the analysis placed in a new workbook. Click “OK” and a new workbook opens with the analysis.