For business users of Microsoft Excel Free guides and templates

Charts for Managers

How to 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, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

(Note: You can get a working copy of Figures 1-4 so you can Display Seasonal Sales with Cycle Plots. You also can Add a Cycle Plot Chart to Your Excel Dashboards.)

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

A typical chart of seasonal data.

Managers...
Charley Kyd can personally help you to apply the Excel methods in this article to your own organization.

Click here to learn more.
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 each 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 as in the chart above, we use Excel to plot one data series per year.

Another typical way to show seasonal data, with one line per year.

But this approach offers little improvement. With only a few years displayed, we could see the typical trends more easily during the year. But with many years displayed, as above, the swarm of lines becomes meaningless.

Naomi Robbins, writing in her book, Creating More Effective Graphs, introduces a better way to display the same data, using Cycle Plots:

A Cycle Plot chart, showing the trend over many years for each month in the year. The horizontal lines show average performance for each month.

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

I've created a version of this chart that you can add easily to your Excel dashboards. You can learn about this plug-in at Add Cycle-Plot Charts to Your Excel Dashboards.

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 sales are becoming more seasonal.

Still another way to show seasonal data is by the day of the week:

This chart of sales for each day of the week shows no obvious pattern.

Originally, I used a cycle plot for the weekly data. But I decided that using column charts provides more useful information for daily sales. Here, we see that sales tend to fall in the middle of the week, but rise on the weekends. But because the total variation is only about 2% of sales, the effect isn't significant in this instance.

Also, because the two sets of columns are virtually identical, we know that this pattern hasn't changed much between the first and second half of the total reporting period.

How to Create a Cycle-Plot Chart in Excel

To keep this article from turning into a short book, I'm going to explain only the most important elements of the original workbook that contained these charts. I've recently updated the workbook to use Excel Tables. And I've decided to include all four charts as Charley's Swipe File #74.

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

Before I get started, here are some questions that you might have about this process:

1. The data begins as one long column of sales in an Excel Table. I use INDEX-MATCH to retrieve the data for Figure 3.

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. Instead, they rely on the WSJ Web color scheme, available with Kyd Colors.

4. The charts have a dashboard-style appearance because I designed them for use with my Excel dashboard templates, Kyd War Room . As this image shows...

This version of the cycle plot chart is specially designed for Excel users who own Kyd War Room dashboard templates.

...you can add Cycle Plots to Excel dashboards, just as you can add any other chart type

With those items aside, let's concentrate on how to set up the basic elements of a Cycle Plot chart as shown in the dashboard and in Figure 3 above.

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.xlsx in Excel 2007 or above. Set up the top area of the worksheet like this:

The data used to calculate the Cycle Plots.

This table shows data from January, 2002, through July, 2013. 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 2002 14 21 25 21 26 32 27 20 10 11 5 5
2 2003 18 24 28 24 33 37 30 25 13 14 6 6
3 2004 22 31 36 28 37 43 35 30 13 13 7 7
4 2005 25 32 38 34 39 48 38 29 14 14 8 8
5 2006 29 38 47 33 44 57 41 39 16 16 9 8
6 2007 29 35 49 34 43 57 41 37 20 17 9 10
7 2008 22 32 37 30 35 44 38 31 16 17 8 7
8 2009 25 34 41 33 39 47 44 32 17 17 9 8
9 2010 26 35 46 40 47 61 47 41 20 18 9 10
10 2011 29 39 55 38 55 67 53 41 19 20 11 11
11 2012 38 48 60 49 57 79 62 54 26 26 13 11
12 2013 40 41 53 42 52 67 54 #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 }. After you do so, 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:

The averages and trends of the sales data.

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.

Step 2: Set Up the Range Names

Now comes the key step. You're going to create three range names that probably are unlike any Excel range names you've created before. You're going to start the process by selecting twelve "discontiguous" ranges.

To begin, click and drag from cell C6 through C19, as you normally would to select that range. Release your mouse button.

Hold down your Ctrl key.

Now click and drag from cell D6 through D19. Release your mouse button again. If you weren't holding down your Ctrl key, Excel would move your selection to this second range. But instead, Excel now adds that second range to your selection.

With your Ctrl key still held down, work your way across the table in Excel, selecting a total of 12 independent ranges, one for each month. In the figure below, you can see the tiny slice of white space between each selected column. This tells you that each of those 12 columns are individually selected. That is, they're discontiguous.

You select each column in this table individually, not as one large area.

Now, you need to assign a range name to this selection. To do so in New Excel (Version 2007 and above) choose Formulas, Defined Names, Define Name.

In the dialog, enter PlotData as the name, then choose OK.

Then, as you did with the PlotData range, discontiguously select the 12 columns in the PlotAvg section and define this selection as PlotAvg. Do the same for the PlotTrend section, naming the selection PlotTrend. In both cases, make sure you include the gray border rows above and below the columns of data.

Finally, define two normal range names. Define DateText to refer to the range C1:N1, as shown in the figure below. Then define DateValue to refer to the range C2:N2.

Step 3: Create the Chart

Now that the foundation is prepared, you can create the Cycle Plot chart. The first data series will display only the X-axis labels shown in figures 1 through 4. The other three data series will display the lines shown in Figure 3.

To begin, select the range C1:N2, as shown here.

The beginning of the data to be charted.

To chart this data in New Excel (Excel 2007), choose Insert, Charts, Line. Choose the top-left sub-type.

The chart object will display the months in the X axis, but show no data.

You won't need the chart's Legend. Therefore, select it in your chart then press the Delete key to delete it.

It's generally a good idea to change cell references to range names. This helps to document our formulas and adds flexibility. To assign the names, replace the shaded areas in this formula...

=SERIES(,Sheet1!$C$1:$N$1,Sheet1!$C$2:$N$2,1)

...with the names shown here...

=SERIES(,Sheet1!DateText,Sheet1!DateValue,1)

...after you do so, Excel will change the formula to...

=SERIES(,CyclePlot_01.xlsx!DateText,CyclePlot_01.xlsx!DateValue,1)

Now let's add the second SERIES function, which will display the Cycle Plot data.

The chart after the second SERIES function is added.

You'll add the series in two steps. First select any two-cell range of data in one row, say C7:D7. Copy this range and paste it to your chart.

When you do so, your chart will look something like this.

At this point, it's a good idea to hide the SERIES 1 line. To do so, you need to select it. This is difficult to do because the SERES 1 line is overlapping the X-axis border.

One easy way to get around this problem is to select your Chart Area and then press the Down Arrow on your keyboard once. Doing so selects the most-recently created SERIES function. (If you happened to choose some other object in the chart, keep pressing the Down Arrow. Eventually, the SERIES function will appear in your formula bar.)

With the SERIES 1 formula showing in your formula bar, press Ctrl+1 to display the Properties dialog for the series. Choose the Line Color tab. Choose No Line. Then choose Close.

Next, select the SERIES 2 function and replace the reference to C7:D7 with a reference to the range PlotData. That is, change...

SERIES(,CyclePlot.xls!DateText,Chart!$C$7:$D$7,2)

...to...

SERIES(,CyclePlot.xls!DateText,Chart!PlotData,2)

After you enter the change, Excel will change the SERIES formula to...

=SERIES(,CyclePlot.xlsx!DateText,CyclePlot.xlsx!PlotData,2)

Now the chart is starting to look like a chart.

...and your chart will look something like this.

The good news is that the chart now displays the Cycle Plots. The bad news is that the X axis labels have been messed up.

The reason for this problem is that SERIES 1, our date labels, and SERIES 2, our Cycle Plot, currently are sharing the same X axis, but their requirements conflict.

To fix this problem we need to set up the Cycle Plot series to use the secondary axis.

First, select SERIES 2 by clicking on one of the Cycle Plot lines. Press Ctrl+1 to launch the Properties dialog, and then choose Series Options, Secondary Axis, Close.

Excel "fixes" the axis problem by ignoring all but the first 12 values in your data, putting the values in sync with your 12 month labels.

Choose Layout, Axes, Axes, Secondary Horizontal Axis, Show Left to Right Axis. Then choose Layout, Axes, Axes, Secondary Vertical Axis, None.

Your chart will look something like the one above.

In both versions of Excel, if your chart isn't wide enough, or your font is too large, the X-axis labels rotate to a vertical position. To correct that problem, select the X axis, press Ctrl+1, and then:

  • In Classic Excel, in the Alignment tab, click the Degrees up-arrow button to change the degrees to 1. Then click the down-arrow button to change the degrees back to 0. Doing so removes the Automatic orientation, forcing the labels to be horizontal.
     
  • In New Excel, in the Alignment tab, click the Custom Angle up-arrow button once and the down-arrow once. Doing so forces Excel to accept a custom angle of 0 degrees.

We now must get rid of the label and tick-mark clutter at that top of the chart. To do so, select the top axis, press Ctrl+1, and then:

  • In Classic Excel, in the Patterns tab, choose None as necessary for Major tick mark type, Minor tick mark type, and Tick mark labels. Then choose OK.
     
  • In New Excel, in the Axis Options tab, select None as necessary in the dropdown list boxes for Major tick mark type, Minor tick mark type, and Axis labels. Then choose Close.

After you make these adjustments, your chart should have lost its clutter at the top.

It's all down hill from here..

We need to add two more lines to the chart. One will display the averages for each month, and the other will display the trend lines. To do so, select the Cycle Plot line. You'll see the following formula in your formula bar:

=SERIES(,CyclePlot.xls!DateText, CyclePlot.xls!PlotData, 2)

Select the entire formula in your formula bar, then press Ctrl+C to copy it.

Select your chart. Doing so will dismiss the SERIES formula from your formula bar. Press Ctrl+V to paste your copied series to your formula bar. Then modify the copied formula, so that the shaded part below is as shown.

=SERIES(,CyclePlot.xls!DateText, CyclePlot.xls!PlotAvg, 3)

Finally, press Enter to enter this new formula.

Select your chart again. Press Ctrl+V again to past your copied SERIES formula again. Modify the copied formula so that it looks like this:

=SERIES(,CyclePlot.xls!DateText, CyclePlot.xls!PlotTrend, 4)

The top chart is from Excel 2003; the bottom chart is from Excel 2007 or after.And press Enter.

When you're done, you should have a chart that looks something like the ones shown here. The top chart is from Classic Excel and the bottom chart is from New Excel.

In both cases, you'll probably want to experiment with colors, line thicknesses, and markers. (The top example has fuzzy thick lines because Classic Excel automatically added markers that you'll need to remove.)

To adjust the lines, select a line in your chart, press Ctrl+1, and then adjust the relevant settings that you'll find.

Also, you might want to see whether vertical gridlines improve your chart. To set them, select the chart and then:

  • In Classic Excel, choose Chart, Chart Options, Gridlines. Under Category (X) Axis, check Major Gridlines. Then choose OK.
     
  • In New Excel, choose Layout, Axes, Gridlines, Primary Vertical Gridlines, Major Gridlines.

To add or reduce the number of years of data that your chart displays, merely insert or delete rows of data between the shaded gray borders in the PlotData section, and then adjust the rows of formulas as needed in the other two sections.

Finally, one more time, you can get a working copy of Figures 1-4 so you can Display Seasonal Sales with Cycle Plots. You also can Add a Cycle Plot Chart to Your Excel Dashboards.



Tags:#excel, #charts, #seasonality, #cycle-plot, #cycleplot, #dashboard, #array formula, #SERIES,

Add Cycle-Plot Charts to Your Excel Dashboards

Excel Charts, Seasonality, & Analysis: Five Lessons from the WSJ

How to Create a Rolling Forecast of Seasonal Sales in Excel

Use Excel's SUMPRODUCT To Summarize Worksheet Data

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.