An area chart (or an area graph) comes in handy when plotting a set of metrics over a period of time. An area chart can also be transformed in to a band or a spline chart so that it represents changes in a metric within a certain band over a period of time. Shown below is an area chart which has been transformed in to a chart with a band (spline) by using two data series one of which is hidden. The band in the middle of this chart covers the area between the respective high and low points of that metric at each point and extends across a given time horizon.
However the default area charts in Excel suffer from a severe drawback. As shown below, when you construct an area chart with two or more time series, you tend to get overlapping area plots which tend to ‘hide’ the data points of the series below. (See the example below. The data plot colored in black hides the area behind it thus preventing the user from getting to know the value of the ‘hidden’ metric during that time frame.)
This problem remains as acute when you need to show multiple overlapping bands rather than areas.
To overcome this problem, one can draw two time series splines, the first one with solid fill and the other with a transparent fill. The result is an area chart with two time series that do not overlap each other.
Steps to construct an area chart with non overlapping data series:
1. Construct a normal area chart with overlapping series.
2. For the first series, double click the series representing the low points. In ‘Area’ choose color as white and ”Border’ as ‘none’.
3. For the second series, double click the series representing the low and the high points. In ‘Area’ choose color as ‘none’ and ”Border’ as grey.
4. Delete the chartjunk from the chart (such as gridlines, unnecessary borders etc.)
If you wanted to add a few more series to the chart, repeat step 3 for each of the additions and just keep on changing the color of the border so that it stands out in contrast with all the other series.
And well…there we are with our brand new area chart having non overlapping data series.