Horizontal Panel Chart - Monthly Cycle Chart
Updated 2/25/08 - Download file includes VBA procedure to automate chart preparation

Trend charts show the progression of your data over time. Some of our time series data exhibit cyclical patterns,  possibly annual cycles related to seasons or weekly patterns related to work schedules.  In this tutorial, we will walk through producing monthly cycle chart in Excel.

You can download the example workbook for this tutorial here.

1. Trend Chart and Data

The chart below-left- shows a long term trend chart; the chart on the right shows a Monthly cycle chart of the same. This data set reflects monthly residential real estate listing inventory for the period Jan., 1993 to April, 2006.

Review of this trend chart shows a strong cyclical pattern as well as a significant drop in inventory in late 1999.

2. Monthly Cycle Chart

The monthly cycle chart, with the 12 months of the year across the X axis, shows both the average monthly values and the yearly trends for each month.

The monthly cycle chart shows that July is the highest inventory month and December is the lowest month. It also shows that every month had a marked drop-off in late 1999. This chart helps us look deeper into our data to se if there is an underlying structure not apparent  in the overall trend.

3. Making a Monthly Cycle Chart in 3 Steps

To see 3 minute how-to Flash video, press arrow in video window.

The Mo_Cycle_Chart_VBA.xls workbook includes several VBA procedures that automate Monthly Cycle Chart preparation. User prepares the chart in 3 steps:

Step 1: Add Data to Source_Data Sheet: Add your data to the Source_Data  sheet, starting in Cell A2. Column A must have valid Excel Date data. Column B has the actual data to be plotted.

Data does not need to be sorted in any particular manner.

Step 2: Run Update Procedure: When source data entry complete, press Update Button on Source_Data sheet. This will execute a VBA procedure that  prepares the data table in the Mo_Cycle sheet.

Step 3: Add Titles to Monthly Cycle Chart: The monthly cycle chart is automatically updated when the Update Button is pressed. Go to the Mo_Cycle sheet to view the chart. You can edit the chart title and Y axis title by entering desired text in Cells J2 and J4, respectively.

Custom Format: The monthly cycle chart is a standard Excel XY chart. You can adjust the line style - color and number formats by using standard menu selections. Please note that any format changes will be lost when you leave the sheet and return to it because the chart format procedure triggers when you enter the Mo_Cycle_Chart sheet. To save your format changes, make a picture copy and paste to a new blank sheet.
 

Building a Monthly Cycle Chart

To build this chart we use a data transformation technique to convert the X axis date values so that they plot in the appropriate month panels, as illustrated in the schematic below.