Area charts are a great option when we need to show trends over time while also emphasizing the magnitude of values. They allow us to compare multiple series or variables easily, while the filled area illustrates the magnitude of values, and they also show how a part contributes to the whole over time.
In this post, I’ll share different ways to plot them so you can compare the pros and cons with each. I won’t separately discuss stacked versus non-stacked area charts other than to say that the stacked area charts are useful for showing the cumulative effect of different series or variables; the rest of the discussion applies to both stacked and non-stacked kinds. I’ll cover creating the charts in Excel, Python, and PowerBI.
Suppose we have a range of dates and their associated values (can be volume, count, revenue, whatever) for two different variables (we’ll call them Series A, and Series B here), and we want to plot an area chart showing both series. We can chart them quickly in Excel and a simple version would look like this:
We can notice in the above plot that not all Series A values are visible as some of the Series B values are obscuring some of the Series A values. We can fix that by changing the transparency to, say, 50% for each ensuring that both areas will show through in all cases. That version looks like this:
This is better as we can see all the values from Series A now. Both series are in the same Y-axis scale. In some cases though, we might need to place them in different axes. Although this is not necessary in this example, it’s good to know that it putting one series in primary axis and another in secondary axis can be useful when there are too many overlaps or the ranges of the series are very different making one go off the chart or be too small an area to deduce any meaning. Here’s an example of putting them in different Y-axes:
Now in order to read the values for Series A we need to look at the left Y-axis, and for Series B, the right Y-axis. Although they both start at 0 and both are fitted in the same plot area, Series A values are at different steps and range up to 100, while Series B values’ axis range up to 120. If you feel your audience would be confused by this, or you don’t want to clutter it with two axes but still show accurate measurements, you can set the minimum and maximum of both Y-axes to the same range, where it would make sense to choose the one with highest range. Here is a version where Series A is in primary axis, and Series B is in secondary, but the primary Y-axis’s maximum value is set to match the upper range of Series A (i.e. 120), and then the secondary Y-axis is removed or hidden to make the chart cleaner (while rendering all areas accurately along the remaining left Y-axis):
So far so good. But this edgy look with sharp edges for the areas may not be aesthetic to some. For a smoother look, we could make the transitions from one datapoint to another less prominent while still keeping the datapoints start and ending values the same. We could achieve this by adding more in-between datapoints to make the transition from one month to another smoother but that would require us to calculate the values ourselves and alter the dataset. A more common approach is to let the charting tool do the work. However, in Excel (I’m using latest 365 version) there’s no line smoothing option for area charts although there is for line charts!
So, if there’s no other alternative than to use Excel, one approach would be to add two more series using the same Series A and B values again on but this time, instead of area charts, they’ll be added to the same chart as line charts. Once they’re added, using Excel’s formatting pane, we can set them to smooth lines. The result will be that the smooth lines will act are the top outlines for each area making the overall chart look smoother. Immediately underneath those smooth lines will still be the sharp area charts but since their values are the same, they can be “hidden” (technically, paretially covered by the smooth lines) by adjusting the line colors to be same as the area colors for the respective series, and the line border to ensure there are no gaps between area and line peeking through in the datapoints transitions. This is kind of a hack but it works with some extra effort when Excel is the only choice and a smooth area chart is required. Here’s an example of this created using this technique:
The borders for line charts (SeriesA-Line, SeriesB-Line) are set to be thicker with transparency to match their corresponding area charts to cover any gap between them and the area charts underneath. The series labels (SeriesA-Line, SeriesB-Line) should be removed individually from the legend (I left them on here for illustrative purposes so it’s easy to follow).
The above version made in Excel gets the job done but we can do better if we’re open to other methods such as using PowerBI or doing a little coding in Python. Let me show you both examples along with the Python code to achieve this.
PowerBI Method
Intrinsicly PowerBI supports smoothed area charts requiring very little customization out of the ‘box’. It creates beautiful splines for the edges as shown below.
Python Method
If you’re familiar with Python, you can also use Python’s matplotlib with scipy interpolation. The default resuling plot looks like this (which can be further customized in code):
The code to create the area chart:
In the code (line 41), I instructed the library to create a few hundred datapoints to render the splines dynamically based on the number of datapoints; this way, when the dataset shrinks or expands, the smoothness will be automatically adjusted instead of me tweaking it manually. However, that’s just optional, you could just enter a hard-coded number in the hundreds and in most cases, it may be smooth enough for your needs. I hope this was informative for you. Thanks for reading and keep exploring!
To help me continue to produce and share more content that’s informative, and ad-free, please consider making a one-time or a recurring small donation by clicking here (securely transacted via PayPal™).
