Excel User's Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

Create Cycle Plots in Excel To Chart Seasonal Sales Data

If you have seasonal sales, or other measures of performance, Cycle Plots can offer greater insight into your performance than traditional charting techniques.

by Charley Kyd
February, 2008

If your company's sales are seasonable, you've probably seen a chart that looks something like this:

This Excel chart, shows the continuous trend in sales over an eleven-year period. Although the chart makes the general pattern obvious, it provides little help in understanding the details.

For example, which months have the most sales? Are sales becoming more seasonal or less so? On average, how much do we sell in June? Which month is growing most quickly? Most slowly?

Figure 2 shows another common way to present data like this. Rather than showing a continuous trend, we can use Excel to plot one data series per year.

But this approach offers little improvement. We can see the typical trends more easily during the year, but it's virtually impossible to compare year-to-year performance.

From another perspective, this approach creates an administrative problem: In January of every year we need to modify the chart to display another line. That's a maintenance step that we would rather avoid.

Naomi Robbins, writing in the January 2008 edition of the Perceptual Edge newsletter, introduces a new way to display the same data, using the Cycle Plot:

In this Excel chart, we easily can compare the performance of each month of the year. The lines with markers show the performance for each January, each February, and so on for all eleven years. The green horizontal lines show the average sales for each month during those years. And the brown lines show the trend in each month's sales during the years.

This data shows that June's sales are growing more quickly than those of the other months, and that sales in November and December are growing more slowly. Because our high-sales months are growing more quickly, this plot shows that our sales are becoming more seasonal.

In contrast, this Excel chart shows our sales by day of the week:

Here, no particular seasonality is evident during the week. Saturday's sales seem to be growing more slowly, but the random nature of the results makes the trend line fairly useless in this instance.

How to Create a Cycle-Plot Chart in Excel

Three steps are needed to create a Cycle Plot like those in Figures 3 and 4. First, set up the worksheet and its data. Second, assign range names to the data. And finally, create the chart.

1. The data begins as one long column of sales, as charted in the first figure. To arrange the data for Figures 2 through 4, I use SUMPRODUCT formulas to summarize the data by month and year, and also by weekday and year. You can learn more about SUMPRODUCT in the article, Use Excel's SUMPRODUCT To Summarize Worksheet Data.

2. With the exception of their formatting, the line charts in Figures 1 and 2 are quite common. Virtually any information about Excel charts can show you how to create similar charts.

3. The colors in the charts above aren't standard Excel colors. Users of Classic Excel can learn how to set up colors like this in Display Any Colors in Excel. Users of New Excel can assign the colors directly.

4. The charts have a dashboard-style appearance because I copied them from an unfinished Excel dashboard report that includes Cycle Plots. (Excel users can add Cycle Plots to Excel dashboards, just as we add any other chart type.) You can learn how to create and format Excel dashboards in my ebook, Dashboard Reporting With Excel.

So, with those items aside, let's concentrate on how to set up a Cycle Plot chart as shown in Figure 3.

Step 1: Set Up the Workbook and Data

Create a new workbook with one worksheet. Name the worksheet Chart and then save the workbook as CyclePlot.xls or CyclePlot.xlsx, depending on your version of Excel. Set up the top area of the worksheet like this:

This table shows data from January, 1997, through January, 2008. The remainder of row 18 contains #N/A. To eliminate the work of entering the numbers yourself, you can select the cells in the table below, copy them, and then paste them into Excel.

 1 1997 14 21 25 21 26 32 27 20 10 11 5 5 2 1998 18 24 28 24 33 37 30 25 13 14 6 6 3 1999 22 31 36 28 37 43 35 30 13 13 7 7 4 2000 25 32 38 34 39 48 38 29 14 14 8 8 5 2001 29 38 47 33 44 57 41 39 16 16 9 8 6 2002 29 35 49 34 43 57 41 37 20 17 9 10 7 2003 22 32 37 30 35 44 38 31 16 17 8 7 8 2004 25 34 41 33 39 47 44 32 17 17 9 8 9 2005 26 35 46 40 47 61 47 41 20 18 9 10 10 2006 29 39 55 38 55 67 53 41 19 20 11 11 11 2007 38 48 60 49 57 79 62 54 26 26 13 11 12 2008 40 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

Be sure to format the gray border rows as shown in rows 6 and 19. And make sure the border rows are empty.

The formula at the cell address below contains an array formula:

C20:  =ROWS(C6:C19)-SUM(IF(ISNA(C6:C19),1,0))-2

To enter it as an array formula, type in the formula as shown, but hold down your Ctrl and Shift keys before you press Enter. After you do so, Excel will begin and end your formula with braces: { and }. Then copy the cell to the right as shown.

You now can enter the formulas that return the averages and the trends. To do so, add the following sections to your worksheet:

Columns A and B have the values shown. Here are representative formulas for the other numbers:

C24:  =AVERAGE(OFFSET(C\$6,1,0,C\$20))
C25:  =C24

Copy cell C24 to the right through cell N24. Copy cell C25 to the range C25:N35.

C40:  =TREND(OFFSET(C\$6,1,0,C\$20),OFFSET(\$A\$6,1,0,C\$20),\$A40)

Copy cell C40 to the range C40:N51.

ExcelUser, Inc.
http://www.ExcelUser.com