Ever eat popcorn watching the Super Bowl?
When I was developing this chart, it made me think of popcorn, since chart series were popping on and off the screen just like popcorn in a hot kettle. So I've decided to call this kind of chart a Popcorn Chart. A couple weeks ago I was following a game on the NFL site with their Flash application. Their Flash app is called a Drive Chart. It shows the game, play by play, either historically or real time.
This was the first time I had ever seen their app and a few seconds into the game, I started thinking how I would go about recreating the app in Excel 2007. Here's a screenshot of my Excel 2007 version:
You can click on it to see the image in full size. The chart is interactive and will show you play by play how the Saints spanked the Cardinals in week 19.
For a frame of reference, here is NFL.com's Flash app that was the source of my inspiration:
To find the Flash app, click on the blue DRIVE CHART tab at the bottom of the GAME CENTER (top of page).
This is not a trivial chart that you can create by pressing F-11 on your keyboard. This is a compound chart driven by a bunch of formulas. The entire field of play is drawn with error bars. The chart has 69 chart series that pop on and off and scroll. Different series represent different sorts of plays for each team. The game clock is an inverted trapezoid shape. Most everything above the clock is highly formated worksheet cells. The chart can be run without VBA enabled, but you will have to click on a scroll bar to advance the game. If you enable macros, the chart can display (and pause, and rewind) the game automatically at various speeds. Finally it employs a nice visual element where the legend which is actually another chart fades in when you hover the mouse over the golden key.
For now, the chart is limited to displaying games that have already occurred, but for next season I would like to add a module to grab the plays as they happen off of the NFL site and allow the user to view live games, possibly several at the same time, with several copies of the chart on one sheet.
It's possible to learn many techniques for advanced charting from this one application. I am happy to share my tutorial on constructing this chart in Excel 2007, but just realize that it will take a couple of hours for you to complete the chart!
If you liked this article, please share it!