Views > How to create a Bivariate Area Chart in Excel

William Playfair was the first to publish time-series data that emphasize the difference between two curves. The chart below is one such example—taken from Playfair’s remarkable book The Commercial and Political Atlas—showing the shifting balance of trade between England on one side and Denmark and Norway on the other from 1700 to 1780.

A letter “r” is used to indicate that some of the budget figures have been revised. In order to do math operations on the data I first need to get rid of all the “r” letters. To do that:

1. Add the following formula in cell D2
  = VALUE(SUBSTITUTE(B2,"r",""))
2. Select cell D2 and press Ctrl + C to copy the formula into the clipboard. Then select the range D2:E476 and press Ctrl + V to paste the formulas.
3. Add headers to the newly created columns. The data source should look like this:

4. To calculate the 12-month rolling totals add the following formula in cell F13
  = SUM(D2:D13)
5. Select cell F13 and press Ctrl + C to copy the formula into the clipboard. Then select the range F13:G476 and press Ctrl + V to paste the formulas.
6. Add headers to the newly created columns. The data source should look like this:

The trick to create the shading between the two curves is to decompose the time-series into three components, that is, the minimum of the two curves, the surplus and the deficit.

7. To calculate the surplus/deficit add the following formula in cell H13
   = F13 – G13
8. To get the minimum curve, add the following formula in Cell I13
   = MIN(F13,G13)
9. To get the surplus shading, add the following formula in Cell J13
   = IF(H13 >= 0,H13,NA())
10. To get the deficit shading, add the following formula in Cell K13
    = IF(H13 < 0,ABS(H13),NA())
11. Select the range I13:K13 and press Ctrl + C to copy the formulas into the clipboard. Then select the range I13:K476 and press Ctrl + V to paste the formulas.
12. Add headers to the newly created columns. The data source should look like this:

HOW TO CREATE A BIVARIATE AREA CHART IN EXCEL

13. Highlight the range F13:G476 and then click Insert > Insert Line or Area Chart > Line. The chart should look like this:

14. Right-click the Vertical (Value) Axis and then, on the shortcut menu, click Format Axis.
15. In the Format Axis pane, with Axis Options selected, set the Display Units, to Thousands. Delete the Vertical (Value) Axis Units Label by selecting it and pressing the Del key on the keyboard.
16. Delete the Chart Title and the Legend. Change the Vertical (Value) Axis Major Gridlines Shape Outline > Dashes to Dash.
17. Change the Font to Trebuchet MS, its color to Black and its Font Size to 9.
18. Turn off the border of the Chart Area by setting the Border to No line. The chart should look like this:

19. Right-click the chart and then, on the shortcut menu, click Select Data.
20. In the Select Data Source dialog box, select Series1 data series and click the Edit button under Legend Entries (Series).
21. In the Edit Series dialog box, refer to cell F1 in the Series name edit box. Click the Ok button to close the Edit Series dialog box.
22. In the Select Data Source dialog box, click on the Edit button under Horizontal (Category) Axis Labels.
23. Next, in the Axis Labels dialog box, edit the Axis label range to include cells A13:A476. Press the Ok button to close the Axis Labels dialog box.
24. In the Select Data Source dialog box, select Series2 data series and click the Edit button under Legend Entries (Series).
25. In the Edit Series dialog box, refer to cell G1 in the Series name edit box. Click the Ok button twice in a row to close the Edit Series dialog box and the Select Data Source dialog boxes.
26. Right-click the Horizontal (Category) Axis and then, on the shortcut menu, click Format Axis.
27. In the Format Axis pane, with Axis Options selected, under Units, set the Major to 5.
28. Next, under Tick Marks set the Major type and Minor type to Outside.
29. Next, under Number set the Type to custom m/yyyy. The chart should look like this:

30. Select the range I13:K476. Press Ctrl + C to copy the data to the clipboard. Next, select the chart and press Ctrl + V to paste the data series.
31. Right-click the chart and then, on the shortcut menu, click Select Data.
32. In the Select Data Source dialog box, select Series3 data series and click the Edit button under Legend Entries (Series).
33. In the Edit Series dialog box, refer to cell I1 in the Series name edit box. Click the Ok button to close the Edit Series dialog box.
34. Do the same for Series4 and Series5 by referring to cells J1 and K1 respectively. Press the Ok button to close the Select Data Source dialog box.
35. Right-click the chart and then, on the shortcut menu, click Change Chart Type.
36. In the Change Chart Type dialog box select Combo under the All Charts tab.
37. Next, under the Choose the chart type and axis for your data series set the Chart Type to Line for the Receipts and Outlays data series, and to Stacked Area for the Minimum, Surplus and Deficit data series. The chart should look like this:

38. In the chart, select the Minimum data series and set its Shape Fill to No Fill.
39. Next, change the Shape Fill color of the Surplus and Deficit data series and set their Transparency to 75%.
40. Then, change the Shape Outline color of the Receipts and Outlays data series. The chart should look like this.

41. Resize the chart, add title, source of data, legend and some annotations directly on the chart. The chart should look like this:

The strength of this chart type is its ability to show the shape of the data over time while emphasizing the difference. For example, the chart below shows the 12-month rolling totals of outlays and receipts for the US government from Sep 1981 to Apr 2020. The positive trend of both lines is clearly apparent. Also one could easily see how these lines are moving up and down. The shaded areas between the two curves represent the balance of payments. Green represent a surplus and orange represent a deficit. Another important characteristic of the data encoded in this graph is recognizing whether a swelling deficit, for instance, is due to increasing outlays, shrinking revenues or both—or is it due to outlays growing at a higher rate than revenues?

William Cleveland, however, warned in his The Elements of Graphing Data that comparing two curves can be misleading. He used one of William Playfair’s charts to convey his point.

Here is another example from the same book where the lines appear to converge as the value of x increases—while in reality the difference between the two curves is constant.

Source: The Elements of Graphing Data, William S. Cleveland, 1994.

The idea is that it is more accurate to perceive differences measured with respect to a common fixed baseline than differences measured against a changing baseline. Therefore, if you are interested in the deficit then graph the deficit as per the graph below. Best option—of course—is to graph the data from different angles. Why not use both graphs?

It should be noted that the money figures in the above graphs are not adjusted for inflation. These are in nominal and not real billions of dollars. It’s worth remembering here one of Edward Tufte’s six principals of graphical integrity:

“In time series display of money, deflated and standardized units of monetary measurement are nearly always better than nominal units.”  

Enough of data visualization concepts. Lets build the chart with Excel.

The Monthly Treasury Statement of Receipts and Outlays of the United States Government (MTS) data—as downloaded from the U.S. Department of the Treasury website—looks like this:

Since I’m only interested in the Receipts and Outlays, I’m going to delete the data in columns D, E, F and G make few formatting changes to the table and the data table should now look like this:

If I chart the monthly receipts and outlays, I get a chart that looks like this. A lot of noise. Not much can be inferred from it. A better way to show the data is to use the 12-month rolling totals produced every month that eliminates any seasonal effects, smooth the data and put in perspective the impact of abnormal receipts or outlays.