Layering in Excel

Chandoo provides an interesting use of layering Excel objects to add background effects to charts. The example provided suggest many practical uses, but the underlying technique of layering has far broader application and is more powerful than Chandoo’s post indicates.

Layering is the placing of multiple charts and/or shapes over the underlying spreadsheet grid; the objects are made transparent, so multiple layers can be viewed at once. For charts the transaprency is achieved by setting ‘no fill’ for both the plot area and chart area formats. There are several reasons for layering:

• Integrating multiple charts
• Integrating charts with non-chart report elements
• Formatting and customizing charts
• Creating compound charts

I will cover each of these possible uses of layering. If you have any other uses or examples of layering please indicate in the comments. I assume I am only scratching the surface of this technique.

Integrating multiple charts

In a previous post Integrating report elements in Excel I demonstrate use of a common spreadsheet layer providing unifying zebra stripes and axes to integrate completely different chart types (e.g. stem-and-leaf-plot and scatter plots) or small multiples of the same chart type (e.g. dot plots). Alternatives to integrating multiple charts exist, but layering seems simpler, especially if different chart types are involved.

Integrating charts with non-chart report elements

Chandoo’s post provides a good example of this type of application.

Formatting and customizing charts

One example is creating a colored background. Zebra stripes is one example already noted. Another would be to highlight different time periods or different quadrants or sections of a graph. Jon Peltier provides some examples here and here of how to set background color for charts by using a stacked bar within the chart. However, layering seems to be a far simpler and flexible solution. Consider the following scatterplot. To color the four quadrants, the plot area and chart area backgrounds are set to ‘no fill’ and the plot area is snapped to a 10 x 10 grid of cells, which are colored accordingly. This is effectively the same effect that was achieved with the stacked bar chart method.

However, we can add many different types of chart customization by similarly adding text of formatting the underlying cells. Here sub-quadrants are created with different colors, borders, and labeling.

Another application is highlighting of groups such as quarters and months. Again, only the cells underneath the transparent graph are colored. The following chart was created using layering, which is relatively simple compared to a graph within a graph approach explained by Chandoo .

Chandoo’s example is dynamic. Likewise, the cell coloring can be made dynamic with some simple conditional logic within a macro. The benefit here is flexibility. If you wanted a more complicated pattern, it would only be a matter of specifying the colors of ranges of cells. The full code used for this example is as follows (The code is a bit redundant and could be made more concise, but it works for now):

Creating compound charts

One approach to creating combination charts is to select individual data series and change the chart type just for that data series. This method is explained by John Walkenbach, Jon Peltier, and Chandoo . Layering can also be used as an alternative to this approach. Here is a Pareto chart, which consists of a transparent line graph over a transparent bar graph. The plot sizes are snapped to the same cells so they overlap correctly.

Compared to the aforementioned method, the time to create the graph is roughly the same. However, I think the layering method offers advantages when dealing with more complex types of combinations. Here we have three layers of graphs: two line graphs and a bar chart. The two line charts use different axes.

Conclusion

In sum, layering is a method that is conceptually simple and intuitive, yet is both flexible and powerful in its application. In some cases, alternative methods exist to achieve the same effect as layering, but in nearly every situation layering offers greater flexibility. Moreover, in the absence of layering one would need to learn many different excel tricks, some of which are hard to remember, to perform the same tasks. In contrast, layering is one simple method amounting to little more than removing the chart and plot areas of graphs and using the underlying excel grid or other objects as background.

In a future post I will show how layering can be used to create create rugs and more informative axes.