+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
This tutorial shows how to construct my NFL Drive Chart that was introduced in a prior post.
--- Please note that this is an Excel 2007 chart and tutorial. It may work in Excel 2010, but I have not tested it. ---
Part 1 of the tutorial will create this football playing field:
If you have not downloaded the tutorial workbook, please get it now. You'll need it.
The Excel 2007 Drive Chart is an embedded chart so the first thing you should do is create a new sheet. Go ahead and color the sheet background now. I used RGB: 109, 109, 109, which can be set by selecting all the cells and using Format Cells, More Colors, Custom.
Insert a new chart in the sheet (Insert tab on the Ribbon; select Scatter and then Scatter with Straight Lines). To set the size of the chart, click on the Format tab of the Ribbon, and in the Size section enter a height of 2.78 inches and a width of 10.3 inches. Right-click on the chart and select Format Chart Area. For Fill, select Solid fill with a Transparency of 0%. Click on the Color button, More Colors, Standard, and select absolute White which is the color in the exact middle of the color hexagon. Click on the Plot Area (not the entire Chart Area, but the Plot Area inside of it) and stretch it so the it fills 100% of the Chart Area left and right. Nudge the Plot Area to the very top of the Chart Area and stretch it down so that it comes close to the bottom of the Chart, but not quite all the way (you can adjust this later).
We now have a blank canvas to create the chart on.
You might think the green grass section should be created by setting the Fill on the Plot Area. I made that mistake on my first attempt. The problem is that the Fill (as the name implies) will fill the entire Plot Are with the green grass. This means the End Zones will need to be masked over the grass. Additionally, my chart shows Field Goals being kicked past the End Zone into the broad white border. The only way for that to happen is to extend the Plot Area all the way to the extreme widths of the Chart Area, but then we will need additional masks over the grass for the white borders.
Another problem would be the Home Team logo in the center of the field. If we use a Fill on the Plot Area for the grass, the logo will need to be the background of the Chart Area. This decouples the logo from the playing field. If the chart area is ever resized, care will need to be given to recenter the logo in the middle of the playing field. This also means that the majority of the Chart Area will necessarily have a transparent background. We could make good use of a solid white background, but a transparent background to the entire chart will require more masking. A better strategy is to make the logo the background of the Plot Area, which keeps it coupled to the playing field. But doing so eliminates the possibility of creating the grass from Fill, as a Fill can be a color, a gradient, a texture, or a picture; but it can only be one of these things - they cannot be combined.
My solution was to draw the grass as one really fat error bar with no Cap. This is actually a fairly versatile technique which is used liberally to draw the two End Zones and all of the line markings on the grass. I learned the hard way that it is important to put the grass down first. When a chart has dozens of chart series, it can sometimes lose it's ability to adjust the order that items are drawn. Since the grass is the bottom of everything we draw on the playing field, make it first.
On the Setup sheet, you'll find the definitions for the field. The range Q1:S1 has the data to draw the grass. On your new sheet, click on the blank chart and then click the Design tab on the Ribbon, and then Select Data. Add a new series with the Series Name set to =setup!$Q$1, the X values set to =setup!$R$1, and the Y values set to =setup!$S$1. You'll notice that Excel draws a bunch of stuff on the chart. Unfortunately we don't want any of it. So let's take a small break from the grass to clean up the chart. Click on the Chart Title (it should say "Grass") and delete it. Click on the Legend and delete it as well. Click on the Layout tab on the Ribbon. Click on Gridlines and set the Primary Horizontal Gridlines to None.
Right click on the Horizontal Axis at the bottom of the chart and select Format Axis. Change the four settings at the top so that they are all Fixed. Their values should be:
Minimum = -15 (that's negative 15)
Maximum = 115
Major unit = 10
Minor unit = 1
Close the dialog and delete the Horizontal Axis by pressing the DELETE key on your keyboard. Note that this does not really delete the axis; it just makes it disappear. We can bring it back whenever we need it.
Right click on the Vertical Axis at the left of the chart and select Format Axis. Change the four settings at the top so that they are all Fixed. Their values should be:
Minimum = 0
Maximum = 9
Major unit = 1
Minor unit = 1
Close the dialog and delete the Vertical Axis.
We need to add the Home Team logo to the field; remember we are using the Plot Area for this.
Here is the logo for the background:
Save this logo to your computer.
Select the Plot Area and then click the Format tab on the Ribbon. Select Shape Fill and then Picture. Navigate to the folder where you saved the SAINTS logo. Insert that logo. It will fill the entire Plot Area. Bring up the Format dialog for the Plot Area and set the Stretch options Offsets to:
Left = 46%
Right = 46%
Top = 30%
Bottom = 30%
Now we can get back to the grass. Make sure the chart is still selected and click on the Error Bars in the Analysis section. Set the error bars to Error Bars with Standard Error. This adds both X and Y error bars to our grass series. We only need the Y bars, so delete the X error bars. In the Current Selection section of the Ribbon, click on the combo box and select Series "Grass" X Error Bars and then press the DELETE key on your keyboard. Now select the Y error bars in the same combo box. And then right underneath the combo box, click on Format Selection. Set the Vertical Error Bars display direction to Both and the End Style to No Cap. Set the Error Amount to Percentage and 101%.
You should now see a vertical bar at the 50 yard line. This is our grass.
On the left hand side of the Format Dialog, click on Line Style and set the Width to 568 pt.
To color the grass:
Click on Line Color on the left panel and select Gradient Line. Make sure Type is Linear and Angle is 90 degrees. For Stop 1 set the Stop Position to 4% and Transparency to 5%. Click on Color, More Colors and Custom and set the RGB to 86, 158, 80.
Click on Stop 3 and make exactly the same settings as for Stop 1, except for Stop Position at 95% and Transparency at 0%.
Click on Stop 2 and make these settings: Type = Linear, Angle = 90%, Stop Position = 50%, Transparency = 13%. Click on Color, More Colors and Custom and set the RGB to 127, 188, 122.
At this point your chart should look something like this:
Let's add the 5-yard lines to the field.
Click on the chart and then click the Design tab on the Ribbon, and then Select Data. Add a new series with the Series Name set to =setup!$Q$24 the X values set to =setup!$P$25:$P$125, and the Y values set to =setup!$Q$25:$Q$125. Click OK.
Click on the Layout tab of the Ribbon and then select Series "5-yd lines" from the combo box in the Current Selection section. Add Error Bars with Standard Error from the Analysis section. Delete the X error bars. Select the Y error bars from the combo box. Click on Format Selection (just below the combo box). Set direction to Both and end style to No Cap. Set Error Amount to Percentage and 98.4%. Click on Line Color on the left pane and select Solid Line with 35% Transparency. Click on the Color button and set it to absolute White. Click on Line Style on the left and set the Width to 1.25 pt. Click Close. It should look like this:
Progress!
This will now start to get repetitive. I'm sorry, but I know of no other way to do this, without building the chart in VBA.
Let's add the 2-yard lines.
Click on the chart and then click the Design tab on the Ribbon, and then Select Data. Add a new series with the Series Name set to =setup!$R$24 the X values set to =setup!$P$25:$P$125, and the Y values set to =setup!$R$25:$R$125. Click OK.
Click on the Layout tab of the Ribbon and then select Series "2-yd lines" from the combo box in the Current Selection section. Add Error Bars with Standard Error from the Analysis section. Delete the X error bars. Select the Y error bars from the combo box. Click on Format Selection (just below the combo box). Set direction to Both and end style to No Cap. Set Error Amount to Fixed Value and 0.12. Click on Line Color on the left pane and select Solid Line with 35% Transparency. Click on the Color button and set it to absolute White. Click on Line Style on the left and set the Width to 1.25 pt. Click Close. Our field should now have two tiny little lines, one at the far left and one at the far ring, just before the end of the grass.
Let's add the hash marks.
There are four hash mark series, so this procedure will need to be repeated four times.
Click on the chart and then click the Design tab on the Ribbon, and then Select Data. Add a new series with the Series Name set to =setup!$S$24 the X values set to =setup!$P$25:$P$125, and the Y values set to =setup!$S$25:$S$125. Click OK. Click on the Layout tab of the Ribbon and then select Series "Hash 1" from the combo box in the Current Selection section.
Add Error Bars with Standard Error from the Analysis section. Delete the X error bars. Select the Y error bars from the combo box. Click on Format Selection (just below the combo box). Set direction to Plus and end style to No Cap. Set Error Amount to Fixed Value and 0.2. Click on Line Color on the left pane and select Solid Line with 35% Transparency. Click on the Color button and set it to absolute White. Click on Line Style on the left and set the Width to 1.25 pt. Click Close. Our field should now have a row of tiny hash marks very near the bottom of the grass.
Click on the chart and then click the Design tab on the Ribbon, and then Select Data. Add a new series with the Series Name set to =setup!$S$24 the X values set to =setup!$P$25:$P$125, and the Y values set to =setup!$S$25:$S$125. Click OK. Click on the Layout tab of the Ribbon and then select Series "Hash 1" from the combo box in the Current Selection section.
If at this point you see some horizontal colored line segments you will need to set the line color on the Hash 1 series to None. Note that this is the series and not the error bars. In fact, if this happens to any of the series that define our field, the same applies.
To add the next three rows of has marks, the procedure's the same. Rather than repeating all of the instructions, I will list just the settings.
For Hash 2:
Series Name: =setup!$T$24
Series X values: =setup!$P$25:$P$125
Series Y values: =setup!$T$25:$T$125
Y Error Bars Direction: Both
Y Error Bars End Style: No Cap
Error Amount: Fixed Value at 0.11
Line Color: Solid absolute White with 35% Transparency
Line Style Width: 1.25 pt
For Hash 3:
Series Name: =setup!$U$24
Series X values: =setup!$P$25:$P$125
Series Y values: =setup!$U$25:$U$125
Y Error Bars Direction: Both
Y Error Bars End Style: No Cap
Error Amount: Fixed Value at 0.11
Line Color: Solid absolute White with 35% Transparency
Line Style Width: 1.25 pt
For Hash 4:
Series Name: =setup!$V$24
Series X values: =setup!$P$25:$P$125
Series Y values: =setup!$V$25:$V$125
Y Error Bars Direction: Minus
Y Error Bars End Style: No Cap
Error Amount: Fixed Value at 0.17
Line Color: Solid absolute White with 35% Transparency
Line Style Width: 1.25 pt
At this point if you have any colored (other than white) lines on the field, you will need to go back and format those series to have No Line in the Line Color setting.
Another thing that can go wrong is when you add the error bars for a series, if you forget to select the series, Excel will add the error bars to every series and you'll lose the grass. Just undo this and make sure you select only one series before adding error bars.
Now we need to add the two inbound line series. Note that we need the X error bars on these two and not the Y error bars. This is different than all of the previous steps.
For Inbound Lines 1:
Series Name: =setup!$W$24
Series X values: =setup!$P$25:$P$125
Series Y values: =setup!$W$25:$W$125
X Error Bars Direction: Both
X Error Bars End Style: No Cap
Error Amount: Fixed Value at 0.6
Line Color: Solid absolute White with 35% Transparency
Line Style Width: 1.25 pt
For Inbound Lines 2:
Series Name: =setup!$X$24
Series X values: =setup!$P$25:$P$125
Series Y values: =setup!$X$25:$X$125
X Error Bars Direction: Both
X Error Bars End Style: No Cap
Error Amount: Fixed Value at 0.6
Line Color: Solid absolute White with 35% Transparency
Line Style Width: 1.25 pt
Congratulations for hanging in there. It now looks like a football field:
Let's add the end zones.
Each is made from a very fat Y error bar, just like the grass was.
For Left End Zone:
Series Name: =setup!$Z$7
Series X values: =setup!$Y$8
Series Y values: =setup!$Z$8
Y Error Bars Direction: Both
Y Error Bars End Style: No Cap
Error Amount: Percentage at 101%
Line Color: Solid RGB: 162, 4, 47 with 0% Transparency
Line Style Width: 75 pt
For Right End Zone:
Series Name: =setup!$Z$11
Series X values: =setup!$Y$12
Series Y values: =setup!$Z$12
Y Error Bars Direction: Both
Y Error Bars End Style: No Cap
Error Amount: Percentage at 101%
Line Color: Solid RGB: 15, 15, 15 with 0% Transparency
Line Style Width: 75 pt
Here it is:
Notice that the zero yard lines (the boundary between the end zones and the grass) are not actually drawn by a series. They are made by the Chart Area background and are visible because the Plot Area background fill is set to our home team logo, which makes the Plot Area background transparent except for where the logo is - so the Chart Area shines through. Notice also the detail where the zero yard lines are the only yard lines that extend completely from the top of the field to the bottom. All the other yard lines are inset ever so slightly from the top and bottom. This is how a real football field is painted, and the NFL.com Flash app got this wrong!
To finish the field we need to draw the line numbers on the grass and the team names in each end zone. To this point we have been using error bars, but we will use Data Labels for the line numbers. Add a new series:
For Line Numbers:
Series Name: =setup!$Q$7
Series X values: =setup!$R$8:$R$16
Series Y values: =setup!$S$8:$S$16
Set the Line Color to No Line. Right click on one of the highlighted line markers and select Add Data Labels. Right click on one of the new data labels and select Format Data Labels. Set the Label Position to Center and close the dialog. With all of the data labels still selected, click on the Home tab of the Ribbon and set the Font to Times New Roman, 22 pt, Bold. Set the Font color to absolute White.
If we wanted to have the label value come over automatically we would need to create one series for each label and name the series the yard line number. Believe it or not, I want to minimize the number of series in this chart, so we are going to manually name each data label and use just one series to place them all.
You'll notice in Column Q of the setup sheet we have the label text that we want to use. So copy the contents of Q8 from the Formula Bar and paste it into the first line number label on the left of the chart. Notice that the triangle is very large. Another detail of real fields is that this triangle is tiny in comparison to the line number and it is high. We have some formatting to do. Excel allows us to format different parts of the text within the label with different formats.
Click on the label and select just the triangle. Right click on that selection and select Font. Set the triangle font to Size: 11 and Superscript with an Offset of 60% and close the dialog. Now click on the data label again and select the number portion (not the triangle). Right click that selection and select Font again. This time go into the Character Spacing tab and set Spacing to Expanded By 4 pt. Close the dialog. Notice that the number 10 now straddles the 10-yard line and our triangle is high and tiny.
I know this is a pain (I told you this was not an F-11 chart). The above procedure needs to be repeated for each of the 10-yard line numbers. Copy the text values from the Q Column on the setup sheet to each remaining data label and format them appropriately. When you get to the labels where the triangle points to the right, there is one slight modification to the recipe. When you are setting the kerning for the number spacing, just select the first digit, not both digits.
If you stuck it out, you now have this:
A quick note on data labels is in order. Data Labels (and other type of text like Text Boxes) will always appear in a chart above everything else like data points and lines and error bars. We are lucky that the in this case that the design calls for the line numbers to be tucked away at the bottom of the chart, where they will not interfere with our plays by being visually on top of them!
I wanted to use Data Labels for the team names in each end zone, and I did on my first attempt. Excel nicely allows the data labels to be rotated so it was not a problem to get the team names rotated 90 and 270 degrees. But for some arbitrary reason that I don't understand, Excel limits the width of a data label to some unknown percentage of the total width and height of the chart. As a result, my SAINTS data label for their end zone worked perfectly, while I could only get the CARDINALS to work by making the font size small enough so that the length of the word CARDINALS was roughly the length of the word SAINTS. I searched high and low for info on this strange data label behavior and found nothing. If you know more details, please leave me a comment. In the end I decided to use WordArt for a little style.
To add the team names:
Click on the Insert tab of the Ribbon. Click on WordArt in the Text section and select the 3rd style (Fill - White, Drop Shadow). Click in the Formula Bar and type =TeamLeft_Name_Cap. Press ENTER on the keyboard. Click on the WordArt border and switch to the Home tab of the Ribbon. Change the Font to Corbel at 32 pt. Switch to the Format tab and click on Rotate in the Arrange section. Select Rotate Left 90 degrees. Move the WordArt into the red (left) end zone. Click on the edge of it and then carefully resize by dragging each side until in fills the entire end zone, but does not bleed out of it. Switch back to the Home tab and center the text both vertically and horizontally.
Click on the border of the WordArt again and then copy and paste a new copy of the WordArt. Drag this new copy to the other end zone (the black one) and center it precisely. With the new copy selected, click in the Formula Bar and type =TeamRight_Name_Cap. Press ENTER on the keyboard. Rotate this label to the right 90 degrees (twice).
On the Format tab you can use the Align button to make sure they are bot aligned on the top and then select them both and Group them.
Here is the completed football field:
Onto this canvas, we will create our Popcorn Chart in Part 2 of this tutorial. I think one of the main ideas you may want to think about from this tutorial is that with a little ingenuity we can use the Excel charting engine to create all sorts of interesting displays. For our football field we used a number of chart series with their line colors set to No Line. We were in fact not interested in their lines. We used these series as a way to place error bars and data labels. This is a powerful technique that can be used in many situations.
Take a break and come back for Part 2, where the real magic happens...
If you liked this article, please share it!
Here it looks like there is a code error. All i see is a bunch of code in where the article should be. Is this mine or your problem?
Pretty great entry, definitely informative stuff. Never ever imagined I would find the info I want in this article. I have been scouring all around the internet for some time now and was starting to get disappointed. Fortunately, I stumbled onto your internet site and acquired exactly what I had been struggling to find.
Have you ever considered adding more videos to your blog posts to keep the readers more entertained? I mean I just read through the entire article of yours and it was quite good but since I'm more of a visual learner,I found that to be more helpful well let me know how it turns out. Keep up the great works guys I've added you guys to my blogroll. This is a great article thanks for sharing this informative information.. I will visit your blog regularly for some latest post.
Great tutorial! I am still with you and can't wait for part 2. I enjoyed the calendar also. Thank you!
I'm glad you enjoyed them, Matthew, and welcome to my blog!
I hope to get part 2 of the tutorial posted next week.
Right now I'm working on a very interesting chart that I hope to get up this weekend!
Regards,
Daniel Ferry
excelhero.com/blog