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

 

This blog post is a featured request from Adam of Norway. This is what he asked:

Hi Himanshu,

I am Adam from Norway and am an avid reader of your blog. I have one question. I always struggle with the type of Excel charts to use in my analytics reports. Can you please share some tips.

Thanks

Adam

This question is brilliant and requires comprehensive explanation. So I thought of sharing the answer not only to him but to all of my blog readers. Before I move forward, I would like to thank Adam for letting me share his question on this blog.

Now back to the question. The short and bitter answer to Adam’s problem is, there is no one size fit all solution. The type of excel chart you select for your analysis and reporting depends upon what you are going to analyze and report.

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

1. Relationship among data
2. Comparison of data
3. Composition of data
4. Distribution of data
5. Overlapping of data

 

Relationship among data

To understand relationship, you need to understand connection/correlation between two or more data points. A data point/category is a mark on a graph/chart which corresponds to a piece of data. The data that the mark represents is also called a data point. The value associated with a data point is called data value. A variable can store one or more data values which we can represent graphically via a chart. A set of related data points is known as data series. For example following chart has got 15 data points and two data series:

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. Analyzing 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.

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 some columns charts that you should avoid creating:

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:

As 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.

Another Example:

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

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:

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:

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:

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:

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:

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:

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:

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:

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 tell exactly the intent of your chart.

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

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:

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.

 

If you like this post then you should subscribe to my blog and follow me on twitter

 

Other Posts you may find useful:

• Beginners Guide to Universal Analytics - Creating Custom Dimensions & Metrics
• Common Google Analytics Mistakes that kill your Analysis, Reporting and Conversions