Formatting

Column Chart Gridlines Cutting Through Bars

Friday, November 2, 2012 by Jon Peltier 25 Comments

Friday, November 2, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

An astute reader asked how to achieve the gridline effect in the following chart. The gridlines are not gray lines running across the whole chart, behind the bars. Instead, the bars themselves appear to be cut into sections where the gridlines would have gone. There are in fact gridlines in the chart, with a line color to match the background, in front of the bars.

Column Chart with gridlines cutting through bars

Gridlines

The thing is, gridlines are always in back of the bars, as shown in this chart. I’ve lightened the bars, and darkened and widened the gridlines, to show their normal relative positions. Don’t let me EVER catch you making gridlines like this.

Normal gridlines lie behind bars. Don't make gridlines like this!

If gridlines are used at all, they should be thin light gray lines, as unobtrusive as possible.

Use thin light gray lines for unobtrusive gridlines

But I digress.

The Data and Chart

Here is the population data for this exercise. Column B has numerical years. Column A has formulas as follows. The even decades (1800, 1820, etc.) simply link to the year; A3 has the formula =B3. The odd decades (1790, 1810, etc.) insert a line feed before the year; A2 has the formula =CHAR(10)&B2. The reason for this will become apparent when we get to it.

US Population Data for our chart

Here is the default chart created from the data in columns B and C.

Default column chart

Let’s apply some formatting. That default title is way too large, perhaps the only concession to my over-50 eyes in all of the Office suite. The gap width is set to 50% so the bars are more prominent. And I’ve changed the source data for the chart to column A. The line feed in alternating cells staggers the X axis labels so the years are easier to read. The vertical axis is removed, because we’ll add our own later.

Apply a little tasteful formatting

Adding Custom Gridlines

And now to add the gridlines. And how do we get gridlines in front of the data? Well, there are several ways to make horizontal lines in Excel charts. The crudest is simply to draw lines from the Shapes command on the Insert tab, but they’re hard to line up, and they don’t stay where you put them. What’s the trick?

Error bars. Error bars are positioned in front of bars in a chart. We’ll apply error bars, and color them white, so they appear to cut through the bars.

We’ll add a series of points along the right vertical edge of the chart, draw error bars to the left of these points, and add data labels to the right. Here is the data.

Data for our gridlines

To get the millions to show without all of those extra zeros, apply a number format of “0,,” (without the quotes, duh). The zero by itself means use the number format with no decimal digits, and each comma after the zero means slice off one group of three zeros, one for thousands, another for millions.

But that 23.5? People always ask me where that came from. The answer is easy once you know it. Let’s look at the chart without the year labels, and just count the bars. They are numbered 1 to 23 from left to right. The tickmarks on the axis between the bars are at the halves: the chart starts at 0.5, the next tickmark is at 1.5, all the way up to the tick at 23.5 at the right edge of the chart. We’ll add an XY series with the points directly above this tickmark, at X=23.5.

Where does '23.5' come from?

Copy the gridline data (two columns and all rows including the “million” label). Select the chart and use Paste Special to add the data to the chart. Make sure the Paste Special dialog has these options selected: New Series, Values in Columns, Series Names in First Row, Categories (X Labels) in First Column, and DON’T Replace Existing Categories.

Paste Special

And here is the chart with the added data. It’s in column chart form, but we’ll fix that.

Add gridline data to chart

Right click on the added bars, choose Change Series Chart Type from the pop-up menu, and choose the XY Scatter type with markers only.

Convert gridline series to XY Scatter

Excel moved the series to the Secondary axis, and dumped an extra set of axes into the chart. No problem, Format the XY series, and on the first tab of the Format Series dialog, choose Primary Axis.

Move XY series to primary axis

Now it’s time to add the error bars. Select the XY Series, and on the Chart Tools > Layout tab, click on Error Bars, More Error Bar Options.

Add error bars

The horizontal error bars are easily visible: note how they lie in front of the bars. You can’t see the vertical error bars, because they are only one unit (one person) tall, but they are selected when you add the error bars. Close the Format Error Bars dialog and press delete to remove the vertical error bars.

Delete vertical error bars (can't see them, because they are 1 person tall)

Format the horizontal error bars. Choose the Minus direction, No Cap as the End Style, and a Fixed Value of 23 (see the explanatory chart above: the error bars reach from X=23.5 to X=0.5, so they are 23 long). Here’s how the Format Error Bars dialog should look:

Format Horizontal Error Bars

And here’s the chart with the appropriately sized error bars.

Format horizontal error bars

Select the XY series, and on the Chart Tools > Layout tab, choose Data Labels > Right. This adds the default data labels, which show the value. The data labels have the same number format as the cells containing the values. You’ll probably have to select the plot area and drag the right edge inward to make room for the labels.

Add data labels to XY series

Format the XY series, and choose None for the Marker Option.

Hide markers of XY series

Finally, format the error bars, so the line color matches the chart background, in this case, white.

Format error bar line color to match chart background

Lessons Learned

In this tutorial I’ve shown how to use gridlines that seem to cut the bars in a bar or column chart. Among the tricks I’ve used are:

Line feeds in the category axis labels to stagger labels and prevent overlap,
Using error bars to draw horizontal lines in a chart (error bars are great for vertical lines, too),
Using data labels for custom axis labels.

You can accomplish many things with these tricks in your charting arsenal.

For an Excel workbook that contains this step-by-step tutorial, click on this link: Gridlines_Splitting_Bar_Chart.xlsx

Posted: Friday, November 2nd, 2012 under Formatting.
Tags: Column Charts, Combination Charts, Error Bars, Gridlines.
Comments: 25

Microsoft MVP Logo

Conditional Formatting of Lines in an Excel Line Chart Using VBA

Tuesday, September 11, 2012 by Jon Peltier 13 Comments

Tuesday, September 11, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.

Posted: Tuesday, September 11th, 2012 under Formatting.
Tags: Conditional Formatting, VBA.
Comments: 13

Microsoft MVP Logo

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

Excel Charts With Horizontal Bands

Monday, September 19, 2011 by Jon Peltier 58 Comments

Monday, September 19, 2011 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

You may want to highlight certain ranges of values in a chart, to make it easier to judge the values in the chart. For example, you may want to highlight “good” values with green or blue, and “bad” values with red. Or you may want to highlight the standard deviation bands in a control chart. This banding is not a native feature of Excel, but you can use standard chart elements (i.e., stacked columns or in this case stacked areas) to achieve this effect. Read on to learn how.

Chart With Horizontal Bands

This technique works with XY (Scatter), Column, and Line charts, and all of the chart triptychs in this tutorial show these chart types in that left to right order. Download the annotated workbook HorizontalBandWorksheet.xlsx if you want to follow along in an existing workbook.

Original Charts and Data

This screenshot shows the data used in this exercise. Column A has the X values for the XY (Scatter) charts, column B has the X labels for the Column and Line charts, and column C has the Y values for all charts. Column E lists the values at the tops of the bands, from the bottom up, starting with the top of the blank area below the lowest band. Column F has a label for these bands, which I’ve simply filled with the intended colors of the bands. Columns G and H have the same values, G2:H2 containing the lowest value from F2 (=F2), and the rest of the cells containing the band heights (e.g., =F3-F2). These heights will be used to populate the values of stacked area charts, which will serve as our bands.

Chart With Horizontal Bands - Data

Here are the XY (Scatter), Column, and Line charts of our original data.

Original Chart Without Bands

You can start with a chart containing your original data, and add the bands, or you can follow a somewhat easier approach which starts from scratch, building a banded chart, then adding the original data. I’ll start the tutorial with the second approach. Skip ahead to Adding Bands to Existing Chart if you are not starting from scratch.

Starting From Scratch

Select the data for the bands (the shaded range F1:H8) and create a stacked area chart (not stacked 100% area), with series data in rows. The charts all look the same for a few steps, until we actually have a series that has either XY, Column, or Line type.