A fan chart, or uncertainty chart, as I like to call them, is a way to display historical data along with a prediction of future values. For example, they are often used to indicate inflation or exchange rate predictions, but can display any data with an uncertain future value. So how can we create a fan chart in Excel? They look similar to line charts, but the future values fan out to represent the range of possible future values.
The term “fan chart” was coined 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.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be helpful for future reference.
Download the file: 0069 Fan chart in Excel.zip
Watch the video
The data
The data layout is one of the most critical factors for creating a fan chart. If we get this part wrong, the chart will not look how we expect. So, let’s take a bit of time to understand it.
In our example, we have taken 12 periods of actual results (Cells B7:B18) and 12 periods of future prediction (Cells C19:C30).
Cells D3 and E3 are set to the minimum and maximum values of the prediction. These are just for illustration; you will need to calculate estimates suitable for your scenario.
Cell G9 represents the number of fans to display. This needs to be an odd number to ensure there is a center fan, with an even number of fans above and below.
The calculations
I have used a Table to calculate and store the values in this scenario. It is possible to calculate using standard cell ranges, but you will need to adjust the formulas accordingly.
The calculations required are:
Final Forecast
Cell C3 calculates the last value from the Forecast column. We use this in some of the coming calculations.
=INDEX(Data[Forecast],ROWS(Data[Forecast]))
Min Column
The formula in the Min column is:
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])* COUNT(INDEX([Forecast],1):[@Forecast])
This is the most complex formula in the entire Table, so let me break it down for you.
The difference between the min and the forecast at the end of the chart is -2.2 (D3 less C3). This is calculated in the highlighted section below.
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])*
COUNT(INDEX([Forecast],1):[@Forecast])
We want the fan size to increase the further the chart goes out into the future. In our scenario, we have 12 forecast periods, so let’s divided the value calculated above by the number of forecast periods (see highlighted section below)
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])* COUNT(INDEX([Forecast],1):[@Forecast])
Having divided the value by the number of periods, we next want to multiply by the number of periods. This needs to increase as we move down the Table.
The first forecast period needs to calculate as 1, the second period must calculate as 2, the third period must calculate as 3, etc. We achieve this with a dynamic range (see the highlighted section below).
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])* COUNT(INDEX([Forecast],1):[@Forecast])
Finally, we add the forecast value (see the highlighted section below) to calculate the lowest point of the fan.
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])* COUNT(INDEX([Forecast],1):[@Forecast])
Max
The calculation of the maximum value is similar to the minimum calculation. The only difference is the calculation points to the Max, rather than the Min value. The differences are highlighted below.
=[@Forecast]+($E$3-$C$3)/COUNT([Forecast])*
COUNT(INDEX([Forecast],1):[@Forecast])
Base
The Base column calculates the area below the line and fan.
=[@Actual]+[@Min]
Fan Size
The size of each fan is calculated as:
=([@Max]-[@Min])/$G$3
Create the fan chart
We’ve got all the calculations; now it’s time to create the chart.
Insert the chart
Select all the data in the Date, Base, and Fan Size columns.
Click Insert > Charts > Stacked Area to insert a chart.
We only have one fan in our chart at present, so let’s add 8 more.
- Select the Fan Size column
- Copy the data (Ctrl + C)
- Select the plot area of the chart
- Paste the data into the chart (Ctrl + V)
Repeat the steps above until there are 9 fans.
Delete the chart legend and chart title.
The chart now looks like this:
From now on, it’s all about formatting.
Format the base (the blue section below the line and fans)
- Select the bottom blue area of the chart
- Right-click and select Format Data Series… from the menu
- Change the formats to be no fill and no line
Format the center fan
Select the center fan.
Format with a solid fill and solid line with a color and 0% transparency.
Format the other fans
Finally, format each fan with the same color as the center fan, but with increasing transparency. The further the fan is away from the center the higher the transparency value.
In our example, we have 4 fans above and 4 fans below. Therefore, the transparency values applied are 20%, 40%, 60%, and 80%.
The final chart
And we are done. The final chart now looks like this:
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
génial, merci énormément pour le partage.