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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920