Bar Charts

VBA Conditional Formatting of Charts by Category Label

Monday, March 3, 2008 by Jon Peltier 176 Comments

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

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.

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 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

Tornado Charts and Dot Plots

Sunday, March 2, 2008 by Jon Peltier 39 Comments

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

Tornado charts are often used to compare distributions, in the form of back-to-back histograms. Tornado charts are particularly popular for comparing closely related populations, such as male and female age distributions. In this context they are known as “population pyramids”. Tornado charts are attractive, but I don’t think they are as effective as they at first seem to be. To illustrate this point, I went to the US Census Bureau and found the following age data for males and females:

We can make the two histograms from this data, one for the female population and the other for the male population.

Histogram of US female population by age group

Histogram of US male population by age group

The histograms present the data in separate charts, which makes them tricky to compare, while the vertical age group labels on the horizontal axis are difficult to read. A tornado chart combines the data by rotating these two charts so their categories are arranged vertically, and placing them back to back.

Tornado chart of US male and female population by age group

Although the category labels are now horizontally oriented, they are still difficult to read because they are partially obscured by the bars they overlay. This is often fixed by putting the labels on the side of the chart, which unfortunately puts them close to only one of the populations.

This is also sometimes addressed by splitting the chart halves and inserting the labels between the two populations. This is probably the best of the tornado chart options, because the labels are located at the baselines of each distribution.

While a tornado chart seems like a good approach for comparing similar distributions, I am always somewhat distracted by the optical illusion in which an image seems to switch between a goblet and two profiles in silhouette.

Source: AVIV – optical illusions – brain teasers – riddles

Another problem is that the two populations are more difficult to compare than we think when we view the chart. In the last tornado above, we can clearly see the greater female population in the oldest age groups, and we can see the baby boomer peak in the 45-49 age group. We cannot easily detect the differences between any age groups younger than about 60-64, because the bars move away from each other.

This problem can be solved by putting the bars on the same side of the category axis, in a clustered bar chart. We can easily see the differences between male and female populations in all age groups. In groups up to 30-34, the males have greater numbers; at 35-39 the numbers are very close to the same, and from 40-44 upwards, females have larger numbers, and the differences increase with age. An advantage is that the chart shows the populations with the same resolution as above in less space, because the data only extends in one direction from the category axis.

Clustered bar chart of US male and female population by age group

The problem with the clustered bar chart is that the alternating bar colors disrupt the smooth display of the distributions, and in some cases the distributions may be made to appear offset, because of the placement of the bars side-by-side rather than one atop the other. Jorge Camoes has written about population pyramids in How-to screencasts: Population pyramids in Excel and Animation and demographic information visualization. Jorge also thinks these charts are not as effective as they might be, and he suggests the use of overlapping bars, with one displayed as an outline rather than a filled bar. This approach gives a better impression of smooth distributions than the clustered bar chart above, but the different formatting gives different emphasis to the filled bar and outlined bar. The two charts below show the same data, but the initial impressions each gives are different.

Overlapped bar chart of US male and female population by age group

A different choice of colors may improve the uniformity of the two series, but not completely. A black line and a light gray fill give the series more equal weight than the blues and reds above.

I’ve written about Dot Plots in Microsoft Excel on my web site, where I’ve also linked to other sources: Good Graphs for Better Business, by William S. Cleveland and N.I. Fisher, Dot Plots: A Useful Alternative to Bar Charts, by Naomi B. Robbins, Ph.D., and Compare Metrics by Category Using Excel Dot Plot Charts, by Charley Kyd. Jorge Camoes also finds them a suitable replacement for population pyramids in Population pyramids in Excel. This is a good data set to display in a dot plot. The lines clearly show the distributions for both populations, as well as the relative populations in each age range.

Dot plot of US male and female population by age group

Posted: Sunday, March 2nd, 2008 under Chart Types.
Tags: Bar Charts, Column Charts, Dot Plots, population pyramid, Tornado Charts.
Comments: 39

Microsoft MVP Logo

Conditional Stacked Clustered Column Chart with Targets

Monday, February 18, 2008 by Jon Peltier 4 Comments

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

In the microsoft.public.excel.programming newsgroup, ptheese asked for the following:

I need to chart three groups of two bars on a axis. -Each group is a year, 2007, 2006, 2005 etc so we can compare data from previous years -in each group of two there is a profit vertical bar and a combined ratio percentage bar, these bars are not related to each other (one going up has no impact on the other) -For each of these bars, there needs to be a target indicator, a small horizontal line crossing over the bar (or above it if the target was not hit). This line does not go across the entire chart, there is a separate one for each individual bar. -For the profit bar, if it crosses above the target bar, it should be green. otherwise red. -For the ratio/percentage bar, if it crosses above the target bar, it should be red, otherwise green

I’ve come up with the following chart:

Simple Column Chart

I don’t have the particular data, but presumably it’s something like this:

	A	B	C	D	E
1		Profit	Ratio	Profit Target	Ratio Target
2	2007	26	12	25	14
3	2006	20	16	20	15
4	2005	16	20	18	16

If I created a chart using A1:C4, I’d get something like the following, but I’d have to manually change the colors and somehow draw lines to represent the targets.

Simple Column Chart

First we need to fix up the data, then we can build the chart. Since we want the bars to change colors automatically, we need stacked columns (see the conditional charts tutorial). Since we want clustered stacked columns, we need to stagger the data and insert appropriate blank rows (see the link to clustered stacked column charts). The data range will require one row for column headers, a blank row for the space to the left of the first cluster, two rows for the first cluster of two columns, a blank row for the space between clusters, two rows for the middle cluster of two columns, a blank row for the space between clusters, two rows for the last cluster of two columns, and a blank row for the space to the right of the last cluster. I’ve set up the data as shown below:

	A	B	C	D	E	F	G	H	I	J
1			Profit	Ratio	Profit Target	Profit Green	Profit Red	Ratio Target	Ratio Red	Ratio Green
2	a	a
3	2007	Profit	26		25	26	0
4		Ratio		12				14	0	12
5	a	a
6	2006	Profit	20		20	20	0
7		Ratio		16				15	16	0
8	a	a
9	2005	Profit	16		18	0	16
10		Ratio		20				16	20	0
11	a	a

The “a” in some cells in columns A and B are temporary placeholders, and will be hidden later. The actual data in columns C and D will not be plotted but are used in calculations. There are formulas in columns F, G, I, and J as follows:

F3: =IF(C3>=E3,C3,0)

G3: =IF(C3<E3,C3,0)

I4: =IF(D4>=H4,D4,0)

J4: =IF(D4<H4,D4,0)

I’ve color coded the cells in the table above. The blue cells are the actual plotted Y values, the green cells are used by the chart for series names, and the purple cells are used for the chart’s category axis labels. The yellow are not used in the chart, but they help Excel parse the data into Y values, series names, and category labels.

Now we can start working on the chart. Select the cells I’ve shaded in the table. Select A1:B11, then hold Ctrl and select E1:J11, so that both areas are highlighted. Create a stacked column chart:

Simple Column Chart

The first thing I’ve done is colored the series: red and green as appropriate for the Profit and Ratio series, and blue for the targets.

Simple Column Chart

The target series don’t help us as columns. We’ll convert them to XY series, add X error bars, and hide the markers. First, right click the Profit Target series, select Chart Type, and select the XY type (not a Line type, because this type has no X error bars, only Y error bars).

Simple Column Chart

Do the same for the Ratio Target series. If you don’t do anything after changing the type of the first series, you can simply select the second and use the F4 shortcut key, to repeat the last action.

Simple Column Chart

Right click on the Profit Target series, select Format Data Series, and on the X Error Bars tab, choose both directions, Fixed Value, and enter 0.5 in the box next to fixed value.

Simple Column Chart

Do the same for the Ratio Target series.

Simple Column Chart

Now you need a little formatting.

Delete the legend.
Format the error bars so they are thicker and don’t have the end caps. Color them if desired.
Format the target series so they have no markers as well as no lines.
Reduce the column gap width if desired (I use 50% in this chart).
Change “a” in the table above to ” ” (a space character). Don’t completely clear the cells, or the category labels will be messed up.

Simple Column Chart

This chart isn’t too bad, and it meets the specs. But not knowing the particular usage, I think it makes more sense to put the profit values together and the ratio values together, as in the next chart. I’d also make a few other formatting changes; I might even be tempted to use area charts for the targets and line charts for the actual values, and let the reader see for himself which is good or bad without color coding the results.

Simple Column Chart

Posted: Monday, February 18th, 2008 under Example Charts.
Tags: Bar Charts, Column Charts, Combination Charts.
Comments: 4

Microsoft MVP Logo

Bar Charts

Related Articles about VBA Conditional Formatting of Charts

Articles about Non-VBA Conditional Formatting of Charts