Quantcast

How to Create a Heatmap in Excel

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…

17 Comments

  1. Melisa on January 23, 2017 at 3:44 pm

    Thank you, this was so useful! (And a note, in Excel 2013, you need to use the Developer tab to find the VB and Macros tools . You may have to change your Excel preferences in order to be able to view said tab.)

  2. Edwin Q on July 7, 2016 at 6:35 pm

    Thank you so much. Finally an internet VB tutorial that works. So much useless stuff on the internet. You actually delivered on what you titled. Thank you again. I´ll remember this site.

    – Fast
    – Simple
    – Useful

    10 rating

  3. djr677 on February 11, 2013 at 12:33 am

    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.

  4. Ming Her on September 29, 2010 at 7:55 pm

    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?

  5. Ming Her on September 29, 2010 at 7:55 pm

    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?

  6. Paul N. Hengen on April 14, 2010 at 5:12 pm

    Willie,

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

  7. Willie Brickey on April 12, 2010 at 7:05 pm

    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.

  8. Paul N. Hengen on March 2, 2010 at 11:45 pm

    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.

  9. Ming on March 2, 2010 at 2:03 am

    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

  10. Paul N. Hengen on March 2, 2010 at 12:56 am

    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.

  11. Ming on March 1, 2010 at 11:05 pm

    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

  12. Array10 on January 11, 2010 at 7:47 am

    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!!

  13. Brijesh on April 28, 2009 at 9:02 am

    Gr8!! Thank you…

  14. MrAnon on February 24, 2009 at 4:45 pm

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

  15. Augustine on February 14, 2009 at 8:06 pm

    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!

  16. Michelle on February 3, 2009 at 2:00 pm

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

  17. Ian on February 3, 2009 at 10:08 am

    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

Leave a Comment





Share3
Tweet
Share
+12