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

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.

Subpages (2):