程阳：Create Heat Maps Using Excel

 Before Converting to a Heat Map

Heat Maps are an excellent way to present a lot of data in a single view, and can be created using Excel.  It can also be used to identify trends in data that you would lose if you tried to average or aggregate the data to make it presentable.  By using a Heat Map you avoid destroying some of the value of your data.

The way to create a Heat Mapwith Excel is to select the Surface Chart type.  Then you want to remove the lines from between the series, and adjust the number of increments in the scale.

By default, Excel's surface charts display black lines between each color gradient, which makes the chart look strange (and not like a heat map).

Click the chart legend to select it
Click the first legend key (that is the small colored square)
Right-click and choose Format Legend Key
In the Format Legend Key dialog box, click the Patterns tab
Select None for the Border option, and click OK
Repeat those steps for every series in your chart.

If you want to do it automatically, you can use the following macro:

Sub RemoveLinesandSetColors()
Dim LE As Integer
Dim red As Integer
Dim green As Integer
Dim blue As Integer
Dim a As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim q As Integer

red = 0
green = 0
blue = 255

LE = 1

If ActiveChart Is Nothing Then Exit Sub
If MsgBox("Remove lines from surface chart and set colors?", vbYesNo) = vbYes Then
Application.ScreenUpdating = False
ActiveChart.HasLegend = True
a = ActiveChart.Legend.LegendEntries.Count
x = a / 4
y = Int(255 / x)

For z = 1 To 4
For q = 1 To x
ActiveChart.Legend.LegendEntries(LE).LegendKey.Border.LineStyle = xlNone
ActiveChart.Legend.LegendEntries(LE).LegendKey.Interior.Color = RGB(red, green, blue)

If z = 1 Then green = green + y

If z = 2 Then blue = blue - y

If z = 3 Then red = red + y

If z = 4 Then green = green - y

LE = LE + 1
Next q

q = 1
Next z

End If

End Sub
To use this macro, copy it and paste it to a VBA module. Then, select a surface chart and press Alt+F8 to display the Macro dialog box. Execute the RemoveLinesandSetColors macro, and your chart will be changed. Note that the macro also changes the color scale to a blue-to-red spectrum.

The number of colored gradients in a surface chart is determined by the Major Unit setting for the vertical axis. Right-click the legend and select the Scale tab.  Change the value for the Major Unit. The larger the number, the fewer the colors.  Set the scale to an increment that, for your values, will give you a good range of colors.

One note:  VBA is pretty picky about some strange things.  For example, the macro will fail if the Legend Key displayed on the screen does not show all of the legend key entries.  If you have 100 entries but a few of them are outside of the Legend Key box, the macro will fail.  One way to get around this is to change the font size to 1 for the legend, and make the legend box as big as possible.  Then run the macro, then change the Legend Key Font back to whatever you want.

This VBA macro above not only removes the lines, but sets the color scale to a spectrum from blue to red, with blue being low values and red being high.  It increments the colors based on the number of Legend Key entries.

I hope this helps you as you try to create a Heat Map in Excel.

（Heatmap 翻译为“级图”，意为“等级图”。一些早前的说法，“热图”、“数据地图”、“数图”等等均有歧义，容易混淆，故不宜采用——

“热力图”是工程热力学专门术语

“热图”多与情-色相关

“数图”数字图书馆

“数据地图”大多数行业应用与“地图”无关

“数据图”太宽泛，没有针对性）

Heat Map

程阳：Create Heat Maps Using Excel 

ExcelPro：级图制作详解 

NASDAQ-100 Pre-Market Heatmap