Histograms w/Standard Error Bars (MS-Excel)

In a prior article, I mentions that in many cases, it may be best to export aggregated data to Excel in order to provide the highest quality graphical outputs. In this article, we will be utilizing Excel to provide high quality graphics as it pertains to histograms with standard error overlays.

The data that we will be employing for the example illustration was also utilized for the same purpose in the article prior.

Example:

Presented below is the data set:

If you did not calculate the mean, standard deviation, or standard error values within a prior platform, the following cell code will assist with the calculations of each.

Mean:

(For Group A)

=AVERAGE(C2:C11)

Standard Deviation:

(For Group A)

=STDEV(C2:C12)

Standard Error:

(For Group A)

=(STDEV(C2:C11))/(SQRT(COUNT(C2:C11)))

The product of these calculations should resemble the following:

Next, from the workbook’s mean values, we will create a basic histogram. You should have the ability to perform this task without further instructions. The result will resemble the graphic below.

To add bars which indicate the standard error of the mean, follow the proceeding steps.

Click on the chart graphic, and then select the “Design” tab of the upper ribbon menu. Next, select the “Add Chart Element” option.

This will generate a drop down menu. From this menu select “Error Bars”, followed by “More Error Bars Options”.

This should cause error bars to appear within the graphic.

However, these error bars require the appropriate formatting.

To achieve this, click on the error bar graphic, this should cause a menu to appear on the right side of the Excel console.

In the aforementioned menu, make sure that the option “Custom” is selected, then select “Specify Value”.

This should cause the following prompt to generate:

Click on the arrow located next to each option header: “Positive Error Value” and “Negative Error Value”, this will enable you to select the series which represents each prompt.

For both options, highlight the entire row of data which pertains to the standard error values.

Once this has been completed, the product is a beautiful graphical representation of the data.

I’ll leave the rest of the beautification process to you.

I hope that you enjoyed this article and found it helpful. Many more are on the way.