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.
Watch the Video
Download the Excel File
Enter your email address below to download the sample workbook.
Charting Disparate Data
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.
The Truth Chart 2 – Secondary Axis
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 compromise would be to add some labels with the start and end dollar values of the revenue and profit and percentage growth.
The Truth Chart 3 – Panel Charts
Panel charts, which in their basic form are simply two separate charts, enables you to clearly see the growth in each series and the separate axis scales enable quicker interpretation than the secondary axis charts. However, there is still a large gap between the lines which makes it difficult to compare them. This could be aided by adding labels for percentage growth.
The Truth Chart 4 – Index Numbers
The chart above plots the change in revenue and profit figures over time relative to the starting position in 2011. 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 2022 they were almost the same.
The labels on the lines in this chart could be used in any of the charts above to aid interpretation. Download the file to see how they're done.
Calculating the Index
We index numbers by saying that in 2011 (our base period) both revenue and profit were 100, or 100% and from there we calculate the change for each year since 2011.
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 D5 we can see that the indexed revenue is 114, which is the same as saying that the revenue in 2012 is 14% higher than it was in 2011. Likewise the profit in 2012 is 7% less than it was in 2011.
Each subsequent year is also compared to 2011, our base year, to come up with the indexed value for that year.
The formula in D4 is =B4/$B$4 which is then copied down the column so that D5 contains =B5/$B$4 and so on.