Conditional Formatting

Conditional Formatting of Excel Charts

Monday, February 13, 2012 by Jon Peltier 195 Comments

Monday, February 13, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.

Peltier Tech Articles About Conditional Formatting of Excel Charts

Posted: Monday, February 13th, 2012 under Formatting.
Tags: Conditional Charts, Conditional Formatting.
Comments: 195

Pivot Table Conditional Formatting with VBA

Wednesday, October 21, 2009 by Jon Peltier 47 Comments

Wednesday, October 21, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A reader encountered problems applying conditional formatting to a pivot table. I tried it myself, using the same kind of formulas I would have applied in a regular worksheet range, and had no problem. The reader responded that he was having problems in Excel 2007, and I was using 2003. Apparently in 2003 the conditional formatting is preserved when the table is refreshed, but in 2007, the CF in the data range is wiped out.

Well, this is just the type of thing Bill gates invented Visual Basic for. I’ll apply an approach related to that in my VBA Conditional Chart Formatting series: VBA Conditional Formatting of Charts by Series Name, VBA Conditional Formatting of Charts by Category Label, and VBA Conditional Formatting of Charts by Value, with a little help from Referencing Pivot Table Ranges in VBA. I’ll use arbitrary data and an arbitrary condition for the example.

Here is a data source for a simple pivot table. The entire range of values in column D consists of random integers between 1 and 9, and there are three categories by which they can be sorted.

Pivot Table Data Source

The first pivot table, PivotTable1, has the category fields all placed in the rows area. I manually typed the name “Pivot Table 1” in the cell above the pivot table.

Pivot Table 1 Unformatted

Tip: To name a pivot table, right click on the table, choose Table Options, and edit the name in the first line of the dialog.

The second pivot table, PivotTable2, has one category in the columns area, so the values are separated into two columns.

Pivot Table 2 Unformatted

I used the following VBA procedure to apply bold text and yellow fill formatting to rows in PivotTable1 if the value in the Total column is 7 or greater. We are testing the entire DataBodyRange, which is one column wide.

Sub FormatPT1()
  Dim c As Range
  With ActiveSheet.PivotTables("PivotTable1")
    
    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With
    
    ' apply formatting to each row if condition is met
    For Each c In .DataBodyRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next
    
  End With
End Sub

I used the next procedure to apply the same formatting to PivotTable2 rows if the value in column a under Category 3 is 7 or greater. The DataBodyRange is two columns wide, for pivot items “a” and “b”. To test against pivot item “a”, we test the pivot item’s DataRange.

Sub FormatPT2()
  Dim c As Range
  With ActiveSheet.PivotTables("Pivottable2")
    
    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With
    
    ' apply formatting to each row if condition is met
    For Each c In .PivotFields("Category 3").PivotItems("a").DataRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next
    
  End With
End Sub

The two procedures above belong in a regular code module.

Here is PivotTable1 with the conditional formatting applied.

Pivot Table 1 Formatted

Here is PivotTable2 with the same formatting applied.

Pivot Table 2 Formatted

Note that refreshing the pivot tables changes values but does not automatically reformat the tables. You have to manually rerun the VBA routines, or capture the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Select Case Target.Name
    Case "PivotTable1"
      FormatPT1
    Case "PivotTable2"
      FormatPT2
  End Select
End Sub

This procedure goes in the code module for the worksheet that contains the pivot tables. To access this module easily, right click on the sheet tab, and choose View Code. If you use this approach, you can put the first two procedures into the worksheet’s code module, instead of a regular module.

Posted: Wednesday, October 21st, 2009 under VBA.
Tags: Conditional Formatting, Pivot Tables.
Comments: 47

Microsoft MVP Logo

VBA Conditional Formatting of Charts by Series Name

Monday, March 3, 2008 by Jon Peltier 134 Comments

Monday, March 3, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

I’ve got a couple of tutorial pages on my web site that deal with conditionally formatted 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 range contains the value to plot, otherwise it contains #N/A, which isn’t plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart.

As with conditional formatting in the worksheet, the formatting you can achieve is limited only by your ability to construct formulas. Of course, in the chart, you are not limited to three conditional formats as you are in the worksheet (in Excel 2003 at least; the conditional formatting enhancements in Excel 2007 are mind-boggling).

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a chart with several series, and the series may be in any order. However, you want to color a category according to its name, so that Halliburton always is colored red and Enron is always blue.

The following protocol allows you to color the series in a chart according to colors you’ve reserved for each series. The range below illustrates the data: range A1:A4 contains a list of the series names, with each cell filled with the desired color for that series’ data in a chart. A6:E10 contains data for Chart 1, and A12:E16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

The charts made from the two ranges are not remarkable.

There is a simple VBA procedure that will apply the colors from the designated range to the appropriate seriess, based on their names.

Excel 2003 and earlier:

Sub ColorBySeriesName()
  Dim rPatterns As Range
  Dim iSeries As Long
  Dim rSeries As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole)
      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Interior.ColorIndex = _
            rSeries.Interior.ColorIndex
      End If
    Next
  End With
End Sub

Excel 2007 and later:

Sub ColorBySeriesName()
  Dim rPatterns As Range
  Dim iSeries As Long
  Dim rSeries As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole)
      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
            rSeries.Interior.Color
      End If
    Next
  End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their series names and the colored table in A1:A4.

Articles about Non-VBA Conditional Formatting of Charts

Posted: Monday, March 3rd, 2008 under VBA.
Tags: Colors, Conditional Formatting.
Comments: 134

Microsoft MVP Logo

VBA Conditional Formatting of Charts by Value

Monday, March 3, 2008 by Jon Peltier 80 Comments

Monday, March 3, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

I’ve got a number of tutorials on this blog that deal with conditionally formatted 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 range contains the value to plot, otherwise it contains #N/A, which isn’t plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart.

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a column chart, and the values may be in any order. However, you want to color a column according to its value, so that small values always are colored red and large values are always green.

The following protocol allows you to color the points in a series according to colors you’ve reserved for certain values. The range below illustrates the data: range A1:A4 contains a list of the categories, with each cell filled with the desired color for that value in a chart; actually, the procedure will use the color in the cell with the smallest value greater than or equal to the point’s value. A6:B10 contains data for Chart 1, and A12:B16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

The charts made from the two ranges are not remarkable.

There is a simple VBA procedure that will apply the colors from the designated range to the appropriate points, based on their categories.

Excel 2003 and earlier:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  With ActiveChart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
      For iPattern = 1 To UBound(vPatterns)
        If vValues(iPoint) <= vPatterns(iPattern, 1) Then
          .Points(iPoint).Interior.ColorIndex = _
              rPatterns.Cells(iPattern, 1).Interior.ColorIndex
          Exit For
        End If
      Next
    Next
  End With
End Sub

Excel 2007 and later:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  With ActiveChart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
      For iPattern = 1 To UBound(vPatterns)
        If vValues(iPoint) <= vPatterns(iPattern, 1) Then
          .Points(iPoint).Format.Fill.ForeColor.RGB = _
              rPatterns.Cells(iPattern, 1).Interior.Color
          Exit For
        End If
      Next
    Next
  End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their values and the colored table in A1:A4.

Articles about Non-VBA Conditional Formatting of Charts

Posted: Monday, March 3rd, 2008 under VBA.
Tags: Bar Charts, Colors, Column Charts, Conditional Formatting.
Comments: 80

Microsoft MVP Logo

VBA Conditional Formatting of Charts by Category Label

Monday, March 3, 2008 by Jon Peltier 176 Comments

I have several tutorials on this blog that deal with conditionally formatted 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 range contains the value to plot, otherwise it contains #N/A, which isn’t plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart.

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a sorted column chart, and the categories may be in any order. However, you want to color a category according to its name, so that Halliburton always is colored red and Enron is always blue.

The following protocol allows you to color the points in a series according to colors you’ve reserved for certain categories. The range below illustrates the data: range A1:A4 contains a list of the categories, with each cell filled with the desired color for that category’s bar in a chart. A6:B10 contains data for Chart 1, and A12:B16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

The charts made from the two ranges are not remarkable.

There is a simple VBA procedure that will apply the colors from the designated range to the appropriate points, based on their categories.

Excel 2003 and earlier:

Sub ColorByCategoryLabel()
  Dim rPatterns As Range
  Dim iCategory As Long
  Dim vCategories As Variant
  Dim rCategory As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart.SeriesCollection(1)
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
      .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
    Next
  End With
End Sub

Excel 2007 and later:

Sub ColorByCategoryLabel()
  Dim rPatterns As Range
  Dim iCategory As Long
  Dim vCategories As Variant
  Dim rCategory As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart.SeriesCollection(1)
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
      .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
    Next
  End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their category names and the colored table in A1:A4.

Articles about Non-VBA Conditional Formatting of Charts

Posted: Monday, March 3rd, 2008 under VBA.
Tags: Bar Charts, Colors, Column Charts, Conditional Formatting.
Comments: 176

Microsoft MVP Logo

Conditional Formatting

Conditional Formatting of Excel Charts

Unformatted Charts

Conditional Formatted Bar Chart

Conditionally Formatted Line Chart

Conditional Formatting Flexibility

Peltier Tech Articles About Conditional Formatting of Excel Charts

Pivot Table Conditional Formatting with VBA

VBA Conditional Formatting of Charts by Series Name

Related Articles about VBA Conditional Formatting of Charts

Articles about Non-VBA Conditional Formatting of Charts

VBA Conditional Formatting of Charts by Value

Related Articles about VBA Conditional Formatting of Charts

Articles about Non-VBA Conditional Formatting of Charts

VBA Conditional Formatting of Charts by Category Label

Related Articles about VBA Conditional Formatting of Charts

Articles about Non-VBA Conditional Formatting of Charts