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: