Views > How to create a Fan Chart in Excel

Say you are a financial analyst working for an international chain of fast food restaurants. You’re working on a five-year business plan for the company’s U.S. operations. One of your tasks is to show how you expect the count of restaurants to evolve over the next five years. You’ve looked at the historical count and assessed three scenarios—the base case, the best case and the worst case. One way to present your analysis is through a Fan Chart as shown below.

17. Select the range G3:I20. Press Ctrl + C to copy the data to the clipboard. Next, select the chart and press Ctrl + V to paste the data series. The chart should look like this:

21. Press the Ok button to close the Change Chart Type dialog box. The chart should look like this:

22. Right-click the Vertical (Value) Axis and then, on the shortcut menu, click Format Axis.
23. In the Format Axis pane, with Axis Options selected, under Bounds, set the Minimum to 4,600.
24. In the chart, select the Minimum data series and set its Shape Fill to No Fill.
25. Next, change the Shape Fill color of the Low and High data series and set their Transparency to 75%. The chart should look like this:

HOW TO CREATE A FAN CHART IN EXCEL

26. To add the grey shading defining the historical part of the chart, add new data in column J as per below screenshot:

27. Select the range J3:I20. Press Ctrl + C to copy the data to the clipboard. Next, select the chart and press Ctrl + V to paste the data series (Note that you’ll not notice any change on the chart because the added series values are very small compared to the other data series).
28. Right-click the chart and then, on the shortcut menu, click Change Chart Type.
29. Next, under the Choose the chart type and axis for your data series set the Chart Type to Area for the HistShade data series and turn on the Secondary Axis as per the screenshot below:

30. Press the Ok button to close the Change Chart Type dialog box. The chart should look like this:

31. Right-click the Secondary Vertical (Value) Axis and then, on the shortcut menu, click Format Axis.
32. In the Format Axis pane, with Axis Options selected, under Bounds, set the Maximum to 1.
33. Next, turn off the axis labels by setting the Labels Position, under Labels, to None.
34. Next, change the Shape Fill color of the HistShade data series and set its Transparency to 75%. The chart should look like this:

35. Next to add the data labels to the chart add the following formulas in the respective cells to calculate the CAGR:
    C21 -->  = (C15 / C4)^ (1 / 11) – 1
    D21 --> = (D20 / D15)^ (1 / 5) – 1
    E21 --> = (E20 / E15)^ (1 / 5) – 1
    F21 --> = (F20 / F15)^ (1 / 5) - 1
36. Next, add the following formulas in the respective cells to combine the series name with the CAGR values:
    C23 --> = C3 & " (" & TEXT(C21,"+0.0%;-0.0%;-") & " CAGR)"
    C24 --> = D3 & CHAR(13) &"(" & TEXT(D21,"+0.0%;-0.0%;-") & " CAGR)"
    C25 --> = E3 & CHAR(13) &"(" & TEXT(E21,"+0.0%;-0.0%;-") & " CAGR)"
    C26 --> = F3 & CHAR(13) &"(" & TEXT(F21,"+0.0%;-0.0%;-") & " CAGR)"

I used the CHAR(13) function to force a carriage return to the text. The data source should look like this:

37. In the chart select the Best case data series. Next, click on the last data point to select it.  Right-click the selected data point, and on the shortcut menu, click Add Data Label.
38. Select the added Data Label and then, in the Formula Bar, type the equal sign "=" followed by a mouse click on cell C25. Press the Enter key on the keyboard.
39. Repeat the above steps for the Base case and the Worst case data series by linking to cell C24 and cell C26 respectively.
40. Next add a Text Box shape and link it to cell C23. Add another Text Box shape and edit it to display “Forecasted”.
41. Change the color of each data label to match the color of the respective line. Make them Bold and Left Align. Resize the Plot Area so that the labels do not overlap with the lines.
42. Right-click the Horizontal (Category) Axis and then, on the shortcut menu, click Format Axis.
43. In the Format Axis pane, with Axis Options selected, set the Axis position to On tick marks.
44. Next, under Tick Marks, set the Major Type and Minor Type to Outside.
45. Add a chart title and data source. Resize the chart and by now it should look like this:

The data source for the above graph is shown in the table below.

1. Highlight the range C3:F20 and then click Insert > Insert Line or Area Chart > Line. The chart should look like this:

2. Fill the gap between the historical count and the forecast by linking cells D15, E15 and F15 to cell C15.
3. Right-click the chart and then, on the shortcut menu, click Select Data.
4. In the Select Data Source dialog box, click the Edit button under Horizontal (Category) Axis Label.
5. In the Axis Label dialog box, edit the Axis label range to include cells B4:B20.
6. Click the Ok button twice in a row to close the Axis Label and Select Data Source dialog boxes. The chart should look like this:

7. Lets do some formatting by deleting the Chart Title and the Legend.
8. Change the chart Font to Trebuchet MS, its color to Black and size to 9.
9. Change the line Dash type of the Vertical (Value) Axis Major Gridlines to Dash.
10. Turn off the border of the Chart Area by setting the Border to No line.
11. Change the Color and the Shape Outlines Dashes type of the line series. The chart should look like this:

The trick to create the shading between the three scenario curves is to decompose the time-series into three components, that is, the minimum of the three curves, the difference between Base Case and Worst case and the difference between the Best Case and Base Case.

12. To get the minimum curve, add the following formula in Cell G4
      = MIN(D4:F4)
13. To get the lower shading, add the following formula in Cell H4
      = D4 - F4
14. To get the higher shading, add the following formula in Cell I4
      = E4 - D4
15. Select the range G4:I4 and press Ctrl + C to copy the formulas into the clipboard. Then select the range G4:I20 and press Ctrl + V to paste the formulas.
16. Add headers to the newly created columns. The data source should look like this:

18. Right-click the chart and then, on the shortcut menu, click Change Chart Type.
19. In the Change Chart Type dialog box select Combo under the All Charts tab.
20. Next, under the Choose the chart type and axis for your data series set the Chart Type to Line for the Historical, Base case, Best case and Worst case data series, and to Stacked Area for the Minimum, Low and High data series as per the below screenshot: