Best graph to show change over time

When it comes to decision making, data is becoming indispensable for professionals. But translating data into action isn’t easy. First we have to make sense of the numbers, and for that infographics are where it’s at.

Oftentimes, what we’re seeking to explain in an infographic is how things have changed over time. What has been affecting the problems we and our customers have been facing, and how have our decisions thus far impacted us and them?

Did you know there are several options for how we can show changes in data over time? When we are deciding what to include in our infographic, in order to help ourselves and others use data to make better decisions going forward, we need to consider what we most want to emphasize.

Not a designer? You can use Venngage’s easy-to-edit templates and simple online Graph Maker to create beautiful data visualizations in minutes.

Click to jump ahead:

1. Showing changes in numbers

We can create infographics to help us communicate and understand lifetime sales trends, quarterly revenue changes, annual website traffic patterns, employee retention last year, and much more.

When we’re describing how numbers have changed over time, it is essential to use line graphs in our infographics. They’re the best graphs when you need to how changes like how seasonal cycles affect sales and revenue.

Line graphs to show change over time

Here are a couple examples. This one is super straightforward and hyper focused on the data at hand.

This one has more graphic elements, which might be helpful if the audience needs a bit more information and perhaps emotional appeals to develop an understanding of the data. If you are communicating numbers that seem to be difficult for others to take action on, this is especially important.

When you create line charts, keep in mind that you want to include enough data to paint a complete picture. If you are highlighting recent changes, be sure to put this data in adequate context by including as much data over time as you can to make it clear what the data is indicating. If need be, you can create two graphs, one showing a longer time frame and another showing a shorter one.

There is one exception to this rule, and that is if you want to focus on only two time intervals (two months, two years, etc.) to compare them to one another. For this, slope charts can be very effective. These charts are also great if you want to show how the ranking of various items has changed over time.

Here’s an example. Notice the years 2002-2019 have not been included, so you can see the change that happened over the past couple decades. Because this data shows a record low, it makes sense to frame the dramatic change in this way.

2. Illuminating the factors that drive change

Numbers can help us see that changes have occurred, but sometimes it takes investigation to determine exactly why. We may want to make infographics to help us solve customer service problems, for process improvement efforts, or to reflect on organizational changes.

For these types of infographics, consider using a fishbone diagram to support others in understanding causes and effects. You will need to decide what level of detail your audience needs as you outline what to include.

Here are some examples. This first one is very basic, helping the audience get conceptually what is contributing to an outcome.

This next one has a bit more detail so the audience can identify specific things that might need to change to reduce the likelihood of the problem occurring.

This last example of a fishbone diagram has even more detail. This is really helpful if there are multiple contributing factors, and they can be categorized under themes, like “poor support.”

3. Explaining stages of a process

Diagrams are great for helping people see how things unfold over time. Infographics that include such diagrams can be used to explain sales and service processes, project planning, and employee onboarding.

Flow charts are a natural option for outlining stages of a process. They are ideal for showing how decisions impact the way a process unfolds. Make sure the visual flows for its reader from top to bottom or left to right, and use arrows and/or colors to help guide them.

Here is a relatively simple example of a flowchart infographic that would be useful for employees.

If you want to spotlight a specific goal that a process is oriented towards, a funnel chart will fit the bill. As you’d expect, these charts are generally read from top to bottom.

If you need to add more detail, for example about the people and tasks involved throughout a process, Gantt charts are excellent for project management.

This one would help people remember the phases of a project across a lengthy time period.

This one is perfect for showing how different teams and team members are responsible for different project activities.

4. Telling a story

If you are looking for a way to describe and connect events, then you are probably thinking of creating a timeline infographic. These can be useful for retrospectives, for detailing company history, and for showcasing success stories.

Timelines are engaging when they include captivating visuals like photos and icons.

A horizontal format like this one would be great for a large poster. Notice that the use of color helps tell the story.

This one includes fun illustrations, and because it’s vertical it works well for websites and emails.

You can also use data visualizations to show changes over time in your timeline infographics. You can include any of the previously mentioned charts and diagrams to flesh out the story.

Here is an example with donut charts used not to describe data but to visualize the weeks passing.

Excited to get started? Anyone can visualize data with Venngage. Our professional templates and simple drag-and-drop editor make it easy to get started.

Last Updated: July 16, 2022

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:

#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

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

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 changes over time. For example, temperature changes, speed and distance.

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

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

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