Excel Fan Chart Showing Uncertainty in Projections

A reader emailed me this chart of GDP, with actual values through late 2008 and projected values for the next two years. To illustrate the uncertainty in the predictions, colored bands were drawn alongside the solid line prediction. The bands become lighter as the distance from the prediction line increases. Because of this appearance, showing the data fanning out, this type of chart is called a Fan Chart.

The reader wondered how to create this shaded-band effect in a fan chart. This tutorial was developed using Excel 2010, but the procedure is applicable to all Excel versions.

The chart comes from page 34 of A Preliminary Analysis of the President’s Budget and an Update of CBO’s Budget and Economic Outlook, published in March 2009 by the Congressional Budget Office.

I’ve manually digitized the actual data up to 10/1/08 and the projections from 1/1/09 onward into this tall thin tower of numbers.

Here is the data plotted in a line chart.

I’ve set up the following table to capture the data for the fan. Midline contains the actual and projected values from above. I made up Min and Max so that the fan would grow wider for each successive quarter. I then created values in a and b to split the fan below the midline into three shades of gray, and in c and d to split the fan above the midline into the same three shades of gray. You can use more or fewer calculated columns to increase or decrease the number of shaded regions.

My numbers are arbitrarily calculated. If you have some probabilistic means of generating such values, you should use your calculations instead.

When I add these columns of data as additional lines in the chart, I get the following chart. I’ve changed the default kaleidoscope of line colors to shades of gray that lighten with increasing distance from the midline. The labels indicate which column in the table above contains data for each line. This chart is fine, but it doesn’t show the filled area effect.

To achieve filled regions, I will use a stacked area chart. In an adjacent range I’ve added the following table. The dates are the same, as are the minimum and midline predictions. The rest of the data are calculated differences between each line and the previous line, to capture the height of each colored band (distance between lines). For example, the Max value for 1/1/09 in the table below (33) is the difference between the Max value for 1/1/09 in the table above and the d value for 1/1/09 in the table above (11400 minus 11367).

I’ve created a stacked area chart using this table of data. Here are my colored bands.

The first thing I did was format the axes and delete the gridlines.

Then I converted the Midline series to a Line Chart. This is done by right-clicking on the series, choosing Change Chart Type (or simply Chart Type in older Excel versions), and selecting a line chart style.

Next I made the Min series invisible by formatting it to have no fill.

Then I rescaled the vertical axis, because I don’t want all that empty space at the bottom.

Finally I formatted the Midline to use dark gray for its line, and the area series to have lighter and lighter gray fills the further they are from the midline. I’ve also deleted the legend.

Related Posts:

• Excel Charts With Horizontal Bands
• Contour and Surface Charts in Excel 2007
• Plotting Values Against Limits
• How to Make Vertical Bullet Graphs in Excel
• Adding an Arbitrary Target
• LOESS Smoothing in Excel