Alternatives to Displaying Variances on Line Charts
Bottom line: It can be difficult to see the variance between two lines on a line chart. In this post I explain some alternative techniques to help highlight variances.
Skill level: Beginner
Nic asked a great question. He has line chart with two lines on it and wants to display the variance between the lines.
The variance between these two lines is actually very large in some months (over $200K). But the chart doesn't do a good job of showing it. This is because the scale on the vertical axis is quite large at a max of $6M.
So how can we display the variance better?
Solution #1: Display Variance Percentage on Chart
One possible solution is to add the percentage variance next to the line on the chart.
Normally this would be difficult to do, but Excel 2013 has a new feature that makes this easier. In the Format Data Labels menu you will see a Value From Cells option.
This allows you to select a range of values to add to the labels. For this example I calculated the variance in another column, then chose that column to display on the chart labels.
You can download the sample file below to see exactly how this works.
Solution #2: Display the Variance in a Different Chart
Often times we find ourselves trying stuff too much data in a single chart. I think this stems from putting charts on PowerPoint slides, and wanting to fit everything on a single slide.
However, I'm a fan of creating separate charts to display different metrics. This can really help you understand the trends better, and learn new insights about your data.
In the image above I put the variance % in a different chart, and placed it below the original trend chart. This allows the reader to clearly see the trend in the variance.
The variance from July to February is decreasing each month, while sales are increasing. This is obviously a seasonal business, and we will probably want to investigate why we outperformed our budget in the slow months.
The charts below just show another way to display the variances in a panel chart. A panel chart is just a group of charts.
In the panel above I also added a chart that displays the month-over-month growth for the Actuals. This is basically the slope of each line on the line chart, but it is nice to see the actual numbers.
Panel charts like this are great for dashboards. I also have a free macro that allows you to add zoom buttons to each of the charts if you are displaying them on an interactive dashboard.
You can download the file that contains all the charts below.
Display Variance On Line Chart.xlsx (83.0 KB)
Ask Your Question
If you have an Excel question you can post it on the Ask a Question page, and I will do my best to get it answered.
How Would You Approach This?
I would love to hear your thoughts on this issue. How would you display the variances between the line charts?
There are a lot of different ways to accomplish this and it would be great to learn from everyone.
Please leave a comment below with your solution. You can post a link to a screenshot or file if you'd like.