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

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.