How to Create a Heatmap in Excel

by on 3rd of February, 2009 in Lab Safety

Most available heatmap programs I’ve encountered cost too much, come bundled in a huge application, don’t do what I want, or don’t offer enough flexibility.

In the previous article on heatmaps, I showed how you can use ASAP utilities to sort color-coded cells using this useful Excel Add-in.

What I didn’t tell you is how to create the heatmap in the first
place. So here I’ll show you how you can easily build a color heatmap within an Excel spreadsheet.

Here’s how to do it.

Set up the macro

(1) Copy my the entire text of my handy heatmap macro from this page.
(1) Open Excel
(2) Click on Tools>Macro>Visual Basic Editor (VBE)
(3) In the VBE, double-click on Sheet1 in the upper left corner,
(4) Click here get my handy Excel heatmap macro. Make sure you take all of the text.
(5)Paste the macro text into the code window and close that window by clicking the [X] in the upper right corner.
(6) Close the VBE the same manner

To use the macro

(1) Click on Tools>Macro>Macros (make sure that macros are enabled!)
(2) In the Macro window, you’ll now see the newly created heatmap macro listed
(3) Select it from the list and click the Run box
(4) When you see “Select a range of cells”, mouse over to select the range of cells you want to colorize, then
(5) Click the OK button to color your world.

To change the colors displayed in the heatmap

Go to this webpage (http://www.mvps.org/dmcritchie/excel/colors.htm), find the Excel color codes you want, and edit the macro accordingly.

To test the macro

Make a series of number from 1 to 8 in adjacent cells cells and try to color code them. If you find the number range doesn’t suit your particular purpose, simply edit the macro to change the range of values to be colored.

Good Luck! …and let us know if you have any other macros on hand in your toolbox…

15 thoughts on “How to Create a Heatmap in Excel”

  1. Ian says:

    thanks for this Paul. I’ve used spotfire for this in the past (when I worked for a big company that could afford it). I’ve made some pretty lame heatmaps in excel before – this is way better
    Ian

  2. Michelle says:

    You can also create heatmaps in R (which is free).

  3. Augustine says:

    There is also a handy website called called matrix2png located at the following address: http://www.bioinformatics.ubc.ca/matrix2png/

    This takes a labeled tab delimited excel-type data matrix and allows you to set all sorts of options to get you a pretty nice looking heat-map. Its worth a try!

  4. MrAnon says:

    Excel 2007 also has the ‘Conditional Formatting’ tab on the ‘Home’ ribbon. It can achieve some aesthetically pleasing results and is also customizable.

  5. Array10 says:

    Hey….thanks!!I would have probably wasted hours trying to figure this out…you have saved me alot of time. Now I only hope this will make my data look better :o)
    Peace…and Happy New Year!!

  6. Ming says:

    thanks for the tools.

    i have a question, how to change the macro if i want to separate values (0.5 / 1/ 1.5 / 2 / 2.5 . . .. )?

    thanks

  7. Paul N. Hengen says:

    Ming,

    I think it might be easier for you to use conditional formatting in Microsoft Office Excel 2007, which wasn’t available to me when I wrote the macro. If you still want to use the macro, I think all you need to do is choose a maximum value and then change the values 8,7,6 etc. to 8.5,8.0,7.5 etc. If it doesn’t work for you, please let me know what you mean by “separate values”.

    -Paul.

  8. Ming says:

    Hi Paul,

    The value of my data range from 0 to 4 in which i would like to separate them every “0.5″. I am using window vista and i don’t know how to change it.

    Thanks,
    Ming

  9. Paul N. Hengen says:

    Ming,

    Enable macros within MS Excel.
    Create a new macro or edit and existing macro.
    Use this as the macro text:

    Sub heatmap_for_Ming()
    Dim myrange As Range
    Dim colchoice As Integer
    Set myrange = Application.InputBox(“Select a range of cells”, rangetocheck, , , , , , 8)
    For Each cell In myrange
    Select Case cell.Value
    Case Is >= 4
    colchoice = 1
    fontchoice = 2
    Case Is >= 3.5
    colchoice = 3
    Case Is >= 3
    colchoice = 45
    Case Is >= 2.5
    colchoice = 6
    Case Is >= 2
    colchoice = 19
    Case Is >= 1.5
    colchoice = 20
    Case Is >= 1
    colchoice = 8
    Case Is >= 0.5
    colchoice = 41
    Case Is < 0
    colchoice = 5
    fontchoice = 2
    End Select
    cell.Interior.ColorIndex = colchoice
    cell.Font.ColorIndex = fontchoice
    fontchoice = 1
    Next
    End Sub

    In MS Office Excel 2007, make sure to save your file as “macro-enabled”.

    -Paul.

  10. Willie Brickey says:

    I have a range of -10 to +10 profiling values. I’ve successfully identified and changed colors for subsets of expression values > 0. Can subsets less than 0 be identified and colored (i.e. set A with normalized ratio values <-2; set B with values <-4; set C with values less than -6; set D with values less than 0 but greater than -2; etc)?
    Thanks.

  11. Willie,

    Absolutely. Just edit the “Case Is” part of the macro and give it a try.

  12. Avatar of Micro Micro says:

    Paul,

    I tried to use Excel 2007 “Conditional Formatting” to generate 3-color scale heat map. The problem is how to change the color of the numbers so they will “merge” into the cells to give a real heat map instead of a colored map with numbers inside each cell. I guess the function I need is to do “conditional formatting” on font, so the color will go along with the color of the cell. But cannot find out how to do it in Excel 2007. Any advice?

  13. Avatar of Micro Micro says:

    Paul,

    I tried to use Excel 2007 “Conditional Formatting” to generate 3-color scale heat map. The problem is how to change the color of the numbers so they will “merge” into the cells to give a real heat map instead of a colored map with numbers inside each cell. I guess the function I need is to do “conditional formatting” on font, so the color will go along with the color of the cell. But cannot find out how to do it in Excel 2007. Any advice?

  14. Avatar of djr677 djr677 says:

    Thanks Paul, this is very helpful! I will beusing this soon to show my new acetylscan (acetylation) data at a symposium soon. You have made me a very happy scientist.

Leave a Reply