Category Archives: Chart

Interactive Advanced Excel Chart

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

html5_full.png
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.
Enhanced by Zemanta

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.
pinkdots.gif
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.
Reblog this post [with Zemanta]

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:
 – solarsong
 – 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)
 – Curry’s Paradox (Optical Illusion)
 – The Haze Illusion (Optical Illusion)

 – The Koffka Ring (Optical Illusion)
Enhanced by Zemanta

Excel Optical Illusions #13

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

almonds.png
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 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:
 – solarsong
 – 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)
 – Curry’s Paradox (Optical Illusion)
 – The Haze Illusion (Optical Illusion)

 – The Koffka Ring (Optical Illusion)
Enhanced by Zemanta

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.
combined.gif
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.
You can read the full article here.
And download the latest workbook here.

Excel Optical Illusions #11

This week’s Excel Optical Illusion is quite different. It’s a Stereokinetic illusion.

stereokinetic.gif
How’s that for an advanced Excel chart?
The depth of field is totally illusory. The actual chart contains a check box to turn the chart animation on and off.
This is a bubble chart with a tiny bit of VBA thrown in, just for the animation. Here is the VBA in it’s entirety:
Public Sub Animate_Figure()

    Dim ws As Worksheet
    
    Set ws = ActiveWorkbook.Sheets("1")
    With ws
        Do
            If Not .[Animate] Then GoTo exit_here
            If .[i] = 1 Then .[i] = .[n] + 1
            .[i] = .[i] - 1
            DoEvents: DoEvents
        Loop
    End With
    
exit_here:
    Set ws = Nothing

End Sub
The actual chart is smoother than the animated GIF displayed here. You can adjust the speed of the animation by changing the number of points in an orbit on the chart worksheet.
It looks much better in Excel 2007 and above, but works for all versions. Let me know what you think of this…
Like most of my Optical Illusions, the inspiration comes from Michael Bach’s fantastic site.
More to come. Here’s the chart:
Reblog this post [with Zemanta]

Excel Optical Illusions #10

This week’s Excel optical illusion is called Shaded Diamonds.

optical_illusion_10.png
Which row of diamonds is the darkest? Which is the lightest?
Are you sure?
Would you believe they are all the same?
This advanced Excel chart is interactive and has a scroll control that spins a diamond revealing the truth. I think this is one of the more interesting charts that I’ve shared. Can you figure out how it’s done?
Investigate here:
Reblog this post [with Zemanta]

Excel Location Mapping

I often need to do geospatial location mapping in my Excel projects. If you can acquire the latitude and longitude coordinates, it’s pretty easy.

excelhero_walmart_growth.gif


You’ll need an equidistant map like this one, in order to plot the coordinates easily. If anyone knows the mathematics needed to transpose coordinates so they can be plotted on the more common map projections where the border between the USA and Canada is curved, please let me know! I have not figured that out yet.
When using an equidistant map, all you need to do is plot the coordinates on an XY (Scatter) chart. Of course you will need to set the horizontal and vertical axes min and max so that the points are drawn close to accurate. This is easy when your data includes a lot of coastal points (just adjust the axes until those points are barely on land). The included map is calibrated quite well.
For this post I chose to imitate Nathan’s outstanding Walmart growth movie. While Excel is not up to the task of the Flash animation in his movie, the results are not half bad. In my version you can step through year by year manually with a scroll control, or you can click the Animate button and watch the movie.
I got the data from here. They did not include the lattitude and longitude coordinates so I ran the CSV through batchgeo.com and in a few minutes I had coordinates for 99% of the locations. The rest I acquired manually from Yahoo Maps. Unfortunately, the data source only lists openings through 2006; it would be nice to have the complete and up to date dataset…
The map image is set as the background to the chart’s Plot Area. From this project I learned that background images on charts while useful really slow down the charting engine. To demonstrate I’ve included a check box to toggle the chart background. The charting is significantly faster with it off.
Here’s the chart:
Want to Learn How I Do It?
If you are interested in learning the incredible Excel techniques that I showcase on this blog, you should enroll in the Excel Hero Academy.  There is no other course available anywhere that will teach you how to leverage Excel’s power like my course does.  As an alumni of the Excel Hero Academy, you will be massively more productive when working with data.

Excel Optical Illusions #8

Here’s another take on the Eye Jitter illusion.

optical_illusion_8.png
Does the central large square with the little squares inside float and dance above the background of circles?
Sometime shaking your screen a little enhances the illusion. Seriously!
This was fairly straightforward from the Excel charting perspective. The large square is constructed from error bars. The little squares and the circles are markers on an XY (Scatter) chart. This week’s file is an .xls so it should work for everyone, but for some reason the circles look terrible in versions prior to Excel 2007, and the illusion suffers as a result.
I’ll have another next week – and it’s completely different than all the previous illusions…
Here’s the workbook:
— UPDATE —
After Lori’s fine work I decided to upload a new workbook that shows the named formula approach alluded to in the discussion below. The workbook contains two charts. The first uses Lori’s formulas. The second chart uses my suggestions. Neither is more correct, but it should be instructive looking at both. To do so, you will need to use the Name Manager. The charts are not formatted, so the optical illusion is not there. The Named Formula discussion is simply about preparing the data for the chart, and not the formatting that produces the illusion. Here’s the workbook: