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 more insight into your performance than traditional charting techniques.

2268

If you have seasonal sales, or other measures of performance, Cycle Plots can offer more insight into your performance than traditional charting techniques.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.)

A typical Excel chart of seasonal data, which uses one line to display several years of data.

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 way to display seasonal data is to plot one line per year in Excel.

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:

An Excel Cycle Plot chart the shows 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.

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:

An Excel Cycle-Plot Chart that shows the trend by day of the week over twelve months.

And here’s another way to do it:

An Excel Cycle-Plot column chart, which shows the average sales by day of the week over twelve months.

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:

The staging table for Chart 1, which has one line that shows the seasonal patterns.

And here’s the top of the staging table for Chart 2:

The staging table with seasonal data arranged to display a different line for each year of performance.

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:

One staging table for Chart 3, showing the trends in seasonality by month for each month of the year.

And for the average values for Chart 3 we have:

The staging table for the chart line that shows average sales for each month over several years of seasonal performance.

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:

Staging table for the trendline through the plots of seasonal data by year for each month.

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:

Staging table for Chart 4, which plots actual sales data per month for each of seven days of the week.

And the average values for Chart 4:

Staging table for the horizontal line that shows averages of daily sales for each day over the past year.

And the trend values for Chart 4:

Staging table that shows the trend in average sales by day of the week.

And finally, here are the values for Chart 5, the column chart:

Staging table for Chart 5, which shows the percentage values for each day with a week.

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.

The staging table with Actual data for Chart 6, with each column selected discontiguosly.

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:

An Excel Cycle Plot chart the shows the trend over many years for each month in the year. The horizontal lines show average performance for each month.

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:

The two rows with date information for Chart 6.

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.

Excel's Size & Properties pane.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:

The first step in creating the cycle-plot chart.

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.

The staging table with Actual data for Chart 6, with each column selected discontiguosly.

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

The next to adding a cycle plot.

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.