Creating confidence bars in Excel is relatively easy.  First create your line chart.  Then with the series selected, click on Chart Tools > Layout > Error bars > More error bar options.  In the pop up menu that appears, you can either choose to have positive or negative error bars, or both.  You can choose the style and you can choose the amount you wish the bar to show.  This can be a fixed value, a percentage, a standard deviation or a custom range.

format error bars

Since my data has a custom standard deviation for each point, I chose custom and clicked on the Specify Value button.  Another pop up menu then appears and you can choose the cell range for both the positive and the negative bars.

custom error bars

This will give you a chart that looks something like this:

confidence bars

While that is great, it is not so easy to see the limits of the error bars and if you have a lot of data it will be messy.  So I give you instead, a confidence band chart:

confidence bands

At a glance, the confidence limit is much more obvious IMHO. Unfortunately, Excel doesn't allow you to do this automatically.  Instead, you need to create a combined line / area chart. You can download my sample chart here.

Step 1: Set up your data

First in addition to your averages, you also need your standard deviation (or error) calculation.  My data looks like this:

averages std error

Then in row 4, you need to calculate the upper limit of your band i.e. for B4 the calculation will be:

=B2+B3

In row 5, you need to calculate the lower limit of your band i.e. for B5 the calculation will be:

=B2-B4

Your table should now look like this:

completed table

Step 2: Create your graph

Highlight rows 1, 2, 4 and 5 of your table and then click on Insert > Chart > Line chart.

insert line chart

Excel will create a line chart with 3 series as shown below:

initial line chart

Delete the legend and the gridlines.  Then right-click on the upper band series and chose Change Series Chart Type…

upper line seriesChange the chart type to an area graph (the first one in the list).  Repeat for the lower series.

Step 3: Format the confidence bands

By now your chart should look something like this:

combination line area chartTo finish the chart, simply format the upper series with a light blue fill (to match the dark blue line) and the lower series to have a white fill.

And that is it!

confidence bands

Both the confidence bar chart and the confidence band chart are in the sample worksheet here.  Let me know what you think and which you prefer!

+Alesandra Blakeston