Excel Streamgraph

| 5 Comments | 0 TrackBacks

I want to build a Streamgraph in Excel.

I guess I'd call my first attempt a faux-streamgraph. It's certainly interesting looking, but there is a long way to go to get to Lee Byron's inventive visualization.

I'm not actually sure it can be done in Excel. I have some ideas on how to do the wigglies across the x-axis, round the the jagged area plots with intermediary Bezier curving, and better series ordering, but that will have to wait for the next attempt. If I can work it all out, this might be a good candidate for an Addin.

But there are some cool aspects of this first attempt. Moving the mouse around highlights films and displays a visually nice, semi-transparent chart tip for the film's title and the year to date gross. The default Excel chart tip is turned off. Getting this to work properly is inexcusably difficult in Excel. The chart sheet coordinate system and the screen mouse coordinate system are not related and there are a bunch of variables to take into account. But the biggest difficulty (and this is the inexcusable part) is that there is no way to get the LEFT and TOP screen coordinates of a chart in a chart sheet. Without that crucial bit of information, it's impossible to exactly correlate the two coordinate systems to properly have the chart tip track the mouse pointer for example, or to allow for precise drawing on the chart with the mouse.

My hack around this limitation is to accept this shortcoming of Excel and provide a method for the user to calibrate the mouse pointer. When you first open the chart the chart tip will likely not be anywhere near the mouse pointer, but you can move the chart tip left and right with the "A" and "D" keys, and up and down with the "W" and "S" keys on your keyboard.  Once you have it calibrated the way you like, the workbook will remember your settings and the chart tip will track the mouse even when you reopen the workbook the next time. If you adjust your screen resolution or the Excel Zoom setting, you will need to recalibrate. But it's actually kind of fun doing it.

If anyone knows a way to automatically sync the coordinate systems for the mouse and a chart sheet, please let me know. I spent a couple of days researching it and none of the strategies I found worked.

The other interesting aspect of the chart is that it is NOT a STACKED Area chart. For some reason I could not get the stacked version to work properly with the negative and positive values. So I used some formulas on sheet "2" to aggregate the totals and then plot the chart in reverse series order so the smaller values appear in front of the artificially inflated values - a home grown staked chart if you will. There was no science to which series went above the x-axis and which went below. I just alternated every other one.

All in all, I'm quite happy with how the first attempt turned out.

Here's the workbook:

If you liked this article, please share it!

Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/29


I can remember seeing some code a few years ago where some one wrote a macro to allow mouse zooming in on Scatter Graphs by dragging a rectangular area over the chart area, and it contained the code to enable that to happen. It wasn't available in other chart types due to limitations on the Charting Object Model.

I think these charts could be done using scatter graphs, building many layers up 1 at a time and smoothing using lots of dummy points and beziers.

Is it worth the effort, probably not, Except to say I did it.

ps: Love the site

The Guys at XLRotor have a utility
Which allows
• Zoom & Pan With the Mouse
• Versatile Cursor Readout using the Mouse
• Copy/Paste for Points and Axis Scales
• Edit Chart Series Formats for All Series in One or More Charts

have a look half way down the following page

There is also a Bezier Curve routine in another file just below

Thanx Dr. Brian Murphy at XLRotor

Wow, that's really nice!

I have parts of the Addin working ok in 2010
If you download and add the Charttool addin in to Excel
It will add an Addin Toolbar to Excel (enable macros)
One of the Buttons on the toolbar is TurnOnMouseCursor
Select a Scatter Chart
Click the TurnOnMouseCursor
The X & Y coordinates will display in the Status Bar (lower left corner)

Note it only works with Scatter charts.
I have never ran this with 2007, but it runs well with 2003 and 2010.

The excel zoom function is very reasonable to have just a part of your spreadsheet displayed larger. A video about this function can you check out here http://www.excel-aid.com/using-the-excel-zoom-function.html

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on April 2, 2010 8:05 PM.

Excel Optical Illusions #7 was the previous entry in this blog.

California Climate is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.