Excel Heatmap Visualization

We're working on a large-scale project with a client who must remain nameless.  Our main contact had a high-level presentation; they wanted to make a lot of complex relationships clear, more-or-less instantly.  

For client reasons, "heatmap" was the visualization of choice. (Don't get me wrong - I understand the appeal of heatmap-style visuals.  I'm particularly fond of the iPad/iPhone app Stocktouch because it makes such elegant use of heatmap visualization.  The whole stock market, at a glance, on a single iPhone screen.  Wow.)  But markets are, by definition, a bunch of like measurements of unlike things.  "Like Measurements" is the key concept there - if AAPL is more red than GOOG, it's "as measured on the same scale."

OK, that's the fun & challenge of helping clients.  You wanna heatmap, we gotta heatmap inna back.  We knew that the data was (cough) in flux, so that ruled out hand-rolling color scales in Illustrator or InDesign.  The magnitude of cross-comparisons probably would have ruled those out anyway - 10 groups, 30 different measures, 3 different comparison-range variable PER MEASURE.   (You want to try hand-calibrating 9000 colored blocks against each other, knock yourself out. Not me.)

So the tool we turned to: big data geeks may sniff at this, but i'll meet 'em at the OK Corral any day with Excel in my holster.  It wasn't a perfect solution, but the Conditional Formatting tool in Excel does include color scales, and we were able to engineer all of the visuals directly in Excel. Here's a snapshot - variables and data changed, of course. 


There are actually 27 color scales here - one per row.  Unlike the stock market, each row had different units of measure and a different basis for comparison; high wasn't always Tempus.  Where Excel cooked on this heatmap problem was in enabling all of the calibrations and comparisons, and letting us change formulas/relationships quickly.

The color scale conditional formatting isn't a perfect tool by any stretch.  Really fundamental Excel tools can be manipulated with & against each other - range references, functions, that kind of stuff. The color scale conditionals are more-or-less 'hard coded' on each row - for example, the LOW and HIGH references are absolute cell references.  (Meaning, move the scale and the whole thing breaks...)  Plus - and I know this is a nit - the only way to manipulate the conditional formatting is to click through the stupid dialog boxes.  

All that said - if you want to take a heatmap view of something out for a run, it's not a bad way to go. Yeah, Processing or R could probably do it better, but deadlines and data-changes make coding solutions difficult.  If the format gets fixed/frozen, we'll give that a shot. 

If you want the actual Excel sheet...enjoy!


Another Excel heat map post here. 

P.S.  Great post on heatmaps and color blindness from our favorite technical partner, Wistia.