## Create a Heat Map Using Excel’s Conditional Formatting

A reader of my post Excel 3D Charts: Charts with No Value asked how to arrange his data to display a variable on a grid, thinking I’d acquiesce to his desire for a 3D chart. My initial thought was “No way”, but I first asked what he was plotting. He was doing an agricultural experimental design with a 2×5 grid of plots planted with various plants, and wanted to see whether there was a positional variation to the results which would not have been seen in a standard ANOVA analysis. His output values typically ranged from 1000 to 5000 pounds per acre.

I thought this could be visualized in 2D without the usual 3D issues, using a heat map. Now I’ll construct such a heat map using Excel’s Conditional Formatting Feature.

I set up a 2 column by 5 row grid, and inserted random numbers between 1000 and 5000.

To represent a 2×5 grid of square plots, I adjusted the rows and columns to be the same size, in this case 50 pixels. This was an assumption on my part, but whatever the dimensions of the actual plots, you can fudge the row and column dimensions to represent these dimensions.

I selected this range, and clicked on Conditional Formatting on the Home tab of the Excel 2013 ribbon, and hovered over Color Scales. As far as I recall, this part of the color scales mechanism worked the same in Excel 2007 and 2010.

There are a dozen built-in color scales, and if this isn’t enough you can pick More Rules.

Below are the five built-in options I’d be most likely to use for such a heat map, highlighted in the popup menu and displayed in theworksheet. These are a red-yellow-green 3-color diverging scale, a red-white-green 3-color diverging scale, a red-white-blue 3-color diverging scale, a white-green 2-color sequential scale, and a yellow-green 2-color sequential scale.

I decided to use the red-white-green diverging scale, because I thought it would be the most color-vision-friendly of the three diverging scales.

To change to a better scale, I selected the range and selected Manage Rules at the bottom of the Conditional Formatting dropdown menu, and the Formatting Rules Manager dialog appeared.

If I’d selected a larger range with multiple sets of rules, like the range shown above with five color scales applied, this dialog would show all of the rules.

I selected the color scale I wanted to change, and clicked Edit Rule, to pop up the Edit Formatting Rule dialog. You can see that the options for this type of rule include 2- and 3-color scales, which are useful for heat maps, as well as data bars and icon sets, which are not.

You can select three colors for minimum, midpoint, and maximum, and you can choose to define these points in several ways. Here I’ve kept the default lowest value, 50th percentile (median), and highest value, but all could be defined by percentiles, values, or formulas.

To select my colors, I went to my favorite source for color schemes at ColorBrewer2.org, by Cynthia A. Brewer at Pennsylvania State University. I decided on a color-vision safe, 9-color, purple-to-green diverging scale, and this is what ColorBrewer showed me. Click on the image to visit this selection at ColorBrewer2.org.

Click on the image to visit this selection at ColorBrewer2.org.

ColorBrewer also lets you export the colors in various ways, including as an array of RGB values:

{118, 42, 131; 153, 112, 171; 194, 165, 207; 231, 212, 232; 247, 247, 247; 217, 240, 211; 166, 219, 160; 90, 174, 97; 27, 120, 55}

I used the second (purple) and eighth (green) elements of this color scheme for the min and max colors, and kept the white central value (the ColorBrewer midpoint is about 5% gray).

Below is the purple-white-green 3-color diverging scale (left), a purple-green 2-color sequential variation (center), and a white-green 2-color sequential variation (right). Note that these intermediate colors are not ColorBrewer’s, but are calculated by Excel.

The three-color purple-to-white-to-green scale seems to show the variation better than the two-color purple-to-green scheme, because the latter doesn’t show much variation within the greens or within the purples. The two-color white-to-green might be the best overall choice, especially if the heat map has to be photocopied.

## Conditional Formatting of Excel Line and XY Chart Lines

I’ve written a lot about conditional formatting of charts. One topic I have not covered is formatting of lines in line charts and XY charts, because this isn’t as easy to do.

## Formula-Based Conditional Formatting

In Conditional Formatting of Excel Charts and Conditional XY Charts Without VBA, I showed examples of a formula-based technique to apply conditional formatting in Excel Charts. Essentially there are as many series in the chart as there are distinct formats you want to show. There is one point (i.e., one bar or marker) displayed where a point should be displayed, and the formulas control which series displays each point, with which format. Using formulas is more reliable than using VBA, because updating is dynamic without needing to run code whenever the data changes, and it’s easier to maintain. This approach can be applied to bar or column charts…

… to markers in line charts…

… and to markers in XY charts…

This conditional formatting is applied to each individual point, but unfortunately it cannot be applied in this way to the individual line segments connecting markers in a line chart.

## VBA-Controlled Conditional Formatting

Sometimes you don’t have the luxury of using formulas for your conditional formatting. Here are a few articles showing VBA techniques for applying conditional formats to a chart:

## VBA-Controlled Conditional Formatting of Line Chart Lines

You might want to format lines differently if they represent rising or falling values. Green line segments for increasing trends or red for decreasing. Actually, a more color-vision-friendly combination is blue for increasing and orange for decreasing. The chart at the top of this article is a good example.

Unfortunately, line segments in a line or XY chart cannot be turned on or off using formulas, the way markers or columns and bars can be. So we’ll have to use VBA to format them.

For this tutorial. let’s dig out some web site statistics. This chart of weekly visits show some week-to-week fluctuations, with occasional flat segments which we will not want to color.

I decided to record a macro so I can figure out the syntax. The help files are pretty sketchy about this.

I started with a boring dummy chart with three series.

I turned on the macro recorder while I applied the following formatting to the dummy chart. I changed line colors, and I didn’t change the marker color but shrunk the marker size

Here’s the recorded macro. It’s no uglier than any other recorded macro.

```Sub RecordedMacro1()
'
' RecordedMacro1 Macro
'

'
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent5
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2.25
End With
Selection.MarkerSize = 4
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent6
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2.25
End With
Selection.MarkerSize = 4
ActiveChart.SeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.Brightness = -0.349999994
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2.25
End With
Selection.MarkerSize = 4
End Sub```

Well, yeah, it is kind of ugly. It selects everything, then works on the selection object, and includes all that default stuff I never touched.

Long story short (long code short), I used constants for the formatting settings, streamlined the code. I also did some magic to compare whether adjacent points showed increasing or decreasing values to decide what colors to use.

```Sub ColorLinesBasedOnSlope_ThemeColor()
Dim srs As Series
Dim iPoint As Long
Dim vValues As Variant

Const thmclrBlue As Long = msoThemeColorAccent5
Const briteBlue As Double = 0.4
Const thmclrOrange As Long = msoThemeColorAccent6
Const briteOrange As Double = 0.4
Const thmclrGray As Long = msoThemeColorBackground1
Const briteGray As Double = -0.4
Const dWeight As Double = 2.25
Const lMarkerSize As Long = 4

Set srs = ActiveChart.SeriesCollection(1)
vValues = srs.Values
For iPoint = 2 To UBound(vValues)
With srs.Points(iPoint).Format.Line.ForeColor
Select Case vValues(iPoint) - vValues(iPoint - 1)
Case Is > 0
.ObjectThemeColor = thmclrBlue
.Brightness = briteBlue
Case Is < 0
.ObjectThemeColor = thmclrOrange
.Brightness = briteOrange
Case Else
.ObjectThemeColor = thmclrGray
.Brightness = briteGray
End Select
End With
srs.Points(iPoint).Format.Line.Weight = dWeight
srs.Points(iPoint).MarkerSize = lMarkerSize
Next
End Sub```

I selected my chart (actually, I was smart and selected a duplicate chart) and ran this code. Here’s what happened:

Oof! Not only did the code format the line segments, it also formatted the marker lines. I never did that while recording the macro.

Turns out, the object model for Excel 2007/2010 charts does not distinguish between the connecting lines and the lines that draw the markers. Well, that’s no good.

Fortunately I still can use the old tired and true Excel 2003 VBA syntax. Instead of .Format.Line.Whatever, I reverted to .Border.Whatever, and I took out that weird ThemeColor drivel, and replaced it with the familiar RGB color definitions. I present the updated code.

```Sub ColorLinesBasedOnSlope_RGBColor()
Dim srs As Series
Dim iPoint As Long
Dim vValues As Variant

Const rgbMyBlue As Long = 14536083
Const rgbMyOrange As Long = 9486586
Const rgbMyGray As Long = 10921638
Const lWeight As Long = xlThick
Const lMarkerSize As Long = 4

Set srs = ActiveChart.SeriesCollection(1)
vValues = srs.Values
For iPoint = 2 To UBound(vValues)
With srs.Points(iPoint).Border
Select Case vValues(iPoint) - vValues(iPoint - 1)
Case Is > 0
.Color = rgbMyBlue
Case Is < 0
.Color = rgbMyOrange
Case Else
.Color = rgbMyGray
End Select
.Weight = lWeight
End With
srs.Points(iPoint).MarkerSize = lMarkerSize
Next
End Sub```

I selected another copy of the chart, and ran this new procedure.

Nice. It colored the lines orange and blue, except for a couple horizontal segments that were colored gray. It also kept the markers gray, but shrunk them so they don’t overwhelm the lines.

The markers aren’t really necessary. I like them, so I know for sure where there is actual data, but you can judge where the lines change their angle or color to know this. I made yet another copy of my original chart, removed the markers, and ran the formatting procedures.

Which procedure did I use? Doesn’t matter. Both produced the same result, since there were no markers for the newfangled code to mess up.

## VBA Conditional Formatting of Charts by Value and Label

In Conditional Formatting of Excel Charts I showed how to simulate conditionally formatting in your charts charts. The trick is to set up ranges containing formulas for each of the conditions, so that if the condition for that range is met, the value will be plotted in the appropriately formatted chart series. Otherwise it doesn’t appear in the chart, but a differently formatted point will.

In most cases the technique in the cited article is the best approach, since it does not use VBA and it updates with the speed of Excel formulas. Sometimes, though, you need a VBA solution.

## Prepare for VBA Formatting

In a worksheet named “ColorSheet”, I have set up a range that has row headers corresponding to the category labels I expect to encounter, and column headers corresponding to values I want to use as cut-offs for conditional formatting. The cell at the intersection of the category label row and value column is formatted in the way I want the chart’s data to be formatted. For example, a point with a category label of Alpha will be some shade of blue, while a point labeled Eta will be orange. The shade of the color used is lighter for smaller values and darker for larger values. I have included a label “other” in case an unexpected label is found, and a value of “above” in case the maximum in the table is exceeded. The light shades might be too light, and in any case, shades of color shouldn’t be expected to provide much resolution when encoding information.

Naturally, your labels and values will be different, and in fact you may need more complicated algorithms in the code.

I’ve named this region “ColorRange”. Note that this name appears in the Name Box above column A when the range is selected.

Here is my data and chart. The bars have the default formatting for series 1.

## Apply Formatting by Running the VBA Code

Here is the chart after running the FormatPointByCategoryAndValue procedure.

After changing the values and rerunning the procedure, the chart’s bars keep their color, though the shades have become lighter or darker. The light green for Gamma is too light to distinguish from gray, and almost too light to see at all. I should probably go back and darken all of the lighter shades.

Now all of the data has changed, and in fact, more data is plotted in the chart. The code still works as expected. Note the Omega series, which is colored gray because that label was not included in the original color table.

## The VBA Procedure

The code is not too complicated. After the declarations (Dim and Consts) the first thing that the code does is abort if the user has not selected a chart (a little extra effort is always well-spent if it that a run time error). There is a line of code which, if uncommented, will remind the user to select a chart by showing this dialog:

Then the range containing the colors is identified and its values put into an array to make the values easier to work with.

The first series of the active chart is defined as the series we are formatting. The category labels (XValues) and values (Values) are put into arrays, also for ease of processing.

The code then looks at each point’s value and label, to determine which cell has the desired formatting. The rows and columns are looped starting at 2, since the first of each contains an irrelevant label. The looping stops one count before the end. If a match is not found, the loop counter points to the last row or column.

Finally the point is given the same fill color as the matching cell in the color table. Note that there are two sets of syntax, one for Classic Excel (2003 and earlier) and the other for Neo Excel (2007 and later). I’ve commented out the 2003 syntax, because I now use 2010 for most of my outward-facing work.

``````Sub FormatPointByCategoryAndValue()
Dim rColor As Range
Dim vColor As Variant
Dim srsColor As Series
Dim iRow As Long
Dim iCol As Long
Dim iPoint As Long
Dim vCategories As Variant
Dim vValues As Variant

Const sColorSheetName As String = "ColorSheet"
Const sColorRangeName As String = "ColorRange"

If ActiveChart Is Nothing Then
' uncomment following line to alert user
'MsgBox "Select a chart and try again.", vbExclamation, _
"No Active Chart"
GoTo ExitHere
End If

Set rColor = Worksheets(sColorSheetName).Range(sColorRangeName)
vColor = rColor.Value

Set srsColor = ActiveChart.SeriesCollection(1)

With srsColor
vCategories = .XValues
vValues = .Values

' cycle through points
For iPoint = 1 To .Points.Count
' find category (row)
For iRow = LBound(vColor, 1) + 1 To UBound(vColor, 1) - 1
If vCategories(iPoint) = vColor(iRow, 1) Then Exit For
Next

' find value (column)
For iCol = LBound(vColor, 2) + 1 To UBound(vColor, 2) - 1
If vValues(iPoint) <= vColor(1, iCol) Then Exit For
Next

' apply color of identified cell to given point
' Excel 2007+ syntax
.Points(iPoint).Format.Fill.ForeColor.RGB = _
rColor.Cells(iRow, iCol).Interior.Color

' Excel 2003- syntax
'.Points(iPoint).Interior.ColorIndex = _
rColor.Cells(iRow, iCol).Interior.ColorIndex
Next
End With

ExitHere:

End Sub``````

To run the procedure, select the chart, then press Alt+F8, select FormatPointByCategoryAndValue, and click Run.

Here are some closely related articles from the archives:

To format by point value, see VBA Conditional Formatting of Charts by Value.
To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.

## Conditional Formatting of Excel Charts

It’s relatively easy to apply conditional formatting in an Excel worksheet. It’s a built-in feature on the Home tab of the Excel ribbon, and there many resources on the web to get help (see for example what Debra Dalgleish and Chip Pearson have to say). Conditional formatting of charts is a different story.

People often ask how to conditionally format a chart, that is, how to change the formatting of a chart’s plotted points (markers, bar fill color, etc.) based on the values of the points. This can be done using VBA to change the individual chart elements (for example, VBA Conditional Formatting of Charts by Value), but the code must be run whenever the data changes to maintain the formatting. The following technique works very well without resorting to macros, with the added advantage that you don’t have to muck about in VBA.

## Unformatted Charts

Here is the simple data for our conditional chart formatting example.

The data makes a simple unformatted bar chart. . .

. . . or a simple unformatted line chart.

We want our charts to show different colored points depending on the points’ values.

Except for some simple built-in formats, conditional formatting of worksheet ranges requires formulas to determine which cells should take on the formatting. In the same way, we will use formulas to define the formatting of series in the charts.

We will replace the original plotted data in the line and bar charts with several series, one for each set of conditions of interest. Our data ranges from 0 to 10, and we will create series for each of the ranges 0-2, 2-4, 4-6, 6-8, and 8-10.

## Conditional Formatted Bar Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

``=C1&"<Y<="&C2``

The formula is cell C4 is

``=IF(AND(C\$1<\$B4,\$B4<=C\$2),\$B4,"")``

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an apparent blank. The formula is filled into the range C4:G13.

When the bar chart is selected, the chart’s source data is highlighted as shown.

We need to change the source data, removing column B and adding columns C:G. This is easily done by dragging and resizing the colored highlights.

The chart now shows five sets of colored bars, one for each data range of interest. It’s not quite right, though, since it’s a clustered bar chart, and each visible bar is clustered with four blank values.

This is easily corrected by formatting any one of the bars, and changing the Overlap property to 100%. This makes the visible bars overlap with the blank bars.

## Conditionally Formatted Line Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

``=C1&"<Y<="&C2``

The formula is cell C4 is

``=IF(AND(C\$1<\$B4,\$B4<=C\$2),\$B4,NA())``

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an error, #N/A, which will not be plotted in a line chart. The formula is filled into the range C4:G13.

When the line chart is selected, the chart’s source data is highlighted as shown.

We need to expand the source data, keeping column B as a line connecting all points and adding columns C:G for the separately formatted series. This is easily done by resizing the colored highlights.

The chart now shows five sets of colored markers and line segments, one for each data range of interest.

A little formatting cleans it up. Remove the markers from the original series, remove the lines from the other series, and apply distinct marker formats to the added series.

Remove the unneeded legend entry (for the gray line) by clicking once to select the legend, clicking again to select the label, and clicking Delete.

## Conditional Formatting Flexibility

This simple example has formatting formulas defined based on the Y values in the chart. It is possible to define formatting based on Y values, X values, or values in another column which is not even plotted. As in worksheet conditional formatting, the only limit is your own ability to construct formulas.

This technique works on most useful Excel chart types, including bar and line charts shown here, and XY charts as shown in Conditional XY Charts Without VBA.