There are lots of heat maps out there. We saw many different versions during the election cycle---everything from campaign financing to election results. These visualizations use a scale of color values to communicate a range of data. Some vary the intensity of a single color value to represent a variable (e.g., the greater the population in an area, the darker the color). Others use two values to present a comparison (e.g., increases and decreases to population over a given time period).
Excel does not have a heat map option in its charts; however, you can use conditional formatting and some other design options to create a really fine looking visualization.
One of the graphics included with Stephen Few's workshop presentation was this one:
 |
| Market Segment from Stephen Few's Now You See It |
I was really taken with it. It's a simple layout, but packs a punch in terms of showing the data. It was the perfect fit for a data set I was wrangling. Take a look at my version (there is an interactive version at the end of this post):
What you're looking at is some data about the different areas of the arts (dance, music, theatre, and visual arts) for three grade bands (elementary, middle, and high school) over three years (2010, 2011, 2012) for districts in Washington state. Please note that although this data is public, it is only available to those who ask. Therefore, I have made some modifications to what I am sharing here: no districts are identified by name and I am only including ~50 districts.
Never underestimate the power of pencil and paper when planning a visualization. Using the version from the workshop as a model, I sketched the layout for this, as well as how to fill the cells. I decided not to use number of students as a factor, as that would skew things based on the size of the district. Instead, I wanted to look at each arts area as a portion of overall enrollment in the arts. In other words, what percent of elementary students in a district had access to instruction in dance vs. music vs. theatre vs. visual arts?
There is a single color scale. The greater (darker) the intensity, the greater the percentage for that area of a grade band. Blank spaces represent a lack of data. This might be due to no instruction offered in that area, but it could also mean that we just don't have any data from the district. Don't read too much into those.
Even though I used a single color (purple) for this visualization, it is still simple to look at gains and losses over time. For example, for District A, we can see that at the middle school, music instruction has decreased over the last three years...and the percentage of students involved with the visual arts has increased.
Similarly, I can see some big trends when viewing the entire data set at once.
Notice how popular Music is at elementary? There are a lot of dark squares in that second column. While not as strong, there is a definite preference at the high school level for visual arts, based on what I see in the last column.
The benefit of a heat map like this one is that it allows you to visualize a lot of data at once (there are over 1500 data points in the image above, including blank spaces) while not losing any of the points.
In the next post, I'll get into the nitty gritty about how I built this. For something that looks rather elegant (if I do say so myself), there is only one formula involved, with some conditional formatting and shapes applied. In the meantime, if you want to play with the spreadsheet a bit, use the Excel Web App below. Sorry about the horizontal scrolling required, but you can also click the bottom right-hand corner to view the sheet full screen.
No comments:
Post a Comment