Excel Optical Illusions #2

Well, it’s Friday and time for my next optical illusion rendered in Excel.

optical_illusions_egg_shadow.png
Don’t believe it?  Go ahead and download the Excel 2007 workbook.
I finally found something useful for Excel’s faux 3d charts! It’s too bad that MS chose not to allow rotation around the Z axis for these kind of charts…
I’ll have another next Friday!
Again here’s the link to the workbook:
Enhanced by Zemanta

Chess Viewer Update

I was getting a lot of requests from people who do not have Excel 2007 to have a chance to see the Chess Viewer operate.

I converted the 2007 file to an .xls and spent a few minutes updating the graphics and formulas. It now works in previous versions of Excel, but I’ll warn you that the graphics pretty much suck. Excel 2007 is way better at rendering graphics.
The link to the .xls is at the bottom of the original post:
http://www.excelhero.com/blog/2010/02/excel-2007-chess-game-viewer.html

Excel Optical Illusions

Designing visualizations for Excel data keeps a lot of us busy. But the human eye is easily fooled.

I thought it would be interesting to recreate some famous optical illusions in Excel. So far I’ve done about a dozen and thought it would be fun to release them on my blog, one each Friday. So here’s the first one:
optical_illusions_bulge.png
I actually came up with the idea for my Excel 2007 Chess Viewer, while I was crafting this optical illusion. The checkerboard is absolutely square. The bulge is a genuine illusion and is entirely contained inside of an Excel chart. The attached workbook allows the user to remove the small checks and then the illusion vanishes. No VBA.
Enjoy.
Here’s the Excel 2007 workbook.
Here’s a workbook for earlier versions of Excel.
— UPDATE —
One of my readers, Gerald Higgins, sent me a version of the bulge that he crafted after seeing mine. He used conditional formatting as an alternative method to the chart to produce the image. Considering the limitations of 3 conditional formats in earlier versions of Excel, his effort is remarkable. I particularly like his strategy for getting the ticks to line up properly. Excellent job, Gerald!
Have a look at his: .xls
Here is a list of other Excel Optical Illusions here at Excel Hero:

– The Haze Illusion

Enhanced by Zemanta

Excel 2007 Chess Game Viewer

It’s now half time for our NFL Drive Chart tutorial and so I thought I’d share another Popcorn Chart with you.

This one is a Chess Game Viewer and it’s a lot easier to construct:
the_immortal_game_excelhero.com.gif
The image above is a screen shot of the Excel 2007 Chess Game Viewer. The workbook is another implementation of the Popcorn Chart as series pop off the screen as chess pieces are captured.


If you’ve been reading my blog you know that I try to avoid IF() functions whenever possible. See I Heart IF for details. Previously I shared about using boolean expressions as one method to eliminate many IF() functions. The way this is usually done works well with numeric formula results. The Chess Game Viewer demonstrates a technique to do the same with formulas that result in text output. The key is the REPT function, which does not error out with zero repetitions.
The project demonstrates conditional chart formatting without VBA and using images as marker fill.
It showcases the advantage of using a state machine as input to the dynamic chart. This gives us the ability to view a game forward and backwards.
The Chess Game Viewer uses one compound chart comprised of a Stacked Column chart type for the chessboard and an XY (Scatter) chart type for the chess pieces.
The chart sheet is protected but there is no password. So to dig into the working details, just Unprotect the sheet.
You can find the controls to advance the game and to change the chessboard style at the bottom right corner of the chessboard.
I’d love to hear your feedback.
Here’s the Excel 2007 workbook.
— UPDATE —
Okay, I’ve been getting a lot of requests to provide a copy of the chess viewer that will work in previous versions of Excel. Today, I made one, but it looks terrible. Excel 2007’s graphics capability are far superior. At any rate, at least it works. Here it is…
Enhanced by Zemanta

NFL Drive Chart Tutorial – Part 1

This tutorial shows how to construct my NFL Drive Chart that was introduced in a prior post.

In part one we will create the playing field. Please download the attached tutorial workbook. It contains all of the game data and support formulas. A later tutorial segment will go over the detail of shaping the data with the support formulas to support our Popcorn Chart, but it’s more interesting to dive into the construction of the chart itself.

— 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:
drive_chart_field_complete.png
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:
SaintsLogoForBackground.png
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:
drive_chart_grass.png
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:
drive_chart_grass_with_yardlines.png
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:
drive_chart_grass_with_yardlines_hashmarks.png
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:
drive_chart_grass_with_yardlines_endzones.png
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:
drive_chart_grass_with_yardlines_linenumbers.png
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:
drive_chart_field_complete.png
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…

Excel 2007 NFL Drive Chart

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:
NFL_Drive_Chart.png
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!
Here’s the tutorial.
Reblog this post [with Zemanta]