How to Create Side-by-Side Time Comparisons in Excel Reports
One of the most common concepts used in Excel dashboards and reports is the concept of trending. Comparative trending is a simple concept. You chart two or more data series on the same chart so that the trends from those series can be visually compared.
You can create a chart that presents a side-by-side time comparison of three time periods. With this technique, you can show different time periods in different colors without breaking the continuity of the overall trending. Here’s how to create this type of chart:
Structure your source data similar to the structure shown in this figure.
Note that instead of placing all the data into one column, you’re staggering the data into respective years. This tells the chart to create three separate lines, allowing for the three colors.
Select the entire table and create a line chart.
This creates the chart shown in this figure.
If you want to get a bit fancy, click the chart to select it, then right-click and select Change Chart Type from the context menu that opens.
The Change Chart Type dialog box opens.
Select Stacked Column Chart.
As you can see in this figure, your chart now shows the trending for each year in columns.
Would you like a space between the years? Add a space in the source data (between each 12-month sequence), and a space is added in the chart.
This figure shows each year separated with a space.