• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

4 Charts – Same Data. Which do you think is best?

You are here: Home / Excel Charts / 4 Charts – Same Data. Which do you think is best?
4 Charts – Same Data. Which do you think is best?
January 15, 2014 by Mynda Treacy

Often when you’re charting data it can be tricky to find the best chart for the job.

If you get it wrong at best it can be a waste of everyone’s time, and at worst it can be misleading.

That’s why you need to know the motivation for the chart up front. What is the question that the chart is supposed to answer?

Of course sometimes you will be analysing data to look for patterns and a story to tell. In either case, knowing which chart displays what type of information is important.

Here we’re going to look at 4 different ways to present the same data and the different questions each chart answers.

The Data

Below is some data which I found on The Science Goddess’s site, Excel for Educators.

chart data

The Science Goddess used this data to show you how you can improve on the default charts that Excel spits out. It’s worth a read.

Caveat: Since I am not familiar with this data I don’t know what the ‘question’ is that we need to answer. Instead I'm going to look at the different charting options we have available for this type of data, and explain what questions each chart allows you to answer. As a result some of the charts may analyse the data in a way that wouldn't apply in reality. Just so you know 😉

Chart 1 – Panel Chart or Small Multiples

small multiples charts

Panel Chart or Small Multiples Key Points

  1. Don’t repeat labels or axes. I have just one vertical axis on the first chart which serves all 5 charts.
  2. Likewise, if space is limited reduce axis labels where they can be implied, as I have done for my horizontal axes where I’ve only labelled 2004 and 2013 for each chart.
  3. Make sure all charts are the same size and colours are consistent. Note also that only 1 legend is required for all 5 charts.
  4. Fix all charts to the same minimum and maximum axis height and align them. It’s natural that people will compare one chart to the next so it’s imperative that each chart uses the same scale so you don’t mislead the reader or make it difficult to read.
  5. You can add data labels if you want, like I have done in the first chart. If you do this it’s a good idea to do it for every chart and then you can delete the vertical axis as it’s redundant.
  6. You can build these charts by creating 5 separate charts and then aligning them together, or you can create a ‘panel chart’ in Excel with some jiggery pokery.

Panel Charts or Small Multiples are good for

Allowing comparison of each category side by side (in this example, ethnicity and student/staff), enabling you to detect patterns in the data.

For example we can quickly see that Students outnumber staff in every ethnicity except for White. Asian, American Indian and Black have not changed significantly since 2004. Hispanic students have increased the most while White students and staff have both decreased.

This approach assumes there is a relationship between staff and student numbers, which may not be the case.

Sparklines

Sparklines are a new feature in Excel 2010. They can be used in a similar way to the Small Multiple charts although they take the ‘small’ idea further, in that they fit into a single cell.

sparklines

Sparkline Key Points

  1. They typically don’t show an axis.
  2. You can choose from line, column or win/loss Sparklines.
  3. You can use them to show trends in a series and highlight maximum and minimum values by applying markers to the Sparkline.
  4. They are applied to the background of the cell as opposed to being an object that floats above the worksheet like a regular chart.

Sparklines are good for

Enhancing a table of data, as patterns can be difficult to see in a mass of numbers. A Sparkline gives you a quick way to see a pattern thus giving the numbers context.

In the above example the reader is less able to make comparisons between the categories and so the message is focussed on the individual ethnic group’s pattern over time.

Line Chart

line chart

Line Chart Key Points

  1. Because there are quite a few lines in this chart it’s easier for the reader if you label each line as opposed to using a legend.
  2. This chart isn’t trying to do too much. It only plots Students. To include Staff would make it too cluttered.

Line Charts are good for

Seeing trends over time. The line implies there is a relationship from year to year.

Plotting each ethnic group in the one chart allows the reader to make comparisons between the groups. Who is biggest, second biggest etc. as opposed to making comparisons between student and teacher trends like we did in the first panel chart.

Bar Chart A

bar chart A

Bar Chart A - Key Points

  1. This bar chart removes the time category and instead focusses on two points in time (2004/05 vs 2012/13) and the overall change.
  2. By overlapping the two columns we’re able to make a quick visual comparison of then and now.
  3. The labels tell us the exact change with an upward facing triangle indicating a positive change and a downward facing triangle indicating a negative change.
  4. Sort the data to aid interpretation. For example we can easily see which ethnic group is biggest, second biggest etc.  Note: if your data already has an implied order then sort by the implied order e.g. Highly Developed, Developed, Developing… etc.
  5. By segregating Students and Staff we imply that there isn’t a relationship between the two groups.
  6. A gap between each bar emphasises that the categories are discreet. Unlike a line chart which is continuous to indicate there is a relationship from one category (typically time) to the next.

Bar Charts are good for

Comparing a large number of classes or groups.

They are also good for displaying data with long titles as they avoid the need to angle or wrap them like you do with a column chart.

Bar Chart B

bar chart B

Bar Chart B - Key Points

  1. This is a variation of bar chart A; however instead of two columns, 2004/05 is represented by a marker.
  2. This chart is cleaner/less cluttered and slightly easier to read. Unfortunately inserting the marker requires a bit more effort (and possibly some Red Bull) than Bar Chart A with the two overlapped columns.

Summary

I hope I’ve illustrated (pun intended 😉 ) that there isn't a 'best' chart out of the ones above. It comes down to choosing the chart that best conveys your message.

By no means is this a comprehensive list of different ways to display this data visually. If you have an example you’d like to share please email it to me via the Help Desk and I’ll add it to this post, or leave me a comment below.

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.

Want More?

If you’d like to learn more about data visualisation and dashboards please consider my popular Excel Dashboard course.

I’ll teach you the fundamentals for data visualisation and I’ll show you the tricks I used to create the charts above, plus a load more.

And to give you a headstart download my free 30 Tips for Better Charts eBook here.

Data Visualisation Inspiration and Help

If you already know how to create amazing charts in Excel, but you’re not sure how to best display your data then Helpmeviz.com is a great place to ask your question and receive feedback, suggestions and examples from members of the data visualisation community.

Or just take a look at some of the previous posts for inspiration.

4 Charts – Same Data. Which do you think is best?

More Charts Posts

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.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.
using jitter to avoid over plotting

Using Jitter to Avoid Over Plotting in Power BI

Plotting data that has one variable where values are similar, can result in points that are plotted over each other. Use jitter to avoid this overplotting.
Excel custom chart labels

Excel Custom Chart Labels

Create dynamic Excel custom chart labels with this category axis hijack trick
Sorting Excel Date Slicers

Sorting Excel Date Slicers

Slicers have some shortcomings when it comes to dates and sort order. This post explains a couple of ways to sort dates correctly in Excel slicers.
Charting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

Charting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

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 ChartsTag: charts
Previous Post:Mac Excel Keyboard Shortcuts CoverMac Excel Keyboard Shortcuts Cover
Next Post:Charting Disparate Data in Excel – 3 Solutions and 1 Crazy MessCharting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

Reader Interactions

Comments

  1. duncan

    January 20, 2014 at 12:36 pm

    Me again!

    Many thanks for your valuable insights and suggestions Mynda. I have updated my file and my blog entry and all of your comments are there too. I have changed a few things and I have left my original Excel file AND my revised file for everyone to see and download.

    I am impertinent I know but I suggested a FIFTH chart to consider: the Then v Now chart. Take a look! I think it helps with this data set very well.

    I have taken more time this time and have got rid of my shoddy formatting: thanks for the push.

    Best wishes

    Duncan

    Reply
  2. MF

    January 17, 2014 at 7:01 pm

    Hi Mynda,
    I like the idea of displaying the delta % in the bar chart. I do it a lot since I know how. 🙂
    btw, for your consideration. Instead of using IF and CONCATENATE the ▲▼, I prefer to set it by custom format:▲0.0%;▼0.0%
    We can color-code the figures in the way too.
    cheers,
    wmfexcel.wordpress.com

    Reply
    • Mynda Treacy

      January 17, 2014 at 7:36 pm

      Great tip, MF. 🙂

      I tend to forget about those custom formats. Thanks for the reminder.

      Reply
  3. duncan

    January 17, 2014 at 12:54 pm

    I will upload my file to my own Excel blog soon Mynda.

    You will see that I have created two conditional formatting charts: one based on the original table and another based on the transposed table.

    In the meantime I have worked on your panel charts as well. I like the idea so thought I would see what I might do with it. What I did was to create a helper table of rates of change: eg, the % change from 2004 to 2005 for each category, then the % change from 2005 – 2006 for each category and so on.

    I then copied your charts, keeping a standard vertical axis and changed absolute data to relative data: given the data you were working with, I think this chart is much more meaningful.

    My connection is very slow so please bear with me as I try to create a new page on my blog, linked to this one and fully acknowledged and put a link to my version of your file. In that file you will see your work in tact and then where I have created something different there is an, eg, Small Multiples_dw tab.

    Reply
    • Mynda Treacy

      January 17, 2014 at 12:58 pm

      Awesome. I’m looking foward to it, Duncan. 🙂

      Reply
  4. Sanjiv

    January 17, 2014 at 6:19 am

    The first one is very clever.
    The other chart I would create is a 100% stacked chart with the year on the x axis and the ethnicity in the various colors. For each year, it will give a visual view of the % breakdown by Ethnicity and a perspective of how it is changing over time.
    A very useful graph in context of South Africa where there is a large focus on getting the distribution by race normalised in the various organisations

    Reply
    • Mynda Treacy

      January 17, 2014 at 11:54 am

      Thanks, Sanjiv.

      I’m not a big fan of stacked bar charts. It makes it difficult to compare categories over time when they’re up and down year on year which makes the height each ethnic group starts at different, except the first group which is on the bottom.

      Here are some clever solutions to that problem:

      https://sites.google.com/site/e90e50fx/home/stacked-and-separated-dynamic-excel-charts

      Kind regards,

      Mynda.

      Reply
  5. Maxime Manuel

    January 16, 2014 at 8:36 am

    They are all great charts. The first one amazed me.
    You are geniuses. Thank you Mynda.

    Reply
    • Mynda Treacy

      January 16, 2014 at 9:15 am

      Aw, thanks Maxime 🙂 Glad you liked them.

      Reply
  6. duncan

    January 15, 2014 at 11:16 pm

    I like the small multiples, they remind me of slopelines or slope graphs.

    Sparklines are efficient

    I didn’t like the line and bar charts.

    Just today I was experimenting with conditional formatting for this kind of thing and it looked good: I was analysibg balance sheets over time but I made tge results almost chart like.

    Reply
    • Mynda Treacy

      January 16, 2014 at 7:36 am

      Hi Duncan,

      I’d like to see your Conditional Formatting ‘charts’.

      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

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

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.