Better Variance Charts in Excel: 4 Ways
You've been asked to visualize actual sales by company.
You have a couple of companies. A column chart provides an easy comparison. It works well here.
Now you've been asked to visualize the % change to previous year for each company.
Here are some alternatives:
Let's take a look at each method:
Method A: Columns for Variance
Method A uses a column chart with dynamic coloring of the columns to distinguish between positive and negative – AND conditional colors for data labels. If you'd like to find out how to conditionally color data labels and bars in Excel graphs, take a look at this video:
The technique in the video shows a variance column chart but it works in the same way for a bar chart.
What I don't like about Method A is that the variance chart is too bulky - It takes away attention from the actual sales numbers in the main column chart.
Method B: Lines for Variance
Method B uses much thinner bars / lines to distinguish between positive and negative variances – it’s less bulky compared to A and more elegant. It does use a special technique.
In this video, I show you how to use the Excel error bar technique to create really thin bars (or thick lines) which have a more subtle effect. The result is a visually effective chart that doesn't distract the readers attention from the main chart but also communicates the difference to previous year visually as well.
On top, it also conditionally formats the bars so they are green if the data points are positive and red if they are negative. This technique works for Excel 2007, 2010, Excel 2013 and Excel 2016.
Method C & D: Arrows for Variance
Method C uses dynamic arrows that are conditionally formatted and placed on top of the original column chart & Method D uses the same technique, except the arrows are placed below the horizontal axis of the original column graph.
In this video you will learn how to create an Excel "arrow" chart that shows variances with conditionally colored arrows. You can use this to show the variance to budget, variance to outlook / forecast, variance to previous year information etc....
You can download the workbook HERE.
Now over to you - which method do you prefer?
Business Intelligence Analyst, Innovator
7 年I like the second method; it's stylish while conveying information on both the target and variance against it (obviously the first method does the same, but it's too colourful and we don't want our audience to get oo distracted). What about the last two methods? These are really masterful ways of doing the same thing, but - again - I would be cautious and try to divert the attention from the presenter's Excel skills to the information. Hm! Now that I think more about it, I actually like the last two as well. Whichever method is used, however, is kind of important to remain consistent and keep using it, unless we hear otherwise from our partners. Again I say (because it is fundamental) - the visualisation techniques should help them make proper decisions.