• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Is Your Line Chart Lying?

You are here: Home / Excel Charts / Is Your Line Chart Lying?
Is Your Line Chart Lying?
May 6, 2014 by Mynda Treacy

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

Subscribe YouTube

Download the Excel File

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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.

Misleading line chart

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.

Log scale line chart

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:

secondary axis chart

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 chart

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

Indexed line chart

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%):

Index calculation

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.

Is Your Line Chart Lying?

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:Conditional Formatting PivotTablesConditional Formatting PivotTables
Next Post:Interactive Excel Web App Dashboardinteractive excel web app dashboard

Reader Interactions

Comments

  1. Jef

    May 7, 2014 at 5:48 pm

    Well written analysis. Thanks for sharing.

    Reply
    • Mynda Treacy

      May 7, 2014 at 7:59 pm

      Thanks, Jef 🙂

      Reply
  2. Jon Peltier

    May 6, 2014 at 8:26 pm

    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.

    Reply
    • Mynda Treacy

      May 6, 2014 at 8:40 pm

      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.

      Reply
      • Stefano B.

        October 29, 2019 at 5:49 am

        Awesome job Mynda and great suggestion Jon!! Chart adjusted to help reader better interpret the more valuable data (profit as a percentage of revenue). Thoughts?

        Reply
        • Mynda Treacy

          October 29, 2019 at 8:57 am

          Hi Stefano,

          Thanks for sharing your chart. I recommend showing the ratio increase in its own chart as bars or a line. The labels are helpful, but they’re slow to read and they clutter the chart a bit too much for my liking. How about this variation:

          line chart with ratio

          Mynda

          Reply
  3. Geraldine Tatters

    May 6, 2014 at 8:06 pm

    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

    Reply
    • Mynda Treacy

      May 6, 2014 at 8:31 pm

      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.

      Reply
  4. Sanjiv Daman

    May 6, 2014 at 5:50 pm

    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.

    Reply
    • Mynda Treacy

      May 6, 2014 at 8:11 pm

      Cheers, Sanjiv 🙂

      Reply
  5. Asif

    May 6, 2014 at 5:25 pm

    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?

    Reply
    • Mynda Treacy

      May 6, 2014 at 8:29 pm

      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:

      Line Chart Lie #5

      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:

      Line Chart Lie #6

      Cheers,

      Mynda.

      Reply
      • roberto mensa

        May 7, 2014 at 2:04 am

        I think that a clear division of the panels can solve the problem of the secondary axis … something like this:
        https://peltiertech.com/WordPress/excel-column-chart-primary-secondary-axes/
        (only the first hint that I found)

        Reply
        • Mynda Treacy

          May 7, 2014 at 8:44 am

          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.

          Reply
      • Jon Acampora

        May 7, 2014 at 2:05 am

        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!

        Reply
        • Mynda Treacy

          May 7, 2014 at 8:45 am

          Great idea, Jon.

          Cheers,

          Mynda.

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.