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.
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.
In our example, I have taken 18 periods of actual results (Cells B2 – S2) and 6 periods of future prediction (Cells T2 – Y2).
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:
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).
A chart similar to the following should appear:
Next, right-click the bottom data series (showing as blue – Series 1 in the screenshot above). From the menu select “Change Series Chart type . . .”
Change to chart type to a 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.
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.
Format the Result line in a similar way.
Right-click on the bottom axis. Within the Format Axis window select Axis Options -> Axis Position: – On tick marks
Now we have a beautiful Uncertainty Chart (Fan Chart).
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.
Not sure if this book is for you? Read my review.