A Template for Creating Cycle Plots in Excel

This blog entry was written by Bryan Pierce of Perceptual Edge.

A cycle plot is a type of line graph that is useful for displaying cyclical patterns across time. Cycle plots were first created in 1978 by William Cleveland and his colleagues at Bell Labs. We published an article about them in 2008, written by Naomi Robbins, titled Introduction to Cycle Plots. Here is an example of a cycle plot that displays monthly patterns across five years:

(Click to enlarge.)

In this cycle plot, the gray lines each represent the values for a particular month across the five-year period from 2009 through 2013. For instance, the first gray line from the left represents January. Looking at it we can see that little changed in January between 2009 and 2010, then values dipped in 2011 and then increased again in 2012 and 2013. The overlapping horizontal blue line represents the mean for the five years of January values.

The strength of the cycle plot is that it allows us to see a cyclical pattern (in this case the pattern formed by the means across the months of a year) and how the individual values from which that pattern was derived have changed during the entire period. For instance, by comparing the blue horizontal lines, we can see that June is the month with the second highest values on average, following December. We can also see that the values steadily trended upwards from January through June before dropping off in July. This much we could also see by looking at a line graph of the monthly means. However, using the cycle plot, we can also see how the values for individual months have changed across the years by looking at the gray lines. If you look at the gray line for June, you can see that we’ve had a steady decline from one June to the next across all five years, to the point that the values for May have surpassed the values for June in the last two years. Unless something changes, this steady decline could mean that June will no longer have the second highest average in the future. The decline in June is not something that we could easily spot if we were looking at this data in another way.

Despite their usefulness, one of the reasons I think we don’t see cycle plots more often is that they’re not supported directly by Excel. They can be made in Excel, but it’s a nuisance. To help with this problem, we’ve put together an Excel template for creating cycle plots using a method that we learned about from Ulrik Willemoes, who attended one of Stephen’s public workshops. It contains a cycle plot for displaying months and years, as shown above, and also a cycle plot for displaying days and weeks. All you need to do is plug in your own data and make some minor changes if you want to display a different number of years or weeks. Step-by-step instructions are included in the Excel file. Enjoy!

-Bryan

9 Comments on “A Template for Creating Cycle Plots in Excel”


By Nitesh Vallabh. October 23rd, 2013 at 9:16 pm

Thank you for this template. I can see some interesting applications of this technique for the data sets I work with.

Regards
Nitesh

By Allen Butler. October 24th, 2013 at 1:08 am

This is amazing. I often wished I had a way to show cyclical patterns over previous years in graphical form, but could never come up with anything as elegant as this. Thank you!

By Mathew Burke. October 24th, 2013 at 2:45 am

Thanks for publishing this template, I always learn a lot of new and interesting techniques following this blog (and a few others) and it’s nice to have another tool for the data I work with.

The graph certainly seems effective but I’m wondering how clear the potential signals are when used to display the results of 52 sets of information in one go - the eight weeks as seen in the template work fine for the example, but I’m sure some users have sets far larger than this readily available.

I’ll have to test this out but I’m confident I and many others could find a few interesting uses for this particular template.

Regards,
Mathew

By Andy Cotgreave. October 25th, 2013 at 4:22 am

Hi Bryan
This is a nice template and the end result looks great. It’s such a shame you need to do so much data processing in Excel in order to make them work.

Andy
@acotgreave
http://www.gravyanecdote.com

By Nick Davies. October 25th, 2013 at 8:38 am

Thanks for the article & template guys - much appreciated; that’s a very neat way of covering long term trends (I just wish this job provided me with enough data to utilise it!).

A quick look at the xls and you could reduce the complexity by only having four columns of data and switching between the pairs for each period - excel will not plot empty cells. Granted this would make the data less easy to read, but would simplify things. Actually, a third way would be to keep all the records in their original two columns (Month & Avg) and then separate each month with a blank row; whilst this would add a gap between each month on the chart, it shouldn’t interfere with the legibility of it, but would make producing the charts much more straightforward.

By Bryan Pierce. October 25th, 2013 at 11:53 am

Hi Nick,

Thanks for your suggestions. I would love to create the cycle plot in Excel using only two columns for the data, rather than the stair-step pattern, but I can’t get the horizontal axis and its labels to display properly. The stair-step pattern provides a way to “trick” Excel into rendering the horizontal axis properly, which seems to be the most complicated part of getting a cycle plot to display as a single graph in Excel. When I use a two-column approach, the blank rows that I have to add create extra gaps between the lines, as you mentioned, but these gaps are off-center, giving the cycle plot a bit of a haphazard look, as seen here:

Cycle Plot with Gaps

I tried to balance or eliminate the gaps using a few different approaches, but they created new problems that were worse. While it would be nice to make it a little easier to format the data in the template, we don’t want to do so in a way that undermines the visual design of the cycle plots produced by the template.

Regarding your suggestion of using four columns, I’m not exactly certain what you’re describing here, but I suspect it would have the same problems with the horizontal axis that I ran into trying to use two columns.

If you are able to get a cycle plot to render correctly in Excel using fewer columns, in a way that won’t make the template more complicated, please send it to us. We’re always eager to simplify, if we can do so without losing anything useful.

Thanks,
-Bryan

By Joerg. October 29th, 2013 at 6:04 am

Hi,

what about taking 12 or @Bryan 5 normal line charts, sticking them into 5 columns (pressing “alt” + moving the chart) and scale the Y-axis everywhere the same (alternative is to normlize all y-values and scale the y-axis e.g to 100)

Now you have the lines, the axis with 1,2,3,4,…. The only thing you need are the additional lables Monday, Tuesday. I would paste it to the cell below the chart and center it. Another possibility is to create your own x-axis in the chart (works best when normalization was done).

In my opionen it’s nothing else than a small multiple visualization. a real improvemnt would be to add the y-values to the datapoints and introduce a logic which hides the ones crossing the lines.

Regards

Joerg
http://dens.io

By Bryan Pierce. October 29th, 2013 at 9:59 am

Hi Joerg,

In the past, we’ve always recommended that people use multiple graphs to create cycle plots in Excel. However, we think that displaying a cycle plot as a single graph is worthwhile, despite the fact that the underlying data needs to be laid out in that awkward stair-step pattern.

Once the data is laid out in the stair-step pattern, a single graph is easier to work with if you want to move it outside of Excel or make changes. A single graph can also be set to automatically adjust the y-axis scale, whereas with individual graphs, you must manually set the upper and lower limits on their y axes so that they work for your data and are consistent across all the graphs. For our template, we wanted it to be as easy as possible for people to just plug in their data, without having to adjust the scales on all of the graphs.

-Bryan

By Joerg Decker. October 29th, 2013 at 12:27 pm

Hi Bryan,

you are right, usually it’s easier to handle one chart. But if you normalize your data (divide everything by the mix-max-range) and multiply it by 100 (if 100 is the fixed height of you y-axis) all charts are synchronized automatcally. That’s quite a nice way to get control over all the charting elements and also to add individual ones as additional labels or customized axis or e.g. a “zoom in” switch - and all without adjusting anything.

Free templates doing it that way are available for free. It is very impressing what excel can do using that little “trick” - doesn’t look like excel anymore.

But as you say, for a simple usecase like the one above it’s easier to use one single chart.

Regards from Germany

Joerg
http://dens.io