Error Bar Ideosyncrasy

After using Excel 2007 for a while, getting used to the idea that everything had changed and we weren’t going back to 2003 ever again, I started fooling around with charts and chart elements. I began to find lots of little changes, most of them actually good ones, even if they were implemented in a funny way (at least until service packs and Excel 2010 cleaned up things).

Anyone who used my first charting utility knows things ain’t always done right the first time, and mine were tiny bits of software. I can’t imagine Microsoft’s task trying to coordinate thousands of developers and a bazillian lines of code.

One of the neat changes I discovered was that error bars were now treated as AutoShape lines, just like any lines in any shapes in Office. This means you had a lot of formatting options available to you, including nice embellishments like arrowheads on the ends of the error bar line segments. Then I forgot about it until a colleague brought it up recently.

To show this nice formatting, let’s look at a simple line chart with simple error bars. For clarity, and to avoid spoiling the story before I’m ready to tell it, I’ll start with positive error bars only.

Simple Line Chart with Simple Error Bars

Select the error bars and click Ctrl+1 (numeral one) to open the Format Error Bars task pane. In the main tab (below the bar chart icon, shown below left) change End Style from Cap to No Cap. Then on the formatting tab (below the paint can icon) check out the Begin and End Arrow Types.

This is the Format Error Bars task pane in Excel 2013; the Format Error Bars dialog in Excel 2007 and 2010 is substantially the same.

Format Error Bar Dialog

The error bar begins at the point, right? And ends at the, uh, end of the error bar, right? At least that’s what seems to make sense. So let’s pick a nice big round ball for the Begin Arrow Type.

Balls at Beginning of Error Bars

And let’s pick a nice big old arrow for the End Arrow Type. I’ve chosen the largest size for both ends of the error bar.

Arrowheads at End of Error Bars

And here’s our line chart with error bars, where the markers of the line chart could be replaced by the balls at the beginning of the error bars, and the arrows point away from the line.

Simple Line Chart with Fancy Ball and Arrowhead Error Bars

This is a very nice embellishment. I’ve used it in several projects already, and shown it to many people.

Anyway, back to the beginning of the story: the message from my colleague was that Excel 2010 and 2013 seem to define the beginnings and ends of the error bars differently.

Well, for positive error bars, Excel 2007, 2010, and 2013 all look the same.

Simple Line Chart with Simple Error Bars

But if we look at negative error bars, only Excel 2013 shows them beginning at the points and ending at the ends of the error bars, pointing downward. Excel 2007 and 2010 show the balls at the ends of the error bars and the arrowheads at the points, pointing upward. That’s bizarre.

Simple Line Chart with Simple Error Bars

Even more bizarre, if the error bars go both ways, only Excel 2013 has symmetric arrows. Excel 2010 shows both sets of arrows pointing upward, the negative ones toward the points, the positive ones away from the points. And Excel 2007 only has one set of arrows, beginning at the end of the negative error bars, ending at the ends of the positive error bars. Though if you had to, you could just pretend the end was the beginning and vice versa. Whatevs.

Simple Line Chart with Simple Error Bars

Excel 2007’s charting infrastructure was finished quickly at the end of the development cycle, so a few glitches weren’t ironed out of the final product. Excel 2010 fixed a lot of inconsistencies in Excel 2007’s charts, but these error bars show that not everything was totally fixed. In Excel 2007 or 2010 you could fake it with a second, hidden plotted series, and format two sets of error bars so they looked right. Excel 2013 has gotten the error bars working nicely, though.

Peltier Tech Chart Utility

Highlight a Specific Data Label in an Excel Chart

I was asked recently whether it was possible to change the font color of a data label in an Excel chart to highlight the maximum value.

Well, sure, anything is possible. And there are at least two ways to accomplish this task. Because I’ve been doing a lot of coding lately, my first thought was an approach using VBA. Then of course I came to my senses, and worked out a non-programmatic approach. If possible, it’s usually advantageous not to rely on VBA for such tasks.

The VBA Approach

Because I thought of it first, I’ll show the VBA method of formatting the label for the maximum value first.

Here is the simple data and chart, with all labels formatted with white text.

Highlight Max Data Label with VBA - Data and Chart 1

For this simple example, I want the tallest bar to have a black label, like this:

Highlight Max Data Label with VBA - Data and Chart 2

Here is the simple VBA routine I worked out to accomplish my task.

Sub HighlightMaxDataLabel()
  Dim srs As Series
  Dim vY As Variant
  Dim iPt As Long, nPts As Long
  Dim dMax As Double
  Dim iHighlightColor As Long

  ' do nothing if user hasn't selected a chart
  If Not ActiveChart Is Nothing Then
    Set srs = ActiveChart.SeriesCollection(1)

    ' highlight color: change to suit
    iHighlightColor = RGB(0, 0, 0)

    ' reset all labels to original font color
    With srs.DataLabels.Font
      .Color = .Color
    End With

    vY = srs.Values
    nPts = srs.Points.Count

    ' find maximum value
    dMax = vY(1)
    For iPt = 2 To nPts
      If dMax < vY(iPt) Then
        dMax = vY(iPt)
      End If
    Next

    For iPt = 1 To nPts
      ' highlight all labels at maximum value
      If vY(iPt) = dMax Then
        srs.Points(iPt).DataLabel.Font.Color = iHighlightColor
      End If
    Next

  End If

End Sub

When the data changes, the labels don’t immediately change.

Highlight Max Data Label with VBA - Data and Chart 3

Run the code again, and the labels are now properly highlighted.

Highlight Max Data Label with VBA - Data and Chart 4

Of course you could modify the code and stick it into a Worksheet_Change event procedure to make the labels change when the data changes.

I made sure that the code checked all values, without stopping at the first maximum. This way, both labels are highlighted if there’s a tie for first.

Highlight Max Data Label with VBA - Data and Chart 5

The Non-Programmatic Approach

Here is the same data with a couple extra columns, and the column chart without data labels. The added columns provide data for hidden line chart series which will contain the differently formatted data labels.

Assuming the data is in A1:D6, the formulas are:

Cell C2: =IF(B2<MAX(B$2:B$6),B2,NA())

Cell D2: =IF(B2=MAX(B$2:B$6),B2,NA())

These formulas result in only one of the two line chart series having a marker for each column of the column chart.

Highlight Max Data Label without VBA - Data and Chart 1

Start by making the chart using all of the data (left), or if you’ve already got the chart, add the extra series. Then change the chart type of the additional series* to line chart (right).

* right click on the series, choose Change Series Chart Type from the pop up menu, and select the desired chart type.

Highlight Max Data Label without VBA - Charts 2

Add data labels to each line chart* (left), then format them as desired (right).

* right click on the series, choose Add Data Labels from the pop up menu.

Highlight Max Data Label without VBA - Charts 3

Finally format the two line chart series so they use no line and no marker.

Highlight Max Data Label without VBA - Data and Chart 4

When the data change, the chart labels change just as quickly as Excel can calculate the new values in columns C and D. No need to hassle with VBA event procedures.

Highlight Max Data Label without VBA - Data and Chart 5

If more than one value matches the maximum, each will be highlighted as the maximum.

Highlight Max Data Label without VBA - Data and Chart 6

 

Peltier Tech Chart Utility

Color Plotted Points to Match Cells

This week in the Mr Excel forum, someone wanted to know how to set Graph Colors to match cell colors. A couple years back, my good buddy Mike Alexander presented code that Color Pie Chart Slices to Match their Source Cells. But I thought of a couple enhancements and I need to increase my posting frequency, so here goes.

Basic Data and Chart

Start with a simple data set:

Simple Data

Create a stacked column chart:

Simple Chart

This will work with stacked or clustered column charts, stacked or clustered bar charts, and pie charts.

Formatted Data and Chart

Enhancement Number One: Work on all reasonable chart types that use fill colors for each point.

Apply the fill colors to the cells that you want applied to the plotted points. Just fill colors; the VBA code will ignore borders and skip any cells that don’t have a simple “Solid” fill pattern.

Data with colored cells

Select the chart and run the procedure. The points (bars) will be filled with the same colors as the corresponding cells.

Chart with colored points

It works on bar charts too.

Chart with colored points

Note that the legend colors haven’t changed, because we’ve changed the points one-by-one, and haven’t changed the series. Even if you changed all points in a series to the same new color, the legend will still show the old color. See?

Legend colors do not change

Partially Highlighted Data and Chart

Enhancement Number Two: Apply fill colors only to certain highlighted points.

That was pretty cool. What if I only want to change the colors of some of the points, indicated by the selective colors of the following data set?

Data with highlighted cells

Look, it works! The program skips any cells with no fill color (in code, it skips cells with a fill pattern of “None”).

Chart with highlighted points

The VBA Procedures

The first procedure is a stub that is used to reformat the active chart.

Sub ColorActiveChartPointsToMatchCells()
  If Not ActiveChart Is Nothing Then
    ColorPointsToMatchCells ActiveChart
  End If
End Sub

You can call the main procedure inside of any other code to format a chart you’re working on, like this:

    ColorPointsToMatchCells ChartIAmWorkingOn

The second procedure accepts a chart as input. It cycles through all series in the chart. If the chart type of the series isn’t column, bar, or pie, it skips that series. Otherwise it parses the series formula to find the formatted range containing the series Y values. The code then loops through the points in the series (and the cells in the source data range), and if the cell has a simple “Solid” fill pattern, it uses the fill color of the cell as the fill color of the corresponding point.

Sub ColorPointsToMatchCells(cht As Chart)
  Dim srs As Series
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sYvals As String
  Dim rYvals As Range
  Dim iPt As Long
  Dim nPts As Long

  If cht Is Nothing Then GoTo OuttaHere

  For Each srs In cht.SeriesCollection
    Select Case srs.ChartType 
      ' only do pie, bar, column charts
      Case xlPie, xlBarClustered, xlBarStacked, xlBarStacked100, _
          xlColumnClustered, xlColumnStacked, xlColumnStacked100

        On Error GoTo SeriesError

        ' get series information
        sFmla = srs.Formula
        nPts = srs.Points.Count
        vFmla = Split(sFmla, ",")
        sYvals = vFmla(LBound(vFmla) + 2)
        Set rYvals = Range(sYvals)

        For iPt = 1 To nPts
          ' don't change point color if cell has no fill color
          If rYvals.Cells(iPt).Interior.Pattern = xlSolid Then
            srs.Points(iPt).Interior.Color = rYvals.Cells(iPt).Interior.Color
          End If
        Next
    End Select

SeriesError:
    On Error Resume Next
  Next

OuttaHere:

End Sub

Note that I used

    srs.Points(iPt).Interior.Color = rYvals.Cells(iPt).Interior.Color

to format the points of the series. The Interior property of a chart series has been deprecated, and the official syntax has changed to the much simpler and easier to remember

    srs.Points(iPt).Format.Fill.ForeColor.RGB = rYvals.Cells(iPt).Interior.Color

But it seemed to make sense to use Interior for both cell and point, especially in this case deprecated doesn’t mean “no longer works”.

Peltier Tech Chart Utility

Multiple Width Overlapping Column Chart

I read a post entitled Calling All Graph Wizards – Overlapping/Stacking Graphs w/o secondary axis on the Mr Excel forum today, and decided the question was broad enough and the answer quick and elegant enough that it was worth sharing.

The problem was that the user wanted to show projected and actual values of one variable as columns on the primary axis and of another variable as lines on the secondary axis.

If you only have the one variable, you can plot projected on the primary axis and actual on the secondary axis, then use a smaller gap width (wider bars) on the primary axis and a larger gap width (thinner bars) on the secondary.

Dual Column Width Chart Using Primary and Secondary Axis

But you can’t use two gap width settings if the columns must be plotted on the same axis. When you plot them, the taller bars in the front obscure the shorter bars in the back, so you can’t compare the values.

Mono Column Width Chart Using Only Primary Axis

But you can still make Excel do what you want. (In fact, you can almost always make Excel do what you want, if you know how.) I’ll describe two ways to accomplish this.

Option 1: Fill Bars with Rectangular Shapes

This approach was the original topic of this tutorial.

Draw two rectangles, pretty tall. The taller the better for visual quality of the chart. Make the thinner rectangle the color you want, and make the thicker one transparent. Make the widths of the two rectangles in the same proportion as the widths you want for the bars in the chart. Center the two rectangles horizontally.

Rectangles for Dual Column Width Chart

Select both rectangles, and copy (Ctrl+C). Select the series in the chart, and paste (Ctrl+V). The chart now uses the copied shapes as the fill for the selected series.

Dual Column Width Chart Using Only Primary Axis

Pretty easy, once you know how.

This comes in handy too if you need more than two widths. Without this trick, you couldn’t make the following chart even if you could use the primary and secondary axes:

Triple Column Width Chart Using Only Primary Axis

This requires two pairs of rectangles, a clear one and a relatively wide filled one for the second series, and a clear one and a relatively narrow filled one for the third. These are shown below:
Pairs of rectangles for bar chart fills

The advantage of this approach over the next is that the narrower bars keep their relative width, which is fixed by the ratio of filled rectangle to transparent rectangle used to fill the bars. The disadvantage is that if you want to adjust the width of the narrower bars, you need to adjust the width of the rectangles, then copy and paste onto the chart series.

Option 2: Error Bars with Multiple Widths

My colleague Andy Pope has pointed out in the comments that another approach for this effect is to use error bars for the narrower bars. In Excel 2003 and earlier, you had few options for line width, but since Excel 2007, you can make lines of seemingly any arbitrary thickness.

I’ll show Andy’s technique for three sets of bars. It’s even easier for two sets of bars.

I’ll start with the original column chart, setting overlap temporarily to zero so the different sets of error bars don’t obscure each other. The first thing to do is hide the bars you want to display narrower, that is, use no fill color for them. I’ve kept a colored outline to show what’s going on.

Using Error Bars for Multiple Width Chart Series Bars

Add error bars to the series you want to show as narrower bars.

Customize the error bars using the Minus Only, No Caps, and 100% Percentage value options.

Using Error Bars for Multiple Width Chart Series Bars

Now comes the magic. Apply the desired line colors to the error bars, and make the error bar lines thicker. Here I’ve used 11.25 pt for the orange bars and 5.75 pt for the blue bars.

Then I’ve hidden the outlines of the original bars.

Using Error Bars for Multiple Width Chart Series Bars

Change the overlap back to 100 so the bars are all centered on the categories (the month labels).

This has made the unchanged bars for Plan much wider, so we need to adjust the line widths of our error bars. I’ve settled on 30 pt for the orange and 12 pt for the blue.

Using Error Bars for Multiple Width Chart Series Bars

I used a little trick to reapply the bar colors to the legend. When I made the bars transparent, I started with the entire series formatted with the desired color. Then I selected one bar at a time instead of the entire series, and used no fill for the bar. Working point-by-point in this way leads Excel to believe that the series as a whole has not been changed, so it leaves the legend entries alone.

The disadvantage of the error bar approach is that  any reformatting that changes the widths of the original bars (changing overlap, adding points to each series, stretching the chart) will distort the relative widths of the error bars and the original bars.

The advantage is that these widths can be adjusted very easily by formatting the error bar lines, without having to fiddle with the widths of the rectangles which must then be copied and pasted onto the series bars.

Peltier Tech Chart Utility

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

Peltier Tech Chart Utility

 

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