Individually Formatted Dual Category Labels

In Chart with a Dual Category Axis I showed how to make a chart with dual category labels (two rows of labels). In Individually Formatted Category Axis Labels I showed how to format axis labels individually by ignoring the built in labels and using an invisible series with individually formatted data labels. An attentive reader asked how to format the individual labels in a dual axis category axis, and the answer is: combine these two methods.

We’ll use the same data as in the original post.

Make a column chart using the data in columns C:E, ignoring the axis labels for now.

Format the axis to hide the default axis tick labels (but remember the sequence: 1, 2, 3, etc.).

Here is the data for the dummy (hidden) series that will provide our labels. The first block is for the puter labels: the X values are in column G, corresponding to the slots in the category axis which were numbered for us in the first chart above; the Y values (zeros) are in column H; and the labels are in column I. I’ve kept all of the rows in this first block just so the labels were aligned and I could use simple formulas.

The numbers 2, 6, and 10 in column G refer to the slots that these labels will be centered under; if any of the groups had an even number of points, we would have had to center the outer labels between two axis slots, by using an X value of (for example) 9.5. Since we’ll be making XY series of these sets of data, we could use non-integer X values if necessary.

The labels use a simple formula to insert a line feed before the text of the original label; the line feed positions these outer labels below the inner labels. The formula in cell I2 is

=CHAR(10)&A2

and this is copied down as needed. It can be used in all cells, since a line feed plus an empty string will not look like anything in the chart.

The second block, for the inner labels, is configured in the same way as the first.

Add the first dummy series by copying G1:H12, selecting the chart, and using Paste Special to add the data as a new series. The new series is not visible in the chart, because it is added as another stacked column with values (heights) of zero.

Select a visible series in the chart, and use the up arrow until the added series (“Outer Labels”) is selected. Right click on one of the selection indicators, and if the context menu has an item called Format Data Series, choose Chart Type, and select the XY type with markers but no lines. The series is indicated below as large red squares.

Excel helpfully placed the XY series onto the secondary axis. Select the XY series, press CTRL+1 (numeral one) to open the Format Series dialog, and change its axis to Primary. See how well it is aligned.

Now add data labels to this series. The easiest way is to download and install Rob Bovey’s Chart Labeler. The Chart Labeler is free and easy to use. It installs a submenu to the Tools menu, as shown below (in Excel 2007 it adds an item to the Add-Ins ribbon tab).

In the dialog, select the series, select the range containing the labels, and select the position of the labels.

Here is the chart with the outer labels.

Add the second series. Copy K1:L12, select the chart, and use Paste Special to add this data as a new series. Since the previously added series was converted to an XY series and moved to the primary axis, this chart is conveniently pasted as an XY series on the primary axis, indicated by green diamonds below.

As above, use the Chart Labeler to label the Inner Labels series.

Here is the chart with all category labels showing.

Hide the XY series by formatting them to show no lines and no markers.

Select an individual label (the first click selects the series of labels, the second selects the label itself), and format it as desired. When one label is selected, you can use the left and right arrow keys to move to adjacent labels. In Excel 2003 and earlier, the F4 key will assign the last applied format to the selected label.

Here is the chart with its category labels formatted to match the plotted data. The color used for the font is a darker variation of the fill color used for the columns; it looks like these could be even a bit darker.