Home > Excel Dashboards  > 

Create Cycle Plots in Excel
To Chart Seasonal Sales Data

(Continued from Page 1)

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 Classic Excel, choose Insert, Name, Define.
   
• In New Excel, 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.

To chart this data:

• In Classic Excel, choose Insert, Chart. In the Standard Types tab, choose Line. Select the top-left sub-type. Then choose Finish.
   
• 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.

In Classic Excel, you'll probably want to change the gray Plot Area to white. To do so, select the Plot Area; press Ctrl+1 to launch the Format Plot Area dialog; choose None for the Area; then choose OK.

In Classic Excel, you'll also need to get rid of Auto Scale. To do so, select your chart area; press Ctrl+1 to launch the Format Chart Area dialog; choose the Font tab; uncheck the Auto Scale checkbox; then choose OK. (New Excel doesn't offer Auto Scale.)

In all versions of Excel, 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.xls!DateText,CyclePlot_01.xls!DateValue,1)

(If you use New Excel, the "xls" likely will be "xlsx" instead.)

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 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. This is particularly true in New Excel, where the line is very apparent.

To hide the line, you need to select it. Sometimes, this is difficult to do when it overlaps the bottom border of the Plot Area. One easy way to get around this problem in either version of Excel 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, a SERIES function will appear in your formula bar.)

With the SERIES 1 function showing in your formula bar, press Ctrl+1 to display the Properties dialog for the series.

• In Classic Excel, choose the Patterns tab. Choose None for the Line and (if necessary) the Marker. Then choose OK.
   
• In New Excel, 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.xls!DateText,CyclePlot.xls!PlotData,2)

(Again, if you use New Excel the "xls" likely will be "xlsx" instead.)

...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 has 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.

Select SERIES 2 by clicking on one of the Cycle Plot lines. Press Ctrl+1 to launch the Properties dialog, and then:

• In Classic Excel, choose Axis, Secondary Axis, OK.
  
  Choose Chart, Chart Options. In the Axes tab, make two changes to the Secondary Axis section: Check Category (X) Axis and Uncheck Value (Y) Axis. Then choose OK.
  
  During these changes, Excel adds markers. To remove them for now, select the Cycle Plot series again, press Ctrl+1, in the Patterns tab choose None for Marker, then choose OK.
  
  Your chart now should look something like the one below.
   
• In New Excel, 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)

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.

If you would like to get a working copy of all charts shown here, check back around the first of March.