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.

Example Fan Chart

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.

Actual and Projected Data

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.

Line Chart Data for Fan Chart

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).

Area Chart Data for Fan Chart

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

Fan Chart Step 1: Stacked Area Chart

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

Fan Chart Step 2: Format Axes

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.

Fan Chart Step 3: Convert Midline to Line Chart

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

Fan Chart Step 4: Make Min Series Invisible

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

Fan Chart Step 5: Reset Vertical Axis Scale

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.

Fan Chart Step 6: Format Line and Fill Colors

Peltier Tech Chart Utility

Comments

  1. Juan Pablo González says:

    I really love your tutorials Jon. You really have a talent for making it look simple!

  2. Hi, I have a little Problem with this Chart. In the final stage I have an unexpected line, Starting in a junction and going down strait to the (X) Axis… Could you help me to hide this unnecessary line?
    Thanks in Advance.

  3. Tom –

    The line might be a border if your area series have borders. Remove borders and the line should go away.

  4. Jon—
    Thanks, I turned off every border and in the end, finally there was no line :) Thank you for your support. Actually I might use this chart type for some report in my work.

  5. Jon- This was great. Thank you very much!

  6. Or one can just use the Area Band in E-views to create the fan graph without having to do any calculations or adjustments

  7. Wow, E-views is over $1000 per user.

  8. Does anyone know the technique/fomula used to produce the data that generates the fan? Is it somekind of “weighted” confidence interval?

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

  9. You ask for “the” technique or formula used to generate the fan. I’m sure the approach used by most people is wild-ass guessing. Other than that, some kind of confidence interval at least sounds good. It’s more likely that people decide on some kind of +/- percentage which increases the further out you go.

  10. This is fantastic! I can see myself using this. Has anyone written a macro to automate this?

  11. This is far too hard to follow. Needs to be made clear but thanks for trying

  12. Prob best site on web for fan chart, but every time I want to make it, it takes ages, really fussy. Could you please upload a template perhaps? or re-do with just 3 columns or something?

    Thanks

  13. If you’ve made even one fan chart, you’ve already got your template.

  14. Hello,
    Does anyone know how to create a fan chart with both negative and positive values on the Y axis?

  15. OK, thanks to the ‘trial and error’ method, I have done it. :-)

Subscribe without commenting

Trackbacks

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites