Cycle Plot – Instruction on Excel

OVERVIEW

Cycle Plot is mentioned in the book “Now you see it” of Stephen Few as a good tool to present trend overall of different statistic over times. For example, the below graph show how many blog visits in different months but also flag the performance of each month through 2002 – 2013.

cycle-plot-1

In this graph, the trend present performance of each month through years and the marker is average value of each month to compare with the trend and with other months.

  • Trend: presents overall performance in that month through years.
  • Marker: presents average value of that month in period of years. This value can also be the YTD value to show the trend of the current year. However, it is more beneficial to use the average value and set the trend of the current year in another graph for better visualization.

CREATE ON EXCEL 

  • Concept: Use Combo Graph with Series 1 (Month) present month name and Series 2 (Average) present average on the Primary Axis. Series 3 (Data) present data of each month over years on the Secondary Axis.
  • Prepare data:

Cycle Plot 2.PNG

  • NOTE: There are three tables in the above data sample
    1. Month: This table will be used for Series 1 to present the month name for horizontal axis. The value is so that it will not flag any data point on the graph.
    2. Data: This second table will be used for Series 3 to present data of each month over years and will be the trend for each month. Row 5 and 18 are included as blank rows with purpose to separate the trend in each month on the graph.
    3. Average: This last table will be used for Series 2 to present average. It is calculated with Average formula below:
B21 = AVERAGE(B6:B17)
  • Use Name as Data Shortcut: Open Formula -> Name Manager -> New to create a shortcut for long data series for Series 2

3

  • In the picture above, I have already created three Name shortcuts. The most important shortcut is the PlotData, in which the refers link has to be in order of value for each month. Start with Jan on column B, data range from B5:B18 and next Feb on column C, data range from C5:C18. Reference link sample with tab name Data:
=Data!$B$5:$B$18,Data!$C$5:$C$18,Data!$D$5:$D$18,...,Data!$M$5:$M$18
  • Insert a Chart and Select Data:
    1. Series 1: Month

5.PNG

Edit Horizontal Axis Labels

6.PNG

2. Series 2: Average

7.PNG

3. Series 3: Data

8.PNG

  • Change Chart Types: Series 1 (Month) and Series 2 (Average) on Primary AxisSeries 3 (Data) on Secondary Axis.

9.PNG

  • Make sure both Vertical Axes have same range. In this sample, it is 0 – 90.
  • Show both Primary Horizontal and Secondary Horizontal Axes. The Primary Horizontal will be the Horizontal we choose for Month (Jan – Dec). The Secondary Horizontal is the multiple number of data. Show both of these Axes to merge them together.

10.PNG

  • Hide Secondary Horizontal by setting no fill, no line, no color and font = 1

11.PNG

TIPS AND OPINIONS

  • Show YTD data instead of AVERAGE: You can use Series 2 with a specific Year data or the most recent year data.
  • Color: Use proper color for Average, here I use color light gray and a line marker not to interrupt the trend.

REFERENCES:

  • Few, S. (n.d.). Now you see it: Simple visualization techniques for quantitative analysis.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s