Which Chart Type Works Best for Summarizing Time-Based Data in Excel? (2024)

Last Updated: September 5, 2023

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; using a column or bar chart will clutter 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 a 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 you see can be very misleading.

For example, the following line chart contains 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 in 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. This way, you give clues to your chart reader regarding how to read your chart. For example:

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, a weather report, an increase in sales, etc.
• Line graphs should be used to identify spikes in 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 you plot 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 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 with 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:

This chart, though it still looks 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, 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) 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 compares the sales performance of various countries in January. Create another column chart which 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:

See Also

Best MACD Strategy for 15-Minute Charts - Article

#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 in your analysis.

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

How to create a clustered column chart:

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

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

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

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

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:

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

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.

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

Clustered column charts and stacked column charts 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 with their composition, and the overall size of each data series is important. Stacked column charts allow a part to whole comparison over time.

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 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, ensure 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, consider using other chart types that are easily interpreted.

5. Stacked area chart

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

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

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

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

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

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

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

Other Articles on Excel Charts

• Five Advanced Excel Charts and Graphs
• Data Visualization in Excel Tutorial
• Best Excel Charts Types for Data Analysis, Presentation and Reporting
• What type of chart to use to compare data in Excel

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