This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

How to create an uncertainty chart (fan chart)

How to draw an uncertainty chart

Excel - how to draw an uncertainty chart (fan chart)

Originally posted: 1 December 2016, updated: 4 April 2017

An uncertainty chart, or a fan chart as they are known, is a way to display historical data along with a prediction of future values.  They are often used to indicate inflation or exchange rate predictions, but can be used to display any data with an uncertain future value.  They look like a line chart, but the future values fan out to represent the range and probability of future values.

The term “fan chart” was created by the Bank of England who started using this method for displaying inflation in 1997.

The image below shows the finished fan chart which we will be creating in this tutorial.

Final Fan chart

You can download the workbook here.

 

The data

The layout of the data is one of the most important factors in creating a fan chart.  If we get this part wrong the chart will not look how we expect it to.

Fan chart data

In our example, I have taken 18 periods of actual results (Cells B2 – S2) and 6 periods of future prediction (Cells T2 – Y2).


Advertisement:

I have then set the minimum result for the first 18 periods equal to the actual (Cells B3 – S3).  But the minimum for the future periods are purposefully created to be further away from the Result line as each period progresses (Cells T3 – Y3).

The maximum result has no values for the first 18 months (Cells B13 – S13).  For future periods the values diverge from the Result line by the same amount as the minimum, but in the opposite direction (Cells T11 – Y11).

Between the minimum and maximum, I have created various increments to represent the fans in the uncertainty of the future prediction.  In our example, there are 9 different fans, but you could create any number.  Though, if you wish to have a single color in the middle it is easier to use an odd number, rather than set the inner colors to be the same.

It is not essential for the fans to be the same size as each other and depending on your requirements you might decide specifically to have different sizes.  But, it is essential that the Minimum plus the increments equal the Maximum.

To create the fans, type the following into Cell T4:

=(T$11-T$3)/9

This formula is based on Maximum minus minimum, then divided by the number of fans.  Copy this formula across into Cells U4 to Y4.  Then copy Cells T4 – Y4 down to Cells T12 – Y12.

 

Create the fan chart

This chart is actually a combination of two charts; a line chart for the result, which is on top on top of a stacked area chart for the fans.

Select cells A1 – Y12 (we can ignore the Maximum, as it was purely used to calculate the size of the fans.

Click: Insert -> Charts -> Area -> Stacked Area (this tutorial is created using Excel 2016 the other versions of Excel may vary slightly).

Fan chart - insert stacked area chart


Advertisement:

A chart similar to the following should appear:

Fan chart - first view of chart

Next, right-click the bottom data series (showing as blue – Series 1 in the screenshot above).  From the menu select “Change Series Chart type . . .”

Fan chart - change series chart type

Change to chart type to a line chart.

Fan chart - change result to line chart

All the hard work is now done, from here on out it is all formatting.

Now, change the fill of Series 2 (the orange colored area) to be transparent.  Right-click this area and select the Fill paint can, then click No Fill.

Fan chart - change bottom area to no fill

Next, select the legend (the box with all the Series listed), press Delete to remove the box.

Right-click each of the fans and change the Fill.  We want to select colors for each fan which gives a lighter color on the outside fans and darker colors on the inside fans, or if we could use increasing levels of transparency – which also has the advantage of displaying the gridlines, should you want them.


Advertisement:

Fan chart - format the fans

Format the Result line in a similar way.

Fan chart - format line

Right-click on the bottom axis. Within the Format Axis window select Axis Options -> Axis Position: – On tick marks

Fan chart - format axis on tick marks

Now we have a beautiful Uncertainty Chart (Fan Chart).

Final Fan chart

 

What Next?

Do you want to create beautiful, easy to read and insightful charts all the time?  If so, I high recommend you get Effective Data Visualization: The Right Chart for the Right Data(affiliate link).  This is one of my favorite Excel books.

Save

Save

Save