- Choosing a Chart Type
- Understanding a Date-Based Axis Versus a Category-Based Axis
- Using a Chart to Communicate Effectively
- Adding an Automatic Trendline to a Chart
- Showing a Trend of Monthly Sales and Year-to-Date Sales
- Understanding the Shortcomings of Stacked Column Charts
- Shortcomings of Showing Many Trends on a Single Chart
- Using a Scatter Plot to Show a Trend
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using a Scatter Plot to Show a Trend
Typically, trends are shown with line or column charts. One popular chart from the blogosphere is bucking this trend. This chart, which shows a trend using a combination of two scatter plots, has been published in the blog of professor Charles H. Franklin from the University of Wisconsin (see http://politicalarithmetik.blogspot.com/
2005/11/approval-of-president-bush-2001.html).
The advantage of this chart is that the solid line in the chart shows the trend of an average score from month to month. The individual dots in the scatter chart show the individual scores. The degree of scatter in the gray dots gives you an idea of the variation in the individual scores.
As shown in Figure 3.40, my Excel replica of this chart is based on data that I scraped from www.pollingreport.com/BushJob1.htm.
)
Figure 3.40 This trendline is backed by a scatter plot showing the degree of scatter of individual results.
The process of creating this chart involves these steps:
- Collect all the individual results. Column A contains the month, stored as a real date. Column B contains the individual score that will be shown as the dots in the chart. If you have 60 months of data, this dataset might include hundreds of rows of data.
- Build a pivot table to summarize the original dataset, by month. The pivot table should have month in the row area and average of score in the data area.
- In column C, build a formula that gathers the average score for each month. The goal is to report the average month in the first row for each month and then use #N/A values for the remaining rows for that month. If the dates start in A2, the formula is =IF(A2=A1,NA(),VLOOKUP(A2,$V$4:$W$74,2,FALSE)). Figure 3.41 shows a few rows of the dataset. Dot scores appear in the second column, and line scores appear once per month in the third column. The results of the pivot table appear in the fourth and fifth columns.
Figure 3.41 The underlying detail used to create the chart in Figure 3.40.
- Build a chart based on the first two columns. Select Scatter Chart with Only Markers.
- Format the data series to use a light-colored marker of a small size.
- Select the chart. A blue outline appears around the data in the second column. Grab the top-right handle on the blue outline and drag to the right to incorporate the data in the third column.
- In the Layout ribbon, select the new series from the Current Selection drop-down.
- In the Design ribbon, change the chart type to a scatter chart with straight lines. Format the line series as a thicker line.
)
Although Professor Franklin's innovative chart and blog feature mostly political charts, you can easily adapt this concept to your business.
My coauthor on Excel for Marketing Managers, Ivana Taylor of Third Force Marketing, is a huge proponent of asking your customers how you are doing. Ivana provides a service whereby she randomly calls 20 customers each month to get a feel for how your customers approve of your service. This data would be a perfect dataset to port to Professor Franklin's chart. Rather than showing only a "consensus" quality score, you could easily see the degree of scatter around the average score.