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.

Original range for heat map

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.

Expanded range for heat map

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.

Conditional Formatting dropdown showing Color Scales options

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.

Color scales applied to worksheet ranges

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.

Conditional Formatting Rules Manager

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.

Conditional Formatting Rules Manager

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.

Conditional Formatting Edit Formatting Rules Dialog

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.

Color Brewer Diverging Purple-Green Color SchemeClick 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).

Edit Formatting Rules: Purple-White-Yellow Scale

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.

Purple-White-Green and Purple-Green Color Scales

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.

 

Peltier Tech Chart Utility

Conditional Formatting of Lines in an Excel Line Chart Using VBA

Conditional Formatting of Excel Line and XY Chart Lines

Chart with conditionally formatted line segments

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…

Conditionally Formatted Column Chart

… to markers in line charts…

Conditionally Formatted Line Chart

… and to markers in XY charts…

Conditionally Formatted X-Y Points

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.

Chart with line segments to be conditionally formatted

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.

Dummy chart before recording a line formatting macro

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

Dummy chart after recording a line formatting macro

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.TintAndShade = 0
      .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.TintAndShade = 0
      .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.TintAndShade = 0
      .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:

Chart with conditionally formatted line segments and ugly markers

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.

Chart with conditionally formatted line segments and nice markers

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.

Chart with conditionally formatted line segments and no markers

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.

 

Peltier Tech Chart Utility

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.

Table of colors by value and category label

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.

Named range of colors by value and label

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

Original chart with default bar colors

Apply Formatting by Running the VBA Code

Here is the chart after running the FormatPointByCategoryAndValue procedure.

Original chart with VBA formatted bars

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.

Same chart with modified values and reformatted bars.

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.

Chart with completely new data and reformatted bars

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:

"Select a chart, dummy!"

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.

Run Macro

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.

 

Peltier Tech Chart Utility

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.

Data for Conditional Chart Formatting Example

The data makes a simple unformatted bar chart. . .

Unformatted Bar Chart

. . . or a simple unformatted line chart.

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.

Calculated Data for Conditional Bar Chart Formatting

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

Unformatted Bar Chart Source Data

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.

Formatted Bar Chart Source Data

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.

Partially Formatted Bar Chart

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

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.

Calculated Data for Conditional Line Chart Formatting

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

Unformatted Line Chart Source Data

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.

Formatted Line Chart Source Data

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

Partially Formatted Line Chart

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.

Partially Formatted Line Chart

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.

Conditionally Formatted Line Chart

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.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.