How to Create a Bar Chart with Error Bars in Excel - three groups PDF

Preview

Summary

Download How to Create a Bar Chart with Error Bars in Excel - three groups PDF

Description

Jamie Stoneman

How to Create a Bar Chart with Error Bars in Excel These instructions will show you how to create a Bar Chart in Excel which has 95% confidence error bars on it. The finished chart will look like this: Blood Constituent Levels for Groups 1, 2 and 3 5.2

Bood Constituent Level

5 4.8 4.6 4.4 4.2 4

Group 1

Group 2

Group 3

If you are applying this guide to a different set of data, you must change the cell references accordingly to match your data. Just to give you an idea of what your final spreadsheet will look like, when you have completed your spreadsheet it will look like the one in the screenshot below.

PTO

Jamie Stoneman

Begin by copying and pasting the data from the following table into Excel; paste it into cell B1, as the first column will be needed for labelling. Blood Constituent Level Group 1 4.76 4.94 4.85 4.58 4.44 4.76 4.72 4.63

Blood Constituent Level Group 2 5.13 4.94 5.35 5.4 4.94 4.89 4.77 5.25 5.01

Blood Constituent Level Group 3 4.29 4.4 4.61 4.73 4.55 4.36 4.38

Your spreadsheet should now look like this:

Now you will need to calculate some values which you need for the bar chart: Type ‘mean’ in cell A12, ‘standard deviation’ in cell A13, ‘standard error’ in cell A14 and ‘for 95% confidence’ in cell A15 Type ‘Group 1’ in cell B17, type ‘Group 2’ in cell C17 and ‘Group 3’ in cell D17. It will look like this (PTO):

Jamie Stoneman

The mean for Group 1 will be put into cell B12. Type the following formula into cell B12: =AVERAGE(B2:B9) You should get the value 4.71 in cell B12

The standard deviation for Group 1 will be put into cell B13. Type the following formula into cell B13: =STDEV.S(B2:B9) You should get the value 0.1575.. in cell B13

The standard error for Group 1 will be put into cell B14. Type the following formula into cell B14: =B13/SQRT(8) You should get the value 0.0557.. in cell B14 Note that the 8 in this formula is the SAMPLE SIZE. There are 8 individuals in this sample, for Group1. If you are applying this formula to a different set of data you need to change the figure 8 to whatever the sample size is for your data set. The value for 95% confidence interval for University A will be put into cell B15. Type the following formula into cell B15: =1.96*B14 You should get the value 0.109.. in cell B15 (PTO)

Jamie Stoneman

Your spreadsheet should now look like this:

Now you need to calculate these values for Groups 2 and 3. Have a go at working these out yourself. Here is what you should type in to cells C12 to C15: =AVERAGE(C2:C10) =STDEV.S(C2:C10) =C13/SQRT(9) =1.96*C14 For cells D12 to D15: =AVERAGE(D2:D8) =STDEV.S(D2:D8) =D13/SQRT(7) =1.96*D14

PTO

Jamie Stoneman

Now everything is set up to create the Bar Chart. Highlight cells B12 to D12 as below:

Click on the Insert tab

Then click on the Bar Chart button which looks like this:

Select the ‘clustered column graph’ – top, on the left:

PTO

Jamie Stoneman

Now you should get a chart which looks like this: Chart Title 5.2 5 4.8 4.6 4.4 4.2 4

1

2

3

It needs some labels. Right-click on the chart and click on ‘Select Data’. In the window that opens, click on the ‘Edit’ button in the ‘Horizontal (Category) Axis Labels’ window, see below:

The following window should appear:

Highlight cells B17 to D17:

Then click ‘OK’ and click ‘OK’ again. Your graph should now look like this (PTO)

Jamie Stoneman

Chart Title 5.2 5 4.8 4.6 4.4 4.2 4

Group 1

Group 2

Group 3

A label is needed for the vertical axis. Click anywhere on the chart, then on the toolbar at the top of the screen, click on the ’Design’ tab (see below)

Then click on the ‘Add Chart Element’ button on the left. From the drop-down menu, select ’Axis Titles’ then ‘Primary Vertical’:

PTO

Jamie Stoneman

The vertical axis will have a label (‘Axis Title’) which you can click into and type over to give it an appropriate label. Chart Title 177.6 177.4

Axis Title

177.2 177.0 176.8 176.6 176.4 176.2

University A

University B

Change the label to ‘Blood Constituent Level’. Also give the chart a title. Click in the title above the graph and write ‘Blood Constituent Levels for Groups 1, 2 and 3’. Your chart should now look like this: Blood Constituent Levels for Groups 1, 2 and 3 5.2

Blood Constituent Level

5 4.8 4.6 4.4 4.2 4

PTO

Group 1

Group 2

Group 3

Jamie Stoneman

Now the error bars can be added. Click once on either of the two bars to select them:

Then click on the ‘Design’ tab and then ‘Add Chart Element’. From the dropdown menu select ‘Error Bars’ then click on ‘More Error Bars Options..’:

A menu will appear on the right:

In this menu click on Custom, then click the ‘Specify Value’ PTO

Jamie Stoneman

The following window will appear:

Click on the top blue and red button. The window should get smaller like this:

Now highlight your ‘for 95% confidence’ values, cells (B15 to D15 in this example):

Go back to the window and click the red and blue button again, it should then look like this:

Click on the lower blue and red button. The window should get smaller like this:

Now highlight your ‘for 95% confidence’ values, cells (B15 and C15 in this example) again, as you did previously:

Go back to the window and click the red and blue button again, it should then look like this.. (PTO):

Jamie Stoneman

Now click ‘OK’ and your error bars will be created as below: Blood Constituent Levels for Groups 1, 2 and 3 5.2

Bood Constituent Level

5 4.8 4.6 4.4 4.2 4

Group 1

Group 2

Group 3

Note that the bar for Group 2 is slightly longer, this is to be expected, as this group has a larger standard deviation than the others.

End...