How to add 95% Confidence Intervals to an Excel graph

You will need:
Mean for each group
95% CI lower and upper limit for each group.
(Download my worksheet example)

In row 1 of your worksheet, first write the following titles in cells A1 to F1:
A1: Group Name
B1: Mean
C1: 95%CI LL
D1: 95%CI UL
E1: Neg Error Bars
F1: Pos Error Bars

In column A, beginning in cell A2 of your worksheet, under Group Name, add the names of each group beginning in cell A2.
In column B, beginning in cell B2 of your worksheet, under Mean, add the mean values for each group.
In column C, beginning in cell C2, add the lower limit of the 95% CI for each group.
In column D, beginning in cell D2, add the upper limit of the 95% CI for each group.

In column E, in cell E2, type the formula
=(B2-E2)
to subtract the lower limit of the 95% CI from the mean.
Now select cell E2, and drag it downwards to auto-fill the remaining cells in Column E.

In column F, in cell F2, type the formula
=(F2-B2)
to subtract the mean from the upper limit of the 95% CI.
Select cell F2 and drag downwards to auto-fill the remaining cells in Column F.

Create a new Marked Line graph.

Right-click on the graph and choose “Select Data”.
For Series 1, select column B, beginning at cell B2 as the values for the Y axis, and column A beginning at cell A2 for the X axis category labels. This will draw a graph with the means of Group A and B connected by a line.

Now go to the Chart Layout tab, and click Error Bars. Select Error Bars Options.
Under Error Bars, select Custom and click Specify Value.

For Positive Error Value, click and drag down column F, beginning at cell F2.
For Negative Error Value, click and drag down column E, beginning at cell E2.

Click OK and you are done.

Comments are closed.