Chart with confidence intervals

In this article, we will try to learn the method for creating charts with confidence interval in Excel.

Adding confidence intervals for a graph

In Microsoft Excel, you can create a chart with confidence intervals to display the range of values that are likely to contain the true value of a set of data with a certain level of confidence. Here’s how to create such a chart:

Start by creating sample sales data for a week. Now let us create more data using stdev functions and it will show up as follows:

confidence intervals data set

Let us calculate the differences (upper band and lower band) as follows:

Upper band formula: =B2+ B3

Upper band formula

Lower band formula: =B2 – B3

Lower band formula

Creating a confidence interval graph

Let us try to create the 2D chart as follows:

2D line chart

Now we have to change the chart type to an area graph:

area graph

It will look like this now:

area chart example

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

chart with confidence intervals

Both the confidence bar chart and the confidence band chart are in the sample worksheet here. It depends upon what you prefer it to look like.

See also  Chart that Ignore N/A! Errors and Blank Cells