If your company’s sales are seasonable, you’ve probably seen a chart that looks something like the first one below.
This Excel chart shows the continuous trend in sales over an eleven-year period. With the exception of a dip during the years of the Great Recession, sales seem to be on the rise.
Although this chart makes the general pattern obvious, it provides little help in understanding the details of the sales performance. This is because the finer details are obscured by the significant seasonality of the sales.
(You can download the two workbooks discussed in this article here.)
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 another 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 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:
And here’s another way to do it:
I first used the cycle plot for the weekly data. But then I wondered whether 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. You can download the actual workbooks here.
Four steps are needed to create a Cycle Plot like Figure 3. Here’s the first step:
Step 1: Set Up Your Data.
This is the easiest step, conceptually. You just need to have your data in Excel so that you can access it easily—usually with the SUMIFS function. Typically your data will be in an Excel Table.
Step 2: Set Up the Staging Tables
Each of the previous charts gets its data from one or more staging tables. And the key to each chart is the structure of the staging table.
All of them use formulas to return data from the same dataset. And in all of them, I applied a green fill to show you the areas of the staging tables that each chart references. You’ll find these Tables in CyclePlotA.xlsx workbook, that you can download here.
Here are the top few rows of the staging table for chart 1, the chart with one line:
And here’s the top of the staging table for Chart 2:
The formulas that return numbers in the green cells use the pattern shown here:
B15: =INDEX(MonthlyActual,MATCH(DATE($A15,B$13,1),MonthlyDates,0))*ScaleReport
Normally, we’d use the SUMIFS function to return values from the Table with monthly values. But these formulas use INDEX-MATCH instead. This is because if MATCH can’t find a date in the Table, it returns #N/A—which is exactly what we want for the values to be plotted.
On the other hand, if we’d used SUMIFS, it would have returned zero for dates not found. And that would have caused the chart to plot zeros for the values of the dates not found.
However, if your version of Excel includes the LET function you could use a pattern like this to achieve the same result:
=LET( MySum, SUMIFS(whatever), Result, IF(MySum=0, NA(), MySum), Result)
The data values for Chart 3 are:
And for the average values for Chart 3 we have:
Here are the formulas for the first column of numbers:
C53: =AVERAGEIFS(C$35:C$48,C$35:C$48,”<>#N/A”)
C54: =C53
In cell C53, the AVERAGEIFS function averages the numbers in its own column in the staging table for Chart 3a…as long as those numbers don’t equal #N/A. The function can anchor its references in the two gray rows because AVERAGEIFS (and AVERAGE) ignore empty rows.
And because each green column in the 3b staging table contains the same value, cell C54 and the green cells below it all return the values from the previous cells.
Here are for the TREND values in Chart 3:
Here, the formulas for Chart 3c must use the OFFSET function to calculate the TREND. This is because TREND doesn’t allow us to reference the gray rows. Here’s one of the formulas:
C70: =TREND(OFFSET(C$35,1,0,C$68),OFFSET($A$18,1,0,C$68),$A70)
And here are the syntaxes for OFFSET and TREND:
=OFFSET(reference, rows, cols, height, width)
=TREND(known_ys, known_xs, new_xs, const)
Therefore, in cell C70, the first OFFSET function begins in the top gray row, moves the reference down one row, moves it neither left or right, and then gives it a height of 12 rows, as calculated by cell C68.
The second OFFSET returns a similar range for column A. And then the TREND uses these ranges to calculate the values for the first through the twelfth rows.
Similarly, for Chart 4, here are the data values:
And the average values for Chart 4:
And the trend values for Chart 4:
And finally, here are the values for Chart 5, the column chart:
Step 3: Set Up the Range Names
Now we’re going to create three range names that probably are unlike any Excel range names you’ve ever created before. You’re going to start the process by selecting twelve “discontiguous” ranges.
To keep things simple, we going to use the following table, which you’ll find in CyclePlotB.xlsx, which you can download here, along with CyclePlotA.xlsx.
To begin, click and drag from cell C7 through C20, 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—with eight rows hidden—you can see the tiny slice of white space between each selected column. This tells you that each of those 12 columns is individually selected. That is, they’re discontiguous.
After I selected the ranges as shown, I assigned a range name to this selection. To do so I chose Formulas, Defined Names, Define Name. In the dialog, I entered PlotData as the name, then chose OK.
Then I did the same for the other two staging tables in the workbook. I discontiguously selected the 12 columns in the Plot Average section and defined this selection as PlotAvg. And I did the same for the PlotTrend section, naming the selection PlotTrend. In all cases, I made sure to include the gray border rows above and below the columns of data.
Why use the gray border rows? They cause a break in the cycle-plot line. That gives us the 12 line segments you see for the black lines, the blue lines and the gray lines here:
Finally, I defined two normal range names. I defined DateText to refer to the range C2:N2, as shown in the figure below. Then I defined DateValue to refer to the range C3:N3.
Step 4: Create the Chart
Now that the foundation is prepared, we can create the Cycle Plot chart. The first data series will display only the X-axis labels shown in charts 1 through 4 above. The other three data series will display the lines shown in the preceding chart.
To begin, select the green range C2:N3, as shown here:
To plot this data, choose Insert, Charts, Line, and then choose the top-left sub-type.
Your chart object will display the months in the X axis, but show no data.
You won’t need the chart’s title. Therefore, select it in your chart then press the Delete key to delete it.
Select the X axis, which you’ll need to modify in two ways.
With the X axis selected, press Ctrl+1 display the Format Axis pane. Then choose the Size & Properties icon, as shown here.
In the Alignment section, in the Custom Angle edit box, click the up-arrow once and then the down-arrow once. This will set the Custom Angle to zero.
Still in the Format Axis Pane, click the Axis Options (column chart) icon, as shown in the previous figure. In the Axis Options section, for the Axis Position setting, click On tick marks. That should give you a chart that looks like this:
It’s generally a good idea to change your chart’s cell references to range names. This helps to document your formulas and adds flexibility. To assign the names, click on the blue line above the X axis to display the SERIES formula in your formula bar. Then change this formula…
=SERIES(,Data!$C$2:$N$2,Data!$C$3:$N$3,1)
…to this formula…
=SERIES(,CyclePlotB.xlsx!DateText,CyclePlotB.xlsx!DateAxisValue,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 two-cell range of data in one row, say C8:D8, as shown here. 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 can be 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 entire Chart Area and then press Ctrl + DownArrow on your keyboard once. Doing so selects the most-recently created SERIES function, which is the red line. Press it again to select the blue line.
Alternatively, with your chart selected, choose Format. And then, in the Current Selection group, you’ll see a dropdown list box that allows you to choose any part of your chart that you want to select. So in this case, you would choose Series 1.
With the SERIES 1 formula showing in your formula bar, press Ctrl+1 to display the Format Data Series pane. Choose the Fill & Line (paint can) icon. In the Line section, choose No Line.
Next, select the red line, as shown in the chart above. Replace the reference to C8:D8 with a reference to the range PlotData. That is, change…
=SERIES(,CyclePlotB.xlsx!DateText,Data!$C$8:$D$8,2)
…to…
SERIES(,CyclePlot.xls!DateText,Chart!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 are a disaster.
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 significantly. This is because the red lines are plotting 12 years of monthly data…144 data points in all. On the other hand, the X axis is plotting only one year of monthly labels.
To fix this problem we need to take four steps.
First, we set up the red Cycle Plot series (Series 2), to use the secondary axis. To do so, select Series 2 by clicking on any of the red lines. Press Ctrl + 1 to display the Format Data Series pane, and then in the Series Options section, click Secondary Axis.
Second, with the chart selected, choose the Chart Elements “chicklet” to the right of the chart, and then set the Axes settings you see here:
Third, we need to remove the cluttered axis that’s now at the top of the chart. To do so, click on that cluttered area, press Ctrl + 1 to display the Format Axis pane, and then, in the Axis Options (column chart) icon, in the Labels section, set the Label Position to None.
And finally, in the Fill & Line (paint can) icon, in the Line section, click No Line for that secondary axis.
Your chart now will look something like this:
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 red Cycle Plot line. You’ll see this formula in your formula bar:
=SERIES(,CyclePlotB.xls!DateText, CyclePlotB.xls!PlotData, 2)
Select the entire formula in your formula bar, then press Ctrl+C to copy it.
Select the edge of your chart to display the Chart Area. Doing so will clear your formula bar. Click in your formula bar than press Ctrl + V to paste your copied series to it. Then modify the copied formula, so that the shaded part below is as shown.
=SERIES(,CyclePlotB.xls!DateText, CyclePlotB.xls!PlotAvg, 3)
Finally, press Enter to enter this new formula.
(Depending on your version of Excel, you might not be able to edit the SERIES formula in your formula bar. In this case, with your chart selected, choose Chart Design, Select Data, and use the Select Data Source dialog to edit your SERIES formula.)
Select your chart again. Press Ctrl + V again to paste your copied SERIES formula again. Modify the copied formula so that it looks like this:
=SERIES(,CyclePlotB.xls!DateText, CyclePlotB.xls!PlotTrend, 4)
And press Enter.
When you’re done, you should have a chart that looks something like this:
You’ll definitely want to experiment with chart size, colors, line thicknesses, and other formatting. To format the lines, select a line in your chart, press Ctrl + 1, and then adjust the relevant settings you’ll find.
To add a title, a subtitle, or text below the chart, set the Chart Area’s Fill setting to No fill and its Line setting to No line. And then enter your text in your worksheet. Chart 3 near the top of this page might give you some formatting ideas.
To add or reduce the number of years of data that your chart displays, merely insert or delete rows of data or formulas between the shaded gray borders in the three staging tables.
Again, you can download the two sample workbooks by clicking this link.
