How to select best Excel Charts for your Data Analysis & Reporting

 

Do you always struggle with the type of Excel charts to use in your analytics reports?

The type of excel chart you select for your analysis and reporting depends upon what you are going to analyse and report.

We create charts to display qualitative and quantitative data in a graphical format and to make it easy to understand either one, any or all of the following:

  1. Type of data (Qualitative and Quantitative)
  2. Relationship among data
  3. Comparison of data
  4. Composition of data
  5. Distribution of data
  6. Overlapping of data

 

Type of data (Qualitative and Quantitative)

Qualitative data is the data that can be classified/categorized but it can’t be measured.  For example: colours, satisfaction, rankings etc.

Quantitative data (also known as interval/ratio data) is the data that can be measured. For example 5 customers, 12 steps, conversion rate , height etc

 

Types of qualitative data – Nominal data and ordinal data

Nominal data – qualitative data that can’t be put into a meaningful order (i.e. ranked). For example: {Blue, Yellow, Green, Red, Black}

Ordinal data – qualitative data that can be put into a meaningful order (i.e. ranked). For example: {Very Satisfied, Satisfied, Unsatisfied, very unsatisfied} or {Strong dislike, dislike, neutral, like, strong like}

 

Types of quantitative data – discrete data and Continuous Data

Discrete Data – quantitative data with distinct values / observations. For example: 5 customers, 17 points, 12 steps etc.

Continuous Data – quantitative data with any value / observation within a finite or infinite interval. For example: conversion rate, visits, pageviews, bounce rate, height, weight etc.

 

How you can summarize Qualitative data?

Through frequency table:

frequency-table

 

 

 

 

Through Pie Chart:

pie-chart

 

 

 

 

 

 

 

 

Through Column chart

clustured-column-chart

 

 

 

 

 

 

 

 

How you can’t summarize Qualitative data?

You can’t summarize qualitative data by calculating mean (or average).

For example;

  1. Very Satisfied: 40%
  2. Satisfied: 30%
  3. Unsatisfied: 20%
  4. very unsatisfied: 10%

If you calculate the average here than average satisfaction is 25% which doesn’t make any sense.

Look at this calculation this way, when you are calculating the average of a qualitative data, you are actually trying to compute this:

Average satisfaction = (Very Satisfied + Satisfied + Unsatisfied + Very Unsatisfied)/4

Similarly, you can’t summarize qualitative data through line charts and histogram.

 

How you can summarize Quantitative data?

You can summarize quantitative data through mean, median, mode, standard deviation etc. You can summarize quantitative data through column chart, bar chart, line chart, Histogram etc.

 

Relationship among data

To understand relationship, you need to understand connection/correlation between two or more data points.

  1. A data point/category is a mark on a graph/chart which corresponds to a piece of data.
  2. The data that the mark represents is also called a data point.
  3. The value associated with a data point is called data value.
  4. A variable can store one or more data values which we can represent graphically via a chart.
  5. A set of related data points is known as data series.

For example following chart has got 15 data points and two data series:

scatter-chart

Here we are trying to understand the relationship between two variables named ‘average order value’ and ‘sales’.

One of the best excel chart to represent this relationship is Scatter Chart.

We can see from the chart that the relationship between the two variables is linear which means as the value of AOV increases there is a corresponding increase in the value of sales.

Related Post: Introducing Predictive Marketing – The next stage of Business Optimization

 

Consider using Scatter Chart when:

1. Analysing and reporting relationship/correlation between two variables.

2. When you want to show ‘why’. For example: why revenue is correlated with average order value or why conversion rate is correlated with number of transactions.

3. When there are more than 10 data points on the horizontal axis. More the data points the better it is for a scatter chart. Conversely few data points (like 5 or 6 data points) are not good enough for creating a scatter chart.

4. There are two variables that depend on each other.

 

Comparison of Data

For comparing two or more variables the best charts are column charts, bar charts, line chart and combination chart.

Column Charts

Column charts are one of the most widely used charts and are a greatly used to compare variables.

In column charts categories are plotted along the horizontal axis and values are plotted along the vertical axis.

For example following column chart compares the performance of number of branded and non-branded keywords which generated traffic on the website between July and November.

column-chart1

Here the categories (number of branded keywords, number of non-branded keywords) are plotted on horizontal axis and their values are plotted on the vertical axis.

Following are the examples of some columns charts that you should avoid creating:

column-chart2

column-chart3

Consider using Column charts when:

1. You are comparing two or more variables which have same unit of measurement and are of comparable sizes. So that the values of one variable does not dwarf the values of the other variables.

2. When you want to show ‘how much’. For example how much organic visits have changed over three months. So column charts can be used to show data changes over a period of time.

3. The number of categories to plot is less than 5

4. You want to show maximum and minimum values.

 

Bar Charts

Bar charts are similar to column charts except that in the bar charts values are plotted horizontally and categories are plotted vertically.

For example following bar chart shows the social media performance of various insurance websites:

bar-chartAs you can see here the categories (website names) are plotted on the vertical axis and values are plotted on the horizontal axis.

Consider using Bar charts when:

1. The axis labels are too long to fit in a column chart

2. The number of categories to plot is between 5 and 8.

3. You are comparing two or more variables which have same unit of measurement and are of comparable sizes.

4. When you want to show ‘how much’.

5. You want to show maximum and minimum values.

 

Line Charts

Line charts are best to show data trends esp. over a long period of time. For example following line chart shows the performance of organic and PPC traffic between July and March.

line-chart1Another Example:

line-chart2

Here I am visualizing the link growth/velocity of BBC and CNN website in the last one year through Majestic Back Links History Tool. I have used the line chart because they are too many data points to plot and because I want to show data trend over a long period of time.

Another example:

line-chart3

Line charts are best to show data trends: uptrend, downtrend, short term trend, sideways trend and long term trend.

Consider using Line charts when:

1. You want to show data trends over a long period of time.

2. The number of categories to plot is more than 8.

3. You have too many data points to plot and the column or bar chart clutters the data.

4. When you want to show ‘how much’ has changed over a period of time.

 

Combination Charts

A combination chart is a combination of two or more charts.

For example combination of column chart with line chart.

Consider using combination chart when you are comparing two or more variables that have different unit of measurement and/or are of different sizes. For example:

combination-charts

Here both of the combination charts are presenting the same data.

The only difference is that,in the first chart I combined the column chart with a line chart. In the second chart I just plotted the ‘bounce rate’ on ‘secondary axes’.

I use combination charts a lot in my report and you must know how to create them as they are very useful.

Following is a short video on creating a combination chart in excel:

Note: By using combination chart you can ensure that the value of one variable does not dwarf the value of other variable(s).

 

Composition of Data

If you want to show the breakdown of data into its constituents then consider using Pie Chart, Stacked Column Chart or Stacked Area Chart.

 

Pie Chart

Pie charts are most useful when you have only one data series, less than 5 categories/data points to plot and you want to show composition of data.

For example following pie chart shows the breakdown of my website traffic sources in the last one month:

pie-chart1

Here I have got only 4 categories (search traffic, referral traffic, direct traffic and campaigns) to plot. So pie chart is ideal to show the breakdown.

If there were more than 4 categories to plot like 8 or 10 categories then pie chart becomes cluttered and hard to read.

Another example:

pie-chart2

Here we have got only two categories (new visitors, returning visitors) to plot. So the use of Pie chart is ideal here to show data composition.

Following are some pie charts that you should avoid creating:

pie-chart3

pie-chart4

 Consider using Pie charts when:

1. You want to show the breakdown of data into its constituents.

2. You have only one data series.

3. You have less than 5 data points to plot.

4. The data points represent the parts of the whole pie.

5. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

 

Stacked Column Chart

Stacked Column charts are most useful when you have 5 to 8 categories/data points to plot and you want to show composition of data.

For example following stacked column chart shows the breakdown of website traffic in terms of new and returning visits in the last one month:

stacked-column-chart

Consider using Stacked Column charts when:

  1. You want to show the breakdown of data into its constituents.
  2. You have between 5 to 8 data points to plot.
  3. The data points represent the parts of the whole composition.
  4. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

 

Stacked Area Charts

Stacked Area charts are most useful when you have more than 8 categories/data points to plot and you want to show the trend of composition.

For example following stacked area chart shows the breakdown of website traffic:

stacked-area-chart

 

Consider using Stacked Area charts when:

  1. You want to show the trend of composition.
  2. You want to emphasize the magnitude of change over time.
  3. You have more than 8 data points to plot.
  4. The data points represent the parts of the whole composition.
  5. The constituents are of comparable sizes. So that value of one constituent does not dwarf the values of other constituents.

 

Distribution of Data

If you want to show distribution of data then consider using column chart, bar chart, scatter chart or Histogram.

We need to determine the distribution of data points (i.e. how narrow or wide spread the distribution is) in order to trust the ‘average’ metrics.

If the distribution is wide spread then the average value is not a true representative of the typical value in a data set and hence we can’t trust the average metrics.

I have explained the impact of data distribution on average metrics in great detail in the post: How to Analyze and Report above AVERAGE

One of the best charts to show distribution of data is Histogram:

histogram

Note: You need to install ‘Analysis ToolPack’ in order to create Histogram in Excel. You can find more details about installing and using analysis toolpack from this post: Introducing Predictive Marketing – The next stage of Business Optimization

 

Overlapping of Data

If you want to show ‘overlapping of data’ then consider using Venn diagrams.

The multi-channel conversion visualizer chart used in Google Analytics to visualize multi-channel attribution is actually a Venn diagram:

venn-diagram1

We can use Venn diagram to determine whether or not a website has got attribution problem.

If there is little to no overlap between two or more marketing channels then the website doesn’t have attribution issues.

If there is a good amount of overlap then the website has got attribution issues and you should seriously consider taking multi-channel attribution into account while analyzing and interpreting the performance of marketing campaigns.

Another great use of Venn diagrams is in visualizing the back links overlaps between websites:

venn-diagram2

The tool that I have used to create this Venn diagram is known as Venny.

You can learn more about visualizing the back links overlaps between websites through this post: Ultimate Data Visualization Guide for SEO

Note: You can create a Venn diagram in Excel. Check out this tutorial on Microsoft Office website: Create a Venn diagram

 

Add context to your Chart

Different people analyze and interpret same chart differently. It all depends upon the context in which they analyze and interpret the chart.

No matter what chart you select, some people will always find a way to misinterpret your chart.

Therefore it is critical that you provide context with your chart in the form of written commentary and describe exactly the intent of your chart.

First present the context, then the insight and then the chart to support your insight.

In this way you are giving clues to your chart reader regarding how to read your chart.

For example:

chart-context

You can get more data reporting tips from this post: How to become Champion in Data Reporting

 

Charts to Avoid for Reporting Purpose

Throughout this post I have talked about the charts that should be used. But there are some charts which should be avoided at all cost and are worth mentioning to you:

charts-to-avoid

The reason you should be avoiding reporting data via these charts to your clients is simple. Majority of people have no idea what you are trying to communicate via these charts.

  • The speedometer chart looks so unprofessional, yet many marketers use them in reports.
  • The tree map looks like an aerial view of an open field with marked territories. This is the opinion of one of my clients.
  • Waterfall chart looks like there has been some misprinting of the column chart. This is yet another feedback from one of my clients.
  • Nobody understands Radar chart or Bubble Chart.
  • I have yet to find any good use of Bubble chart (aka Motion Charts) in Google Analytics.

 

Use these charts only when your target audience is as data savvy as you.

Other Posts you may find useful:

 

 

Himanshu Sharma About the Author: is the founder of seotakeaways.com which provides SEO Consulting, PPC Management and Analytics Consulting services to medium and large size businesses. He holds a bachelors degree in ‘Internet Science’, is a member of 'Digital Analytics Association', a Google Analytics Certified Individual and a Certified Web Analyst. He is also the founder of EventEducation.com and EventPlanningForum.net.

My business thrives on referrals, so I really appreciate recommendations to people who would benefit from my help. Please feel free to endorse/forward my LinkedIn Profile to your clients, colleagues, friends and others you feel would benefit from SEO, PPC or Web Analytics.

 

 

  • http://www.ewebplace.com/ Online Shopping India

    Some of the content wise details are the best and especially
    middle part is the best for me and my knowledge.

  • xxljuan1630

    Don’t Allow your Past Dictate cheap jordan shoes your Future

    You are not able to change things which are within the last but to positively change the part they play in this particular future. In somehow, past experiences does help to shape the people we are forever today. Past boat charters have influenced the situations in with lots of ways. Yesteryear has brought us the scvngr integrates where our everyday life, it expectations dictated what kind of work we are great for do and could be factor regarding aspects, small and large, of our lives our well being today.

    To stop your purged from defining your next, you for teenagers put these items firmly that will belongs up to behind in order to, in the final, cheap jordan 4 shoes sale a memory with no power to persuade your soon to be. Don’t go ahead and take mistake of using your past beeing the reason or or excuse to get a failure to gain your expectations. The past others think led to finally your location now, but it cannot be able to and carry you back unless you let associated with them.

    There are those who have experienced disaster all over again. These contests have precisely influenced all of their present circumstances even though the extent due to effect the final will used the future open for each mankind’s control. The current traumatic, tragic plus mildly unfortunate originating in life does not automatically means that is how the rest of your life continually to lived.

    Included in this are, a person’s history may possibly reveal he or was raised by intoxicating parents which may have neglected very own education. He knows for this reason he created no academics qualifications and we have a enterprise doing reduced paid manual work. This is this factual information but, if this guy goes on to state the goes through of very own past function as reason he’ll almost certainly never shop for much in adult life, he is utilizing the past as the excuse. He employed depriving himself of the future he are known cheap jordan shoes to have if he’d forget the published. From a job of blaming past for your overall condition, you could possibly slip on the inside laziness or perhaps a an mindset of cringing self-pity. You will hear any individual blaming teens deprivations for which they have nada hope associated with an achieving the item noteworthy in our life. These people occupy an viewpoint of vulnerability and unhappiness when, to be honest, they have because many chance as everybody else of being successful if they will neglect the over and above and and can fill these with negativity.

    Evidence that will the past need not dictate the forthcoming can be purchased in the life stories of all great achievers. Think of the same wealthy business people, leading sports stars, great scientists, movie symbols, super models and entrepreneurs in every one of walks of life. Did they all achieve chance, fame and wealth for the reason that came in the privileged over? Did each has caring parents, a proper education along with the limitless financial resources? If you study life stories you will find the answer isn’t an. Many of these successful the deals started life within a ordinary or not underprivileged style but they could achieve success because they freed themselves worth mentioning constraints imposed as well as the past.

    You could possibly blame your origins to get a failures but it’s just as fast to turn away from your past and create a successful tomorrow’s. You needn’t change most things apart out of outlook. With an thinking in a different way, you is shown to steer future in direction of you favor go. By refusing to think negatively tweaking filling yourself any positive nature, you is capable of doing any goal you set for your family members.

  • Ruby

    Glad that am the first person to post comment for this post. I have been breaking my head every month to prepare a traffic report for my clients.. My question is can these chart can be used as a template to compare results annually, coz once i send the report i don’t use them for further reference. Any optimistic idea to use them just like google analytics. Thanks for the post anyway.

    • seohimanshu

      Once you have created a report template you can use it again. Then all you have to do is change the data. This is what i do. I just add new data and all the charts are updated automatically.

  • Brine Technologies LLP

    Thats an Amazing Example.
    Thank You Mr. Sharma

    • seohimanshu

      Glad you like it.

  • Fahad

    Nice read… In the examples to avoid, you have a chart with many countries and their performance by month. How would you represent such data.

    • seohimanshu

      you can use line chart

  • http://www.swpearls.com/ Spartan-er

    Very great learning, all of information is in details!

  • Ganesh Pandar

    Very good information, thanks for sharing.

    http://www.gikoberry.com

  • kamal

    Hi Himanshu,

    Is there any website or free tool which can make graphs and interpret the key finding i.e.insights from the graph. My problem is that i m working on a report which has more than 200 graphs and write insights for each graph is taking so much time that all my time is getting waste and i have to deliver the report with in next 2 week.

    Please suggest some idea by which u can quickly finish with analyzing these graphs.

    • seohimanshu

      I wish there was such type of tool. My solution is, use less graphs, maximum 10. No one is going to read 200 graphs.

  • BBIL

    Great post! Really useful and informative! I had a quick question, is there any way of combining graphs that have different data sets? For instance, any way of comparing different parameters like (1) criticality or (2) closure status, for the number of manufacturing deviations for the month of September? Or would I just have to use separate graphs for that? Thanks in advance!

    • seohimanshu

      Combine the two different data sets into a single table and then you can use a single graph.

  • Faisal

    Dear Sharma, Surely it was a great informative post and i really like your critics on the charts

    you said above about column chart and using too many categories will make it difficult to read, my question is, then how to display such situation in a chart where we have to many categories? please email me your response at faisalkhanzada@hotmail.com

    • seotakeaways

      In case of too many categories, you need to create different charts for different categories.

  • Abhishek

    Hey, I joined this EXCEL CHAMPIONSHIP http://www.dezyre.com/competition/excel-championship#.UuN2whC6bIU

    Apparently the registration is for free for this competition and the prize money is $500 !!

    I am pretty excited so just wanted to share with you guys.

    • seotakeaways

      Thanks for the share.

  • Eralda

    I want to create a chart that shows achievements vs targets. Which type to use?

    • seotakeaways

      Hi! Sorry for the late reply. I am not sure about the data type of your two metrics. So i can’t suggest any particular chart.

  • ronmartin05

    Probably the biggest mistake people make when doing their own PPC advertising is choosing the wrong keywords just because they want to get traffic. A couple of things to know are 1) General keywords get lots of searches and traffic but are less qualified and less likely to buy. When you’re paying for visitors, you want results, not just traffic. 2) The keywords that are being bid on must be extremely relevant to the product/services you are offering. If it was a retail store, would you want to pay for male motor bikers to walk into Victoria’s secret? Nothing against bikers, it’s just not the target market – and that’s what happens when you bid on general, broad keywords. You get lots of untargeted visitors so your ROI doesn’t work. If anybody wants help with this, call my buddy Simon here: 256-398-3835.

  • Dahiapp

    Hope someone can help me ASAP, I need to do a chart where I compare 5 companies A-D for 3 years 17-19 and I am comparing Price and Quality
    HELP!!

  • sandra wieland

    I like to create charts. It underlines my presentations.
    But it took times until I could do it professionally. Mostly I got the
    help of this site http://www.excel-aid.com/excel-create-chart-layout-and-style-templates.html