How to Create Stacked Time Comparisons in Excel Reports
One of the most common concepts used in Excel dashboards and reports is the concept of trending. A trend is a measure of variance over some defined interval — typically time periods such as days, months, or years. Comparative trending is where you chart two or more data series on the same chart so that the trends from those series can be visually compared.
The stacked time comparison places two series on top of each other instead of side by side (which is another common way to do comparative trending). Although this removes the benefit of having an unbroken overall trending, it replaces it with the benefit of an at-a-glance comparison within a compact space. This figure illustrates a common stacked time comparison.
To create a stacked time comparison, follow these steps:
Create a new structure and add data to it like the one shown in this figure.
Highlight the entire structure and create a column chart.
Select and right-click any of the bars for the 2010 data series and then choose Change Series Chart Type.
In the Change Chart Type dialog box, select the line type in the Line section.
This technique works well with two time series. You generally want to avoid stacking any more than that. Stacking more than two series often muddies the view and causes users to constantly reference the legend to keep track of the series they’re evaluating.