Formatting | Peltier Tech Blog

Color Vision Issues with Heat Map Donut Charts

Friday, January 25, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2013.

I’ve reviewed a colorful yet ineffective graphic in Wow! Heat Map Donut Chart!. One of the features that makes this graphic ineffective is its use of colors. While unfriendly to those with normal color vision, the colors used are particularly unfriendly to the 8% of men and 0.4% of women with color vision deficiencies.

Color Vision

In the normal human retina, their are four types of photoreceptors, neurons that detect light. One type, called rods, respond to low light levels, producing monochromatic vision in near darkness. In brighter light, three types of cones respond to incoming blue, red, and green light. The mechanisms of color vision are fascinating and miles beyond the scope of this article. For this discussion it is enough to know that our perception of color is a function of how these color receptors, other retina neurons, and the visual cortex of our brains process the signals from these cones.

Color blindness occurs when one of the three types of cones are partially or totally disabled. The most common color vision deficiencies are deuteranomaly and deuteranopia, respectively partial and total loss of function of the green photoreceptors. Protanomaly and protanopia are the partial and total deficiency of the red sensing neurons. Much less common than either of these conditions are tritanomaly and tritanopia, partial and total loss of activity of the blue cones.

Deficiencies in either the red or the green photoreceptors leads to difficulties distinguishing red and green. Ironically, the most common pair of colors used to indicate good and bad are red and green. Think of traffic lights with red and green lights indicating stop and go, and innumerable bad dashboards with arrays of red and green symbols. Better combinations would be read and blue, or orange and cyan (which must be familiar from the hundreds of Tableau graphics we’ve seen over the last few years).

Most mammals only have two types of photoreceptors, and have the type of color vision that protanopes and deuteranopes experience. Early during primate evolution, a mutation split one of these receptors into the green and red cones that provide richer color vision. Some reptiles, birds, and fish may have four, five, or even more types of cones, extending into the ultraviolet and infrared ranges of the spectrum we can only imagine.

Testing Images for Color Vision Issues

There are numerous programs and web sites that simulate the effects of color vision deficiencies on your images. One popular and easy-to-use site is Vischeck, which allows you to upload images, and uses various algorithms to convert them into representations of how they would look under various types of color blindness. The simulations here were generated using Vischeck.

Comparison of Heat Map Donut Chart Images

Here is the original heat map donut chart, in all of its visual glory.

Heat map donut chart under normal color vision

This is how deuteranopia (green cone deficiency) renders the donut.

Heat map donut chart by simulated deuteranope vision

This is how protanopia (red cone deficiency) renders the donut.

Heat map donut chart by simulated protanope vision

These two conditions result in similar images: a blob of blue in the top right, and a smear of yellowish brown elsewhere. The red-orange-yellow-green gradients are mostly eradicated, rendering the heat map nearly useless.

Comparison of Pie Chart Images

Here is my depiction of the heat map donut data, in a properly sorted pie chart, using the default Excel 2007/2010 color palette. In full color:

Pie chart under normal color vision

Here is the pie as seen by a deuteranope (simulated).

Pie chart by simulated deuteranope vision

Here is the pie as seen by a protanope (simulated).

Pie chart by simulated protanope vision

As above, the two color vision deficiencies result in similar simulations. Without a gradient, though, the wedge colors are much more readily distinguished. Labeling the wedges themselves does help greatly, as some color pairs are very close; a legend would not be so helpful.

It turns out that the default Excel 2007/2010 color theme is relatively friendly to viewers with color vision deficiencies.

Comparison of Bar Chart and Dot Plot Images

Here is a bar chart of the same data, shown in regular color vision and simulated deuteranopia (protanopia was omitted because it is less common and the effects are similar).

Bar chart under normal color vision

Bar chart under simulated deuteranopia

Here is a dot plot of the same data, shown in regular color vision and simulated deuteranopia (protanopia was omitted because it is less common and the effects are similar).

Dot plot under normal color vision

Dot plot under simulated deuteranopia

We can see that color vision deficiencies have no effect on the readability of bar charts and dot plots, because neither rely on color to encode quantitative information.

Recommendations

Graph types such as bar charts and dot plots again prove their superiority over pie and donut charts, in this case by not relying on colors to depict numerical data.

Gradients are particularly problematic when combined with color vision issues. In general, the guidelines for gradients are that they should proceed from a dark shade of a color to a light shade of the same color, or from a dark shade of a color through a light shade (close to white) to a dark shade of a distinct second color. The gradient used in the heat map example proceeded through highly saturated chades of several colors.

Colorbrewer is an online tool designed to help select good color schemes for maps and other graphics. You can select from among different styles of color schemes, and you can choose to generate only color-vision-friendly schemes. In conjunction with a colov-vision-deficiency simulator like Vischeck, you have no excuse for producing hostile graphics like the donut heat map.

Posted: Friday, January 25th, 2013 under Formatting.
Tags: color, color vision.
Comments: none

Column Chart Gridlines Cutting Through Bars

Friday, November 2, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2013.

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: Gridlines.
Comments: 12

Conditional Formatting of Lines in an Excel Line Chart Using VBA

Tuesday, September 11, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2013.

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.

Categorical XY Data with Highlighted Category Ranges

This combination chart is simple to make.

The Individual Val

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.

ues

We can plot the points as an XY Scatter chart series. This chart uses 1, 2, and 3 for categories A, B, and C.

XY Chart

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.

XY Chart with Jittered X Values

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.

XY Chart with Jittered X Values and Text X Labels

The Data

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.

Data for this tutorial

I’ve never encountered a useful jittering algorithm, so I fudged my own, which works well enough. The formula in H3 is

=G3+0.2*(RAND()-RAND())

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.

Stacked Column Chart

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.

Floating Column Chart

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.

Floating Column Chart with XY Data Added as Another 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.

Floating Column Chart with XY Data Points on Secondary Axis

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.

Floating Column Chart with XY Data Points on Primary Axis

Posted: Tuesday, September 11th, 2012 under Formatting.
Tags: conditional formatting, VBA.
Comments: 6

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

[Read more...]

Posted: Monday, February 13th, 2012 under Formatting.
Tags: conditional chart, conditional format, conditional formatting.
Comments: 84

Excel Charts With Horizontal Bands

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.