Which Chart Type Works Best for Summarizing Time-based Data in Excel

Following are the best chart types for summarizing time-based data in Excel:

  1. Line chart
  2. Clustered column chart
  3. Combination chart
  4. Stacked column chart
  5. Stacked area chart

 

Line Chart

Use line charts when you want to show/focus on data trends (uptrend, downtrend, short term trend, sideways trend, long term) especially long term trends (i.e. changes over several months or years) between the values of the data series:

Use line charts when you have too many data points to plot and the use of column or bar chart clutters the chart.

Use a line chart instead of a clustered column chart if the order of categories is important:

 

Best practices for designing line charts

#1 Start the ‘Y’axis value at zero

When you do not start the ‘Y’ axis value of a chart at zero, the chart does not accurately reflect the trend:

For example, the following line chart amplifies the growth of Facebook fans because the ‘y’ axis value starts at 2500 instead of 0:

 Following is the correct line chart:

 

 

#2 Do not use line chart (to create trends) if you have less than eight data points

When you create a line chart with a few data points, the trend that you see can be very misleading.

For example, the following line chart just contain two data points and as a result, it makes the growth look phenomenal:

 For a line chart, the more data points the better.

#3 Do not hide the scale on the ‘y’ axis of a line chart

When you hide the scale of the ‘y’ axis, your chart won’t accurately reflect the trend. Without any scale on the y-axis, there is no way of knowing where the y-axis starts. When you use such charts it creates doubt on your analysis.

#4 Add context to your chart

Different people analyze and interpret the 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:

Clustered column chart

Use a clustered column chart when you want to compare two to four data series. In other words, avoid using column charts if you have just one data series to plot:

 Alternatively, avoid creating a column chart that has got more than four data series. For example, the following chart contains just five data series and it has already started looking cluttered:

The chart below contains 11 data series and is very difficult to read and understand:

 If you want to create a column chart which contains a lot of data series then you can try switching ‘row’ and ‘column’ of the chart and see whether it makes any difference:

For example, after switching the row and column of the chart (with 11 data series), it looks like the one below:

 Now this chart, though still look cluttered, is much easier to read and understand.

Use a clustered column chart when the data series you want to compare have the same unit of measurement. So avoid using column charts that compare data series with different units of measurement. 

For example in the chart below ‘Sales’ and ‘ROI’ have different units of measurement. The data series ‘Sales’ is of type number. Whereas the data series ‘ROI’ is of type percentage:

Use a clustered column chart when the data series you want to compare are of comparable sizes. So if the values of one data series dwarf the values of the other data series then do not use the column chart.

For example in the chart below the values of the data series ‘Website Traffic’ completely dwarf the values of the data series named ‘Transactions’:

Use a clustered column chart when you want to show the maximum and minimum values of each data series you want to compare. 

Use a clustered column chart when you want to focus on short term trends (i.e. changes over days or weeks) and/or the order of categories is not important.

 

Breaking a clustered column chart

The chart below contains 11 data series and is very difficult to read and understand:

One method of making this chart easier to read and understand is by breaking it into several smaller clustered column charts.

For example, you can create one column chart which just compares the sales performance of various countries in January. Create another column chart which just compares the sales performance of various countries in Feb and so on:

The rule of thumb is to avoid presenting too much data in one chart, regardless of the chart type you use.

 

Best practices for designing column charts

#1 Start the ‘Y’axis value at zero

When you do not start the ‘Y’ axis value of a chart at zero, the chart does not accurately reflect the size of the variables. For example, the following column chart amplifies changes because the ‘y’ axis value starts at 440 instead of 0:

Following is the correct column chart:

#2 Do not hide the scale on the ‘y’ axis of a column chart

When you hide the scale of the ‘y’ axis, your chart won’t accurately reflect the size of the variables. Without any scale on the y-axis, there is no way of knowing where the y-axis starts. When you use such charts it creates doubt on your analysis.

#3 Use a bar chart whenever the axis labels are too long to fit in a column chart:

Combination chart

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

For example the combination of a column chart with a line chart. I use combination charts a lot and I think you must know how to create them as they are very useful.

Use a combination chart when you want to compare two or more data series that have different units of measurement:  

Use a combination chart when you want to compare two or more data series that are not of comparable sizes:

Stacked column chart

Use a stacked column chart when you want to compare data series along with their composition and the overall size of each data series is important:

Use a 100% stacked column chart when you want to compare data series along with their composition but the overall size of each data series is not important:

Stacked Area Chart

Use a stacked area chart when you want to show the trend of composition and emphasize the magnitude of change over time. For example, the following stacked area chart shows the breakdown of website traffic:

To learn more about different types of charts, check out the article: Best Excel Charts Types for Data Analysis, Presentation and Reporting

 

 

Do you know the difference between Web Analytics and Google Analytics?


99.99% of course creators themselves don’t know the difference between Web analytics, Google Analytics (GA) and Google Tag Manager (GTM).

So they are teaching GA and GTM in the name of teaching Web analytics.

They just copy each other. Monkey see, monkey do.

But Web analytics is not about GA, GTM.

It is about analyzing and interpreting data, setting up goals, strategies and KPIs.

It’s about creating strategic roadmap for your business.


Web Analytics is the core skill. Google Analytics is just a tool used to implement ‘Web Analytics’.

You can also implement ‘Web analytics’ via other tools like ‘adobe analytics’, ‘kissmetrics’ etc.

Using Google Analytics without the good understanding of ‘Web analytics’ is like driving around in a car, in a big city without understanding the traffic rules and road signs.

You are either likely to end up somewhere other than your destination or you get involved in an accident.


You learn data analysis and interpretation from Web analytics and not from Google Analytics.

The direction in which your analysis will move, will determine the direction in which your marketing campaigns and eventually your company will move to get the highest possible return on investment.

You get that direction from ‘Web analytics’ and not from ‘Google Analytics’.


You learn to set up KPIs, strategies and measurement framework for your business from ‘Web analytics’ and not from ‘Google Analytics’.

So if you are taking a course only on 'Google Analytics’, you are learning to use one of the tools of ‘Web analytics’. You are not learning the ‘Web analytics’ itself.

Since any person can learn to use Google Analytics in couple of weeks, you do no get any competitive advantage in the marketplace just by knowing GA.

You need to know lot more than GA in order to work in Web analytics and marketing field.


So what I have done, if you are interested, is I have put together a completely free training that will teach you exactly how I have been able to leverage web/digital analytics to generate floods of news sales and customers and how you can literally copy what I have done to get similar results.

Here what You'll Learn On This FREE Web Class!


1) The number 1 reason why most marketers and business owners are not able to scale their advertising and maximise sales.

2) Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

3) The number 1 reason why conversion optimization is not working for your business.

4) How to advertise on any marketing platform for FREE with an unlimited budget.

5) How to learn and master web/digital analytics and conversion optimization in record time.

 
 

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and Beyond
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

Himanshu Sharma

Digital Marketing Consultant and Founder of Optimizesmart.com

Himanshu helps business owners and marketing professionals in generating more sales and ROI by fixing their website tracking issues, helping them understand their true customers purchase journey and helping them determine the most effective marketing channels for investment.

He has over 12 years experience in digital analytics and digital marketing.

He was nominated for the Digital Analytics Association's Awards for Excellence.

The Digital Analytics Association is a world renowned not-for-profit association which helps organisations overcome the challenges of data acquisition and application.

He is the author of four best-selling books on analytics and conversion optimization:

error: Alert: Content is protected !!