Building a Better Heat Map

In the last post, I shared my version of a heat map created in Excel. Now it's time to get into the details of the build.

After you've organized your data in Excel, start a new sheet for the heat map. When I did a "back of the napkin" sketch for this visualization, I knew I would need 37 columns: one for the name of the district, and 36 others to fit three years of data points for four areas (dance, music, theatre, visual) for three grade bands. I labeled my rows and columns.

Next, I created a formula to fill in the data points. Because the values for each cell were a proportion of the grade band, they were all a variation of this one for cell C5, the first one for the District A: =2010!C2/SUM(2010!C2:F2), where 2010! represents the sheet with the data for the year 2010, C2 the cell with the drama number (if any) for elementary, and C2:F2 representing the four cells of elementary data. That's it. Use the fill tool for the rest of the cells.

If you're wondering why I have each year on a separate sheet---there's no good reason, other than it was handed to me that way. I could have easily pasted all the data into one spreadsheet. Do as the Excel spirit moves you.

Let's talk about color for a moment, before we dive into conditional formatting. If you need a two-value system (e.g. to represent gains/losses), remember to stay away from green/red. It's not friendly for those with colour vision issues. Orange and blue is better. For a single value system, blue works well. Why on earth did I pick purple, then? Aesthetic reasons. Blue worked fine, but seemed a bit boring. The purple just seems to suit this data set. If you need help with colour scales, you might start with the Color Brewer.

Also, why do I have ten categories? It seemed like a natural fit for a scale based on percentages---I stuck with the base 10 scheme. My full data set for this piece has over 9000 data points and I wanted to see some nuance. But I could just have easily picked quartiles or another factor. Which one should you choose? Ah, that's part of the art of data science. You really need to play with your visualization a bit to find out what cut values work best.

Okay, let's dive in to the conditional formatting. You're going to need 11 rules. Why 11? Because we also want to create one for any 0 or blank value.

For each rule, tell Excel to do with a range of values---your instructions should include the color for that part of the scale as both the font and fill. This way, you will "hide" the number in the box. Go ahead and put a bunch of 9s at the end of the upper limit. If you have a big data set (and who doesn't like 'em big?), you're going to want to account for all the little variations within it.

Your last formatting job has to do with the borders. In the menu for borders, make each cell have a thick white outline. This will make your coloured boxes look separated. Go into the View tab and uncheck the box next to "gridlines."

The invisible border dialogue

Okay, now you need to change the width of the columns and rows to create squares. Just do the old select and slide until everything looks square.

Finally, use the shape tools to add some very thin grey lines between each chunk of the data set. Yes, you're actually going to draw in those borders.

I made mine a little longer between the grade levels than between the different arts areas.

Guess what? You're done.

One of the things I like about this project is that it's simple, but rich and interesting. The more time you spend, the more you notice the little nuances...things you might never see from just the numbers alone. I think this project also illustrates how much of design is not about the number-crunching. There's just one formula here. The rest is making Excel turn that data into something visual. And yes, that takes some time and finesse (it is Excel, after all...not a tool that was developed for data design), but you can do it.

So get out there and make something that grabs people's attention because it's pretty. And when you've done that, come back and share it here.