Monday, March 21, 2016

How to create a heat map (Google Spreadsheets)

I received some great comments via email from several of you about my last post, describing how to create a heat map using LibreOffice Calc. Thanks for the feedback! It's nice to know my blog posts are helpful.

A few asked how to do this in other spreadsheets. All modern spreadsheets share basically the same features. The key to creating a heat map in a spreadsheet is to use conditional formatting. Every modern spreadsheet should support that.

So I thought I'd also share the same steps to create a heat map using Google Spreadsheets. While I am an open source software advocate, I also recognize that not everyone uses open source software. And since my blog is hosted on Blogspot, which is owned by Google, it's a safe bet that I probably also use Gmail and the other Google Apps. Many of you also have gmail.com email addresses, so you have already access to Google Apps, which includes Google Spreadsheets.

Google Apps can be a great platform if you need to collaborate with others who are far away. You can edit the same document or spreadsheet at the same time, all without having to email files to each other.

Here's a demonstration of how to create a heat map using Google Spreadsheets, using data from a first contribution to usability testing (used here with permission):
First, enter your usability scenario tasks in the spreadsheet. I like to add some whitespace before my results; this will become obvious at the end when we set the borders to white.

Write a brief summary of the usability scenario task on each row. If your usability test involves different groupings of scenario tasks (in this example, different programs) then type those in a separate column. These will be used as "headings" in a later step:


When entering your usability test results, use "G" for green, "Y" for yellow, and so on:


Every modern spreadsheet supports conditional formatting. In Google Spreadsheets, just highlight the data cells you want to format, then select FormatConditional formatting… and set your formatting:


For a heat map, define any cells with a value equal to "G" to have a green background, and so on for the other colors:


The spreadsheet will automatically apply consistent formatting to every data cell you highlighted. Note that you can also set the text color to be the same as the background color, which will effectively "hide" the data text. But in my example I have left the text color as the default so you can easily see how the spreadsheet applies the conditional formatting:


Now you just need to do a bit of manual formatting to make everything look nice. I like to adjust the columns and rows so the data cells are square with centered text. Also set the vertical alignment for everything to "Middle":


Highlight the heat map and a few rows and columns around it, then set the borders to solid white. This effectively erases the grid lines, but leaves nice-looking white lines between each data cell so you can easily follow columns and rows:


Finally, merge the heading cells with empty cells to the right, then set a grey background. You can take a screenshot of the final heat map and insert it into whatever summary you are writing about your results:

images: mine (data from first contribution to usability testing)

No comments:

Post a Comment