# Excel Optical Illusions #15

This week I’m happy to showcase another fine Excel Optical Illusion put together by Hui. It’s called Counter-Intuitive Illusory Contours.

There are no lines connecting the smaller dots, but there sure seems to be, like a wave, or a mirage. I love it.
The animation is straight forward and the chart is just an XY (Scatter) type.
Great job again, Hui, and thanks for contributing to the community.
Here’s the chart.
Here’s a list of other animated charts on Excel Hero:
– Excel, A Presentation Platform (Number Spiral)
– Lilac Chaser (Optical Illusion)
– Stereokinetic (Optical Illusion)
– Illusory Contours (Optical Illusion)
– Breathing Square (Optical Illusion)
– Enigma (Optical Illusion)

Recently I’ve shared several Excel animated charts that have been well received. These have been largely entertainment focused applications. Today I will show you how to apply animation to normal business charts.

This animated GIF shows the business chart in question which is available for download at the end of this post. Last week one of Excel Hero’s readers, Tom Quist, sent me a spreadsheet where he had made an outstanding first effort at the animation of a typical column chart. He wanted to animate between several records – let’s call them divisions.

His technique worked, but was a little slow. So I showed him how I would do it and I believe he was enthusiastic with the results.

There are some points to note. If you add a shadow to the chart or (God forbid) make the series 3D, the animation will slow down significantly. Ditto for a background image to either the Plot Area or the Chart Area. The animation speed is heavily dependent on the number of data points in the chart. After a few dozen points, animation becomes dicey in Excel 2007. Making the series partially transparent will also slow it down, but only slightly (maybe not even perceptibly given the number of points in the chart). In some circumstances the transparency effect may be worth the tiny penalty.

The technique that I shared with Tom that day was to do a straight linear interpolation between the point values of one division to the next. This works well. I shared with him that it was also possible to implement EASING so the animation has a soft start and end. I did not have time to give him an example, but I hinted at how to do it. The animated business chart that I’m sharing with you today includes the option for easing, and you can see it demonstrated in the GIF loop above.

The example also moves the calculations for the animation off of the sheet and into named formulas. To me this makes sense as there is no reason to clutter the sheet with the calculations required to morph one division into another.

The chart allows you to switch between four divisions, change chart type, enable animation, and enable easing. You can also set the number of discreet steps to include in the morphing transition between divisions, which will be useful if you run the workbook on older versions of Excel. The older charting engine is so much quicker than Excel 2007’s that you will want to increase the number of animation steps just to see it!

I’ve heard that Excel 2010 is supposed to improve charting speed. I’d love to hear how this performs on that platform.

This workbook demonstrates interactive dynamic charting, animated transitions including easing, and charting from named formulas. VBA is used only to loop through the number of animation steps that you specify, and to change the chart type.
An animated transition can add a certain amount of flare to your dashboard, but be careful not to overdo it. If used sparingly it can add a Flash-like level of interest, but remember that in and of itself, it does not add to the information the dashboard conveys.
I recommend studying the named formulas (CTRL-F3) and the VBA (ALT-F11) in the workbook. Nothing is protected and there are no hidden sheets. The knowledge gained can be used for much more than animation.
And here’s a list of other animated charts on Excel Hero:
– Excel, A Presentation Platform (Number Spiral)
– Lilac Chaser (Optical Illusion)
– Stereokinetic (Optical Illusion)
– Illusory Contours (Optical Illusion)
– Breathing Square (Optical Illusion)
– Enigma (Optical Illusion)
– Two Sinusoids (Optical Illusion)
– Perpetual Collisions (Optical Illusion)
– Freezing Rotation (Optical Illusion)
– Reverse Spoke Illusion (Optical Illusion)
– Stepping Feet Radial Illusion (Optical Illusion)
– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

– Kaleidoscope Motion (Optical Illusion)
– The Haze Illusion (Optical Illusion)
– The Koffka Ring (Optical Illusion) (Bottom of post)

To be honest, I have no idea what to call this kind of chart.

I was inspired by this useful HTML5 readiness tool. As you move the mouse over the rays the index ray follows the mouse pointer so you know which category is which. It’s pretty cool.

Crafting this project I learned that there are some chart properties that you can access from the Excel 2007 Format Data Series dialog box that cannot be addressed from VBA; and vice versa.
For lines, you can set the Cap Type under Line Style in the regular interface. You have three choices: Square, Round, and Flat. Under Joint Type, you also have three choices: Round, Bevel, and Miter. Nowhere in the VBA object model can these properties be set. I needed to set the index ray to a Cap Type of Flat in VBA and it cannot be done – so it’s round. You would think that you could set it beforehand from the interface – and of course you can – but changing the line color from VBA also changes the Cap Type!
While hunting through the expansive forest of the VBA object model for 2007 charts, I found a couple of interesting objects that are not addressable from dialog boxes – and so 99.99% of Excel 2007 users are likely unaware of them. For chart lines (and some other items) you can set them to glow. You can set the color and the radius of the glow. You can also specify SoftEdges, and I used this on the background darkish ring in this chart. Again, these two interesting settings can only be addressed from VBA.
I don’t have Excel 2010 yet. I hope this kind of strange oversight is rectified in the new version.
This chart uses a modified version of Jon Peltier’s chart events class, and just like my Eurovision 2009 chart, the interactivity and animation only works when the chart is selected. When you first open the workbook the chart will be selected. But if you click on the worksheet instead of the chart, you will need to click the chart again to reengage the interactivity. Be sure to change the years by clicking on them at the top.
Here’s the chart.

# Excel Optical Illusions #14

This week’s Excel optical illusion is called, Lilac Chaser. It is generously brought to us by one of Excel Hero’s Australian readers, Hui.
If you stare at the black cross in the center and keep your focus there a very curious illusion transpires. At first you’ll notice that where a pink dot disappears a green dot appears. If you keep staring at the black cross, all of the pink dots disappear!
I think Hui did an outstanding job. This is a Radar chart with a little VBA thrown in to animate the dots, and it is well worth looking under the hood to see how it’s done.
Michael Bach has a similar illusion in Flash and it is of course outstanding, but ours is in Excel, dammit.
Great job, Hui and thank you for contributing to Excel Hero’s growing community!
The workbook is an .xls so it should work in all versions of Excel, but the newer versions render the gradients better and the illusion is stronger as a result.
Here’s the chart.

# Excel Animated Chart #2

In my humble opinion this is one of the most interesting advanced Excel charting projects I’ve ever done. I call it celtic muse, as it stirs my Scottish soul.
Click this image for a Flash Movie recorded from celtic muse.

If you enjoyed solarsong, I think you’ll be fascinated by this. It uses the Windows DirectShow API to play a haunting celtic strain. The show is very much enhanced by the soundtrack so please be sure to download the workbook!

My inspiration for celtic muse  was the “1,000 Blocks” sculpture by Curtis Steiner of the Seattle Art Museum. Using 1,000 identical walnut blocks he has created an almost unbelievable number of truly inspired tiled images. Each image uses all 1,000 blocks arranged in a grid 20 tall by 50 wide. Each block looks like this:

I thought it would be interesting to recreate his Flash presentation using Excel 2007. I’m calling this type of chart an Excel Tile Chart as each element of the image is one of the six faces of the above block. This technique could be very useful for displaying mathematical tessellations.
The advanced chart is quite a mashup of technologies. It stitches together a Flash movie (for the spinning sample block), the DirectShow API for playing the MP3, some other Windows APIs for extracting the embedded MP3 OLEObject Package from the workbook so it can be played, an advanced Javascript timer running in a web browser control to direct the timing of the slide show and to keep the music and the slides in sync after a pause/resume cycle, a fair portion of VBA to stitch it all together, and a demonstration of how to use the INDEX worksheet function to enable dynamic charting.
The chart itself is an XY (Scatter) type.
The presentation includes 78 images, which means 78,000 tiles had to be encoded to work with the chart. I’d like to thank my wife and my 10-year old daughter for helping with that task!
I’d also like to thank Robert Mundigl from Clearly and Simply. Robert was kind enough to review the celtic muse workbook prior to publishing and offered some excellent suggestions for improving it that I had not thought of. Thank you very much Robert.
Since this project makes heavy use of various Windows APIs I’m confident it will only work on Windows based computers. The workbook itself is 6.8 MB, almost all of which is the ebedded binary data for the beautiful MP3 soundtrack. I promise it’s well worth the download.
Here is the file.
— UPDATE —
It seems that the Flash integration in this project may not work for some, especially if you have a 64 bit version of Windows. Adobe is working on a version of Flash for 64 bits, but it is not ready yet. So, here is a version of celtic muse with all traces of Flash removed. Just in case you were wondering, Flash was only used to display that spinning cube that you can also see on this blog post (if you are running a 32 bit browser!). And that only happened whey you clicked on the question mark button. Here’s the Flashless version:

And here’s a list of other animated charts on Excel Hero:
– Excel, A Presentation Platform (Number Spiral)
– Lilac Chaser (Optical Illusion)
– Stereokinetic (Optical Illusion)
– Illusory Contours (Optical Illusion)
– Breathing Square (Optical Illusion)
– Enigma (Optical Illusion)
– Two Sinusoids (Optical Illusion)
– Perpetual Collisions (Optical Illusion)
– Freezing Rotation (Optical Illusion)
– Reverse Spoke Illusion (Optical Illusion)
– Stepping Feet Radial Illusion (Optical Illusion)
– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

– Kaleidoscope Motion (Optical Illusion)
– The Haze Illusion (Optical Illusion)

– The Koffka Ring (Optical Illusion)

# Excel Optical Illusions #13

I call this week’s Excel optical illusion, Swirling Almonds.

This advanced Excel chart is highly interactive. In addition to all the illusory movement, there’s a button that animates the nuts in actuality. It’s quite a show. You can also remove or include different groups of nuts to investigate what happens to the illusion. Surprisingly, the effect is even stronger for me with less nuts.
The workbook should behave in all versions of Excel.
Here it is:

# Excel Optical Illusions #12

This week’s Excel optical illusion is called the Snake Illusion:

The illusion of expanding movement works best for me when I’m not looking at it directly, and when my eyes are moving, for example when I read the menus at the top of this window.
As in the Hering Illusion a few weeks ago, this is not a chart. The illusion is constructed from inserted star shapes on a workbook. But when it’s at its full size in the workbook, this illusion is particularly strong, at least for me.
Here’s the workbook:

# Excel Animated Chart

Many of us create Excel data visualizations everyday. But have you ever made an audiolization? Luke Twyman of whitevinyldesign.com has made a very intriguing creation in Flash.

Here’s my attempt at emulating his fine work in Excel:

I call my version solarsong. Click the image above to watch a Flash video of the Excel 2007 chart in action. Remember to turn your speakers on!

solarsong uses the orbital frequencies of the eight planets to spin a haunting melody.
This is an advanced Excel chart with a number of interesting points, including animation and MIDI sound. True to form the chart is rendered from named formulas and a tad of VBA is used primarily to advance the animation, but in this case it is also used to play the MIDI sounds.
You can adjust the tempo of the song, and you can pause it. When you click play after a pause, solarsong will choose a different MIDI instrument. The combination of different instruments and different speeds can result in a surprising number of varied songs, yet all retain the constant beat of our sun.
Just click on play.
Give it a spin and let me know what you think!
Here it is.
And here’s a list of other animated charts on Excel Hero:
– Excel, A Presentation Platform (Number Spiral)
– Lilac Chaser (Optical Illusion)
– Stereokinetic (Optical Illusion)
– Illusory Contours (Optical Illusion)
– Breathing Square (Optical Illusion)
– Enigma (Optical Illusion)
– Two Sinusoids (Optical Illusion)
– Perpetual Collisions (Optical Illusion)
– Freezing Rotation (Optical Illusion)
– Reverse Spoke Illusion (Optical Illusion)
– Stepping Feet Radial Illusion (Optical Illusion)
– Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

– Kaleidoscope Motion (Optical Illusion)
– The Haze Illusion (Optical Illusion)

– The Koffka Ring (Optical Illusion)

# A Real-time Advanced Excel Chart

I’ve received quite a few PMs regarding the Multi-threaded VBA simulation. One person could not run the swarm due to a policy setting at work. I’d really love to know if anyone else has issues. It seems to be working for everyone else.

In case you are having difficulty I made these animated GIFs from screen recordings of the program running each of the three methods on my computer. All three produce the same data scraping results. The swarm is just way faster. And remember these results are from scraping a terribly slow website. Faster websites produce much more stunning results.
In case you haven’t downloaded the Multi-threaded VBA simulation workbook yet, an explanation is in order. The gray bar represents the number of records needed to be scraped. The blue bar represents the records that have been requested. The green area represents records returned. The red line represents the speed or rate in terms of records per second returned, which is what the vertical axis scale shows.
I’d like to know if anyone gets over 2.00 records per second from the incredibly slow subject website.