You may have sets of data for several categories, and you want to show the values for each category, and highlight some kind of range for each category (a permissible range, a target range, or some statistically computed range).
The following chart shows three categories (A, B, and C) with highlighted ranges and several data points for each.
This combination chart is simple to make.
The Individual Val
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 Conditional Formatting of Charts by Series Name
- VBA Conditional Formatting of Charts by Value
- VBA Conditional Formatting of Charts by Category Label
- Pivot Table Conditional Formatting with VBA
- VBA Conditional Formatting of Charts by Value and Label
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.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:
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.
We can plot the points as an XY Scatter chart series. This chart uses 1, 2, and 3 for categories A, B, and C.
There is substantial overlap between points, so it would be helpful to jitter the points, that is, spread them out laterally so they no longer overlap.
And of course, we want to show A, B, and C instead of 1, 2, and 3. Plus add the highlighted ranges for each category.
We’ll use the following data. The floating columns are defined in B2:D5, and the actual points are described in F2:I17. Column F has the point identification, category and index, which will not be shown in the chart. Column G has X values 1, 2, and 3 corresponding to the categories A, B, and C. Column H has the jittered X values. Column I has the actual Y values.
I’ve never encountered a useful jittering algorithm, so I fudged my own, which works well enough. The formula in H3 is
This gives a random number within ±0.2 of G3, biased closer to the middle of the ±0.2 range.
Building the Chart
Make a stacked column chart with the data in B2:D5.
Format the bottom series (“Min”) so its fill is No Color. The result is the floating column chart that serves as a backdrop for the actual points.
When an XY series is plotted on the axes of a column chart, an X value of 1 corresponds to the first category, 2 corresponds to the second category, etc. A value between 1 and 2 will be plotted proportionally between the first and second categories: 1.5 is midway between the columns at A and B. The jittered X values between 0.8 and 1.2 will fit within the width of the column at A.
Copy the range H2:I17, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, category labels in first column.
The new series was added as a new set of columns stacked on the floating columns and on extra categories further to the right.
Right click one of the added points, choose Change Chart Type, and select the XY variant with markers and no lines.
The XY points were initially plotted onto secondary axes, which are misaligned from the primary axes of the floating columns. Format the XY series so it is plotted on the primary axis to finish the chart.