Create a heatmap in Excel

Creating heatmaps

An easy way to create heatmaps is to use conditional formatting in your spreadsheet.

However, we are going to look at creating heatmaps as an Excel Chart, and also to automatically create a heatmap in a table of data. As usual this code is implemented in the cDataSet.xlsm module downloadable from Download Complete Projects

Note that this method of calculating the heatmap scale has been superseded by the Color ramp library See Charts and color ramps for how to make interesting charts using color ramps

The HeatMap scale

When I was researching this topic, I had some problems figuring out how to use RGB codes to create a smooth transition from blue to red. Finally I came across this write up http://paulbourke.net/texture_colour/colourramp/ from which I was able to create my first simple VBA function to calculate an RGB code from a value’s position in a range, and which will become smoother the bigger the range.  

Here is the code for that. Note that this method of calculating the heatmap scale has been superseded by the Color ramp library so this is for informational purposes only.

Private Function heatMapColor(min As Variant, _
                max As Variant, Value As Variant) As Long

    Dim spread As Double, ratio As Double, red As Double, _
                    green As Double, blue As Double
    spread = max - min
    Debug.Assert spread >= 0
    ratio = (Value - min) / spread

    If ratio < 0.25 Then
        blue = 1
        green = 4 * ratio
    ElseIf ratio < 0.5 Then
        green = 1
        blue = 1 + 4 * (min - Value + 0.25 * spread) / spread
    ElseIf ratio < 0.75 Then
        green = 1
        red = 4 * (Value - min - 0.5 * spread) / spread
    Else
        red = 1
        green = 1 + 4 * (min - Value + 0.75 * spread) / spread

    End If
    heatMapColor = RGB(red * 255, green * 255, blue * 255)
        
End Function

Automating heatmap to a table of data

The first option is simply to use the values in a table and color the cell with the appropriate heatmap color. As usual, the data is abstracted using Data Manipulation Classes which makes the mechanics pretty simple.  

In this case I colored the heading row with the heatmap scale, and used the values in the table to determine the ‘hotness’ of the background cell color. 

Here is the code, to read the data and color the heading row and the data. 

Public Sub testHeatMapScale()
    Dim dsout As New cDataSet, dc As cCell
    Dim mx As Variant, mn As Variant, dr As cDataRow
    With dsout.populateData(wholeSheet("heatmapcolors"), , , , , , True)
        mx = .max.Value
        mn = .min.Value
        ' do the heading as a heatmap scale
        For Each dc In .HeadingRow.Headings
            dc.where.Interior.color = heatMapColor(1, .ColumnCount, dc.Column)
        Next dc
        'now the data
        For Each dr In .Rows
            For Each dc In dr.Columns
              dc.where.Interior.color = heatMapColor(mn, mx, dc.Value)
            Next dc
        Next dr
    End With
End Sub

Here is the code, to read the data and color the heading row and the data.

Creating a heatmap chart

Excel provides a couple of surface charts that are pretty nice for heatmaps.  Here is one, or see Charts and color ramps for a generalized version of this.

I have provided a generalized chartmaker in cDatSet.xlsm Download Complete Projects which makes the task just one line of code as follows.  

Public Sub heatMapExample()
    Dim dsout As New cDataSet
    createSurfaceChart _
        dsout.populateData(wholeSheet("matrixout"), , "matrixout", True, , , True, "matrix"), _
        "heatmap"
End Sub

To smooth out the transition of colors, I just ask for more categories as follows (in this case 50)

Public Sub heatMapExample()
    Dim dsout As New cDataSet
    createSurfaceChart _
        dsout.populateData(wholeSheet("matrixout"), , "matrixout", True, , , True, "matrix"), _
        "heatmap", , 50
End Sub

 that gives us this  

Finally, here is how to get a different kind of heatmap  

Public Sub heatMapExample()
    Dim dsout As New cDataSet
    createSurfaceChart _
        dsout.populateData(wholeSheet("matrixout"), , "matrixout", True, , , True, "matrix"), _
        "heatmap", xlSurfaceTopView, 50
End Sub

gives us

Code for chart automation

You will find the code and explanation for this in Charts and color ramps.
Acknowlegdement

Summary

For more tips like this, take a look at Get Started Snippets In the meantime why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.

You can also submit anything you want considered for publication on this site to our forum.  See Array formulas and large ranges for another way to solve this problem. You could also take a look at the Color ramp library

Acknowledgement 

http://paulbourke.net/texture_colour/colourramp/ for the write up and getting me started on algorithms for heatmap colors.