11. Calculate mean, standard deviation or standard error in Excel

Mean (average) Standard Deviation (SD) and Standard Error (SE)

For t-tests and ANOVA, when presenting your data in a report or paper, you usually need to provide an indication of the variance or “spread” or variability of your data around the sample’s mean (average).

You usually do this through a figure/chart/graph. When reporting t-tests and ANOVA, you usually present data as a bar chart with the categorical variable represented across the x-axis and the continuous variable on the y.

For each group/level of the categorical variable, you usually present the mean as the “height” of the bar, ± (plus of minus) SE or mean ± SD. The SD and SE are two different, though related, measures of the spread of the data around the mean.

The most simple way of obtaining the SD and SE is in Excel. Let’s say you have a group of values for a categorical variable represented in a column in Excel. At the base of the column you can calculate the mean. Simply enter a “=” into the cell below or next to your column of values and start typing “=average”. Excel will detect that you want to calculate the mean/average, so click on the word AVERAGE that pops up in the list. You can now select the values you want to determine the mean of and hit enter. Your mean/average will appear in the cell.

To calculate the standard deviation, select another nearby cell and write in “=STDEV”. Repeat steps as for average.

To calculate the SE you calculate SE = SD/square-root of N. N is the sample number/the number of replicates in your group.

You usually express the SD or SE of the mean as error bars the length of either of these statistics (choose which one of these variance measures you are going to use per report or publication and stick with it).

You can make your figures in Excel and there are user friendly error-bar fitting procedures available in the chart formatting section.