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

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

Line Chart Type

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:

1 2

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

1 3

 Following is the correct line chart:

1 4

best excel charts

Get the E-Book (40 Pages)

 

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

1 5

 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:

1 6

When to use a line chart:

  • Line charts are the best when you want to map continuous data over a period of time. For example, a traffic increase over a period of time, weather report, an increase in sales, etc.
  • Line graphs should be used to identify spikes in the traffic. This will let you find the exact time when there was a sudden rise or fall in the traffic.
  • Line graphs can also be used to compare data on how two metrics are performing over a period of time. However, it is advised that if there are more than four line charts on a single graph it becomes cluttered and difficult to interpret.

Note: Always make sure that you are plotting data in equal intervals to make accurate representations. If data is not plotted in equal intervals, it becomes difficult to interpret the reports. It would be difficult to understand if there was a drop or rise in traffic for the interval not plotted on the graph.

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

1 7

 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:

1 8

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

1 9

 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:

1 10

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

1 11

 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:

1 12

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

1 13

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:

1 14

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:

1 15

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:

1 16

Following is the correct column chart:

1 17

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

1 18

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:

1 19

How to create a clustered column chart:

Step-1: Select the entire data set in the Excel sheet. Shown below is our data set.

select data

Step-2: Click on the ‘Insert’ tab from the Excel ribbon tab.

Insert Tab

Step-3: Click on ‘Recommended charts’ as shown below.

Recommended Charts

Step-4: From ‘All charts’, select ‘Column’ and select ‘Clustered column chart’ as shown below.

Clustered column chart

Step-5: Click ‘Save’.

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

1 20

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

1 21

Combination charts enable users to analyze large data sets with multiple chart types (bar and line) for a better understanding. They allow you to plot multiple data sets on the same chart.

Best practice for using a combination chart is when you want to visualize differences between different sets of data.

Clustered column chart and stacked column chart both display data in rectangular bars. However, in column charts, the data values will be displayed side by side, whereas in the stacked column chart data is stacked one over the other.

4. 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. Stacked column charts allow a part to whole comparison over time.

1 22

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:

1 23

Stacked column charts should be used for displaying the difference in numerical or percentage values. 

Best practice for designing stacked column charts:

Stacked column charts should be used when you have more than one data set to be represented in the bar graph.

Stacked column charts make comparison simple between values as everyone is familiar with column charts.

While using stacked column charts make sure that your dates have the same intervals.

Stacked column charts work better only for a few totals. If you have more than six to seven bars to be represented then consider using other chart types which are easy to be interpreted.

5. Stacked area chart

As the name suggests, a stacked area chart is a simple area chart that is stacked on top of one another.

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:

1 24

Stacked area charts should be used when you want to show changes in part to the whole relationship over time. In this chart type, data is stacked to show the contribution of each set to the total.

Stacked area charts work in the same way as simple area charts, except they are used for multiple data series.

Stacked area charts should be used when more than two to three variables are present. The number of variables in the stacked area chart should be kept moderate as too many variables would make it clustered.

Steps to create a stacked area chart:

Step-1: Select the entire data set in the Excel sheet. Shown below is our data set.

select data

Step-2: Click on the ‘Insert’ tab from the Excel ribbon tab.

Insert Tab

Step-3: Click on the ‘Recommended charts’ as shown below.

Recommended Charts

Step-4: From the left-hand side select ‘Area’ as shown below:

Area from charts

Step-5: Select ‘Stacked area’ and click on ‘Ok’.

stacked area

Other Articles on Excel Charts

Frequently Asked Questions About Which Chart Type Works Best for Summarizing Time-Based Data in Excel

What is a line graph and when should I use it?

Line graphs are used to track smaller or larger changes over a period of time. They can also be used to see how a certain metric is changing over a time. For example, temperature changes, speed and distance.

What is a clustered column chart and how does it illustrate?

A clustered column chart is simply a bar chart except that it displays clusters of data on the single graph. It displays more than one data in clustered vertical columns.

What is a combination chart?

As the name indicates it is a combination of one or more charts. For example, a column chart with a line graph.

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master 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 BeyondSECOND EDITION OUT NOW!
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.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!