Contents
In a nutshell: It can be very difficult to see the difference between two lines on a line chart. In this article, I will show you several ways to show the difference on a chart in Microsoft Excel.
Difficulty level: Newbie.
My friend has an interesting question. There is a chart with two line graphs on it. It is required to show on the diagram the difference between these lines.
The difference between the two data series in some months is very large (more than $200000). But the graph fails to show this difference clearly enough. And all because the scale of the vertical axis is also very large – $ 6000000.
How can you make the difference more visible?
Solution #1: Plot the Percentage Difference on a Chart
One of the possible solutions is add percentage difference values next to the graph line.
This would be quite difficult to do if not for some new features in Excel 2013, which greatly simplify the task. On the Format panel, under Data Label Format (Format Data Labels) there is a parameter Values from cells (Value From Cells).
This option allows you to select a range of cells whose values will be added to the data labels. As an example, I calculated the difference in a separate column and added that column to the chart data labels.
Solution #2: Show the difference as a separate graph
We often find ourselves trying to fit too much data into one chart. I’m guessing this has to do with putting charts on slides in PowerPoint, where it’s desirable to have all the data on one slide.
I am a supporter of creating separate charts to display different data. This makes it much easier to see the trend, understand and draw appropriate conclusions from the available data.
In the picture above I displayed the difference on a separate chart and placed it under the original chart. In this way, the user can clearly see the trend in the difference values – from July to February, the difference decreases every month, while the sales volume increases.
The charts below show another way to display the difference − on the chart panel. A chart panel is just a bunch of charts grouped together.
In the panel above, I’ve added a chart (bottom right) that shows the monthly growth for the series Present. Agree, it is very convenient to see these values in numbers.
How do you do it?
I would very much like to hear your opinion on this topic. How would you show the difference between line charts?
There are many ways to solve this problem, and it would be interesting to know each of them. Tell us about your solution in the comments below the article.
Thank you!