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:
1Structure 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.
3If 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.
4Select Stacked Column Chart.
As you can see in this figure, your chart now shows the trending for each year in columns.