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.
- Excel Sample Download
- Cycle Plot:
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:
- NOTE: There are three tables in the above data sample
- Month: This table will be used for Series 1 to present the month name for horizontal axis. The value is 0 so that it will not flag any data point on the graph.
- 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.
- 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
- 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:
- Insert a Chart and Select Data:
- Series 1: Month
Edit Horizontal Axis Labels
2. Series 2: Average
3. Series 3: Data
- Change Chart Types: Series 1 (Month) and Series 2 (Average) on Primary Axis; Series 3 (Data) on Secondary Axis.
- 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.
- Hide Secondary Horizontal by setting no fill, no line, no color and font = 1
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.
Few, S. (n.d.). Now you see it: Simple visualization techniques for quantitative analysis.