Charts are great at revealing trends, patterns and relationships in your data. We love how they enable us to glean information in the blink of an eye, but beware of the line chart because it can lie.
Let’s take the line chart below that plots the trend of revenue and profit over time. It looks like revenue is growing faster than profit; after all revenue shows a steady incline whereas profit is quite flat.
Present a chart like this and you’ll be inundated with questions from the CEO who is irate that either COGS or overheads, or both have increased, and all the extra revenue they’ve worked so hard to generate appears to be eaten up by increased costs.
To be fair, it’s not the line chart’s fault. The first line chart is plotting the data correctly. It isn’t a bug in the chart. It’s a case of relativity; a 10% increase on $1m is always going to be a bigger number than a 10% increase on $100k, and when you plot disparate data that continues to grow over time, even at the same rate, the gap is going to widen.
The Truth Chart 1 – Log Scale
However, if we look at the data plotted on a log scale we can see that revenue and profit are actually growing at similar rates.
Phew, the production and operations managers have been spared, but now the CEO is after you for producing a misleading chart!
Using a Log scale is one option to prevent line chart lies, but some people find these a bit confusing to interpret so let’s look at another Truth Chart that will also spare your life and job.
The Truth Chart 2 – Index Numbers
The chart above plots the change in revenue and profit figures over time relative to the starting position in 2001. These values are known as index numbers (more on that in a moment).
It tells a completely different story to the original line chart. Here we can clearly see that the growth rate of revenue and profit have followed a fairly similar pattern, and by 2013 they were almost the same.
Calculating the Index
We index numbers by saying that in 2001 (our base period) both revenue and profit were 100, or 100% and from there we calculate the change for each year since 2001.
It’s tricky to explain in words alone so let’s take a look at this table which shows the indexed results in columns D and E (remember 1 = 100%):
In cell D3 we can see that the indexed revenue is 1.14, which is the same as saying that the revenue in 2002 is 14% higher than it was in 2001. Likewise the profit in 2002 is 7% less than it was in 2001.
Each subsequent year is also compared to 2001, our base year, to come up with the indexed value for that year.
The formula in D2 is =B2/$B$2 which is then copied down the column so that D3 contains =B3/$B$2 and so on.
Please Share
If you liked this, or know someone who might inadvertently be lying with line charts ;-), click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Jef says
Well written analysis. Thanks for sharing.
Mynda Treacy says
Thanks, Jef 🙂
Jon Peltier says
It’s not just line charts; any chart can be used in misleading ways. (Intentionally or not.)
In some cases it is useful to plot the ratio of the two numbers, for example, profit as a percentage of revenue.
Mynda Treacy says
Yep, and yep. Cheers, Jon. I agree. In fact profit as a percentage of revenue would be the most sensible data to plot in this scenario.
I was originally planning on using a different example for this post as I stumbled upon this issue where different figures were used; it was something like the growth of US Debt vs Imports from China, but for the life of me I couldn’t find it again.
Mynda.
Geraldine Tatters says
Really like the index charts; real alternative to changing series from/to primary and secondary axis
I will use this in my day-to-day reporting
Mynda Treacy says
Great 🙂 Glad you liked it Geraldine. I definitely recommend indexing over the secondary axis option as you can see in my examples in reply to Asif.
Mynda.
Sanjiv Daman says
Hi Mynda,
This is awesome. Now we can use excel to add real value instead of just using charts for the sake of showing numbers.
Mynda Treacy says
Cheers, Sanjiv 🙂
Asif says
Thanks Mynda!
By Index approach, one can see the trend of both the revenue & the profit in above chart…right but a secondary axis option can be adopted to see the real values.
Now what if we have three variables plotted on Y axis with different variation in values…here index will solve our problem if we want trend but how we can get 3rd axis on a graph?
Mynda Treacy says
Hi Asif,
The secondary axis is a popular choice, however among data visualisation gurus they are discouraged because they are difficult to quickly see which axis is for which line.
The other problem they pose is since they use different scales they can result in an unclear message. Let’s look at what happens when you add a secondary axis:
In this chart the lines for revenue and profit do follow a similar upward trend but we can’t compare the actual growth rate of one against the other.
The uneducated chart reader may even make the incorrect assumption that profit is performing better than revenue since the profit line ends at a higher point, irrespective of the scales.
We humans make split second assumptions based on what we see, often without even realising. We need to be mindful of this and try to present data in a way that is quick to interpret, but also consider any misinformation they might inadvertently convey.
Perhaps a comprimise would be to add some labels with the start and end dollar values of the revenue and profit, although this may then confuse interpretation too:
Cheers,
Mynda.
roberto mensa says
I think that a clear division of the panels can solve the problem of the secondary axis … something like this:
http://peltiertech.com/WordPress/excel-column-chart-primary-secondary-axes/
(only the first hint that I found)
Mynda Treacy says
Thanks for sharing the link, Roberto. I also like a panel chart and the vertical ones are ideal for this scenario.
If you didn’t want to jump through all those hoops to create it you could always create two separate charts and align them, hiding one of the horizontal axes etc. Not as slick as Jon’s panel chart but maybe good for quickly cobbling together.
Cheers,
Mynda.
Jon Acampora says
Great example of indexing! I like the chart above that shows the revenue in the labels. You could also add labels at the end of each line that state the percentage growth. For example, to the right of the orange line the label could say, “Revenue grew 42% since 2001”.
You could also accomplish this by formatting the numbers as percentages in the y-axis and adding an axis label that describes it. The reader might be confused by what 1.2 or 1.4 means.
Thanks for sharing!
Mynda Treacy says
Great idea, Jon.
Cheers,
Mynda.