Bottom Line: Learn step-by-step how to create a shaded deviation line chart in Excel, either with only one color (simple version) or with different colors for positive and negative deviations (advanced version).
Skill Level: Intermediate
Video Tutorial
If you are working with time series in Excel and want to compare target and actual values over time, there are different visual approaches how to do that. One outstanding visualisation technique is to shade the area between the target and actual line graph.
This advanced data visualisation technique allows the viewer to fully focus on the relevant deviations and it looks outstanding the same time. So let’s start improving your data visualisation portfolio in Excel and learn that technique step-by-step.
You will learn how to build two different versions of this chart.
The simple version shades the whole deviation area with one semi-transparent color, no matter if the deviation is positive or negative.
Simple version (one color)
Advanced version (two colors)
The more advanced version (in the bonus section at the end of this post) allows you to shade the positive and negative deviations with two different colors.
So let’s get started. You can find the Download of the Worksheet at the end of this post.
Table of Contents
Creating a simple deviation line chart
Start with a simple Excel line chart
Let’s take a look at a sample dataset with a plan and actual value over a period of 12 months. For this dataset, we start by inserting a simple Excel line chart via Insert > Line Chart > 2DLine Chart.
We already see, the actual value has some positive and negative deviations compared to the plan value over time. However, to make the deviation area shaded with a beautiful semi-transparent design, our raw dataset is not enough.
Create additional time series
We need to create two additional data series.
- Starting Point: This data series is perfect duplicate of the Plan Value data series, so we can easily create it by referencing the Plan value column
- Deviation: This data series represents the difference between the Actual and the Plan Value for each months. So the calculation is basically Actual Value – Plan Value in each row.
After we have created these two additional data series, we can include them into the chart’s source data range by clicking on the chart and simply expanding the source data range with the mouse. The new chart then looks like this.
What we have now is simple line chart with four separate lines.
The Starting Point line perfectly covers the Plan Value line (as both share the exact same values), and the Deviation line swings around the base line.
Transform the data series chart types
The magic trick now is to transform these two additional lines into a different chart type.
For that, you have to right-click on one of the lines and click on Change Series Chart Type. That will open a pop-window in which you can manage the Chart Type for each individual data series.
In there, we now have to change the Chart Type of the Starting Point and Deviation Series to Stacked Area Chart Type. The result should look like this.
Both new data series are now stacked on top of each other. Since the Starting Point data series is a perfect duplicate of the Plan Value, the area below the Plan Value line is fully shaded in grey now. The Deviation data series has some positive and negative values, so if it is stacked on top of the Starting Point series, it either adds an area on top or takes a part of the grey area away. That’s the whole trick!
As a last step, we now have to remove the grey area by right-clicking on the area and setting the Fill of this data series to No fill.
After that we can start to design the chart to make it look fantastic.
Finalise the chart design
To add a beautiful design to this chart, we recommend to do the following steps:
- Change the Plan Value line color to something strong (like black)
- Change the Actual Value line color and the Deviation Area Fill color to the same color (like blue)
- Change the transparency of the Deviation Area fill to something between 40-50%
- Add little markers to the Actual Value Chart, make their fill white and add a slim border with the same color as the Actual Value line
- Remove the Starting Point data series from the legend. That doesn’t remove the data series itself and in the legend we don’t need it to be displayed.
After taking these steps, your result should look like this.
That is already the whole process for the basic version of this deviation line chart in Excel.
Creating an advanced deviation line chart
For the more more advanced version of the deviation line chart in Excel with two separate colors for negative and positive deviations, the overall process is more or less the same.
The main difference for this advanced chart is: We need two deviation data series instead of only one. One for the negative deviations, and one for the positive deviations.
For both deviation data series, We use the IF function to check if the difference is negative (or positive) and only if that’s the case, we insert the difference, otherwise set the cell to 0. That gives us separate data series that only have zeros and either negative or positive deviation values.
We now have three data series that need to be transformed into a stacked area chart. After we have done the transformation as described in Step 3, we are set and done to format both deviation areas separately and give the chart this unique look.
Conclusion
The shaded deviation line chart is a clean and beautiful chart technique that will impress in every report or dashboard. For an instant insight into all techniques covered in this tutorial, download the worksheet from our Download Area.
