Excel - How to Create a Confidence Interval Chart - Episode 1846

2,191 views · Published 30 December 2013 · 7:00 · Indexed 30 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Confidence Chart in Excel

Welcome to the MrExcel podcast, episode 1846! In this episode, we have Gavin Jordan from London, one of the 16 finalists in the ModelOff 2013 Excel championships. Gavin will be showing us how to create a confidence chart without using a pivot table. This chart will help us visualize the likelihood of a result falling within a certain confidence level over time.

The basic data for this chart includes minimum, maximum, and average values. When plotted as a line graph, it is not very clear or exciting. However, Gavin will show us how to transform this into an area chart that clearly shows the 95% and 50% confidence intervals, as well as the average value. This chart is especially useful for financial modeling and predicting future outcomes.

To create this chart, Gavin first manipulates the data by calculating the difference between the minimum and low values, and between the high and maximum values. He then plots this data as a stacked area chart, with the average value shown as a line chart. By removing the color for the minimum value and coloring the 95% and 50% confidence intervals in light and dark gray, respectively, the chart starts to take shape.

Gavin then changes the color of the line representing the average value to black and removes the legend, replacing it with labels to explain the different areas of the chart. This chart can be customized with different confidence intervals and colors, making it a versatile tool for various scenarios. It is also commonly used in weather forecasting, such as the hurricane cone chart.

Although Gavin is known for his expertise in pivot tables, he was able to demonstrate his skills in creating this confidence chart. So for all his co-workers who were expecting a pivot table tutorial, this one's for you! Thank you for tuning in to this episode of the MrExcel podcast, and don't forget to check out the ModelOff championships. See you next time for another informative netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Confidence Chart (without a pivot table)
(00:21) Steps to create the confidence chart
(01:33) Manipulating the data
(03:23) Creating a stacked area chart
(04:52) Formatting the chart
(05:32) Practical applications of the confidence chart
(06:47) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #charts #excelcharts #excelchartstutorial

This video answers these common search terms:
how to make a bar chart with confidence intervals already calculated  in excel
how to make a bar chart with confidence intervals in excel
confidence interval chart
excel confidence level plot
statistical certainty chart
confidence range graph
margin of error chart
excel error margin visualization
certainty plot in excel
confidence band chart
statistical precision graph
excel confidence boundary chart

In this episode from ModelOff, PW's Gavin Jordan shows how to create a confidence chart - an interesting way to show increased risk in a forecast as the forecast extends out over the next 15 years. This chart reminds me of the Hurricane Cone charts that are published in the newspaper in Florida.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153671/

More from this channel