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.

Juan Pablo González says

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

Tom says

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.

Jon Peltier says

Tom –

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

Tom says

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.

Dustin says

Jon- This was great. Thank you very much!

Katerina says

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

Jon Peltier says

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

Paul says

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

Jon Peltier says

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.

Maz says

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

Anonymous says

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

Random says

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

Jon Peltier says

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

Dominik says

Hello,

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

Dominik says

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

Zameer Ahmed says

Please provide us the detail charts of axial fan calculation charts with motors and accessories.

I hope the above mention articles are send us.

Thanks

Jon Peltier says

Zameer –

Wrong kind of fan. This has nothing to do with HVAC.

Louise says

This is a really helpful tutorial – thankyou – I am struggling with the stacked chart stage

“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”

If I create a new chart using that as stand alone data (not using the first set of data at all) my fan numbers sit at the bottom of the chart and don’t join the midline numbers

I feel as if I am missing something basic or not putting the data together in the right way…

Thanks!

Jon Peltier says

The first set of data in my chart “Min” is transparent, and all the other data floats on it.

Blanca Romero says

Hi Jon! Thank you for the tutorial, it is very good. However, i am having problems to do it in my Excel. Once I have the simple line and all de data, I don’t know how to add extra data. Could you please send me your Excel book so I have the version in excel? Thank you very very much. I am really lost.

Thanks!

Jon Peltier says

Blanca –

Did you generate the data range with the differences? This is the last worksheet screenshot in the tutorial.

From this data range, create a new chart using the stacked area chart type. Right click on the Midline series, select Change Series Chart Type, and change it to a line chart type. Then format the other area chart series.