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.
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.
This will give you a chart that looks something like this:
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:
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:
Then in row 4, you need to calculate the upper limit of your band i.e. for B4 the calculation will be:
In row 5, you need to calculate the lower limit of your band i.e. for B5 the calculation will be:
Your table should now look like this:
Step 2: Create your graph
Highlight rows 1, 2, 4 and 5 of your table and then click on Insert > Chart > Line chart.
Excel will create a line chart with 3 series as shown below:
Delete the legend and the gridlines. Then right-click on the upper band series and chose Change Series Chart Type…
Step 3: Format the confidence bands
By now your chart should look something like this:
And that is it!
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!