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, 20052014
The Father of Spreadsheet Dashboard Reports

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 elevenyear 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.
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:
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.
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
highsales 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:
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 CyclePlot 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 INDEXMATCH 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.
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:
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:
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.
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 Xaxis 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.
To chart this data in New Excel (Excel 2007), choose Insert, Charts, Line. Choose the topleft subtype.
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.
You'll add the series in two steps. First select any twocell 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
Xaxis 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 mostrecently 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)
...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 Xaxis 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 uparrow button to change
the degrees to 1. Then click the downarrow 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 uparrow button once and
the downarrow once. Doing so forces Excel to accept a custom angle of 0 degrees.
We now must get rid of the label and tickmark 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)
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.
Tags:#excel, #charts, #seasonality, #cycleplot, #cycleplot, #dashboard,
#array formula, #SERIES,
