Creating Paired Comparison Charts in Microsoft Excel

Introduction

A paired comparison chart is used to compare pairs of experimental values over a set of different conditions. The paired comparison chart below has a pair of data points at each of three conditions. The chart is part of a larger study examining the direction of plant species interactions (vertical or Y axis) across an estuarine salinity gradient (salt, brackish, or oligohaline marshes, along the category or X axis) and with and without small mammal herbivores (pairs of points). The numbers beside each point indicate the number of observations.

Data provided by Caitlin Mullan Crain, PhD
Postdoctoral Researcher, UC Santa Cruz and The Nature Conservancy's Global Marine Initiative

The Approach

At first glance this seems like a simple chart to produce. The data is shown in the simple table below (error bar data is not shown here for simplicity).

Let's start with an XY chart. Select the data, and use the chart wizard to create the chart (shown below, after a bit of reformatting). On inspection, we can see some problems with this chart. Most important, we have lost the salinity labels that we wanted along the bottom of the chart. This is because an XY chart needs numerical data for both X and Y values. In the absence of numerical X values, Excel uses the counting numbers 1, 2, 3, etc.

Since the XY chart didn't work, let's try a line chart. We know that line charts treat the X values as nonnumeric labels (even if the X values contain numbers). Change the chart to a line chart (select the whole chart and use Chart Type from the Chart menu), or start over and select a line chart type in step one of the chart wizard. The line chart shown below displays salinity labels, but there is no lateral separation between the points within each pair. The overlapping error bars are too difficult to distinguish, and the chart is not as clean as we would like.

The lateral separation we want is achievable using a column chart, as shown below. However, this chart type requires much larger shapes to display each data point (a huge rectangle instead of a small marker), and is not what we are looking for.

We will use a combination chart to achieve the chart appearance that we want. We will start with a column chart with a dummy series of zero values, to provide us with the salinity labels we need across the X axis. For the actual data we will add XY series, using numerical X values. If an XY series is plotted along a category axis, the X values are scaled as if the first category is at 1, the second at 2, etc. The XY series allows us to laterally separate the points, by subtracting a small amount from the X values of one series, and adding a small amount to those of the other series.

The Data

The table below shows how the data is adjusted to facilitate creation of our chart, color coded to help with this description. The yellow range contains the data for the dummy column chart series, consisting only of text labels and zeros.

Row 4 contains the raw X values needed to plot the XY series in the chart, and cell B5 contains the stagger amount. The green and blue ranges show the X and Y values for the two XY series. The first row of the green range is calculated by subtracting the stagger value from the raw X values (the formula in B8 is =B4-$B$5). The first row of the blue range is calculated by adding the stagger value to the raw X values (the formula in B13 is =B4+$B$5).

Below each colored XY data range are two additional rows, the first containing the 95% confidence interval values, which will be used for error bar length, and the second containing the number of observations, which will be used for the labels on each data point.

The Chart

The protocol for building this chart is not too complicated. Start by selecting the yellow range of cells (A1:D2) and creating a column chart. The data points (columns) are not visible becuase their values are all zero. Format the plot area to have a white fill and no border. Changed the line color of the horizontal gridlines to the lightest gray in Excel's default palette.

The data including error bars ranges from -1 to +1, so format the scale of the Y axis with a minimum of -1 and a maximum of +1. Keep the axis crossing at zero (the automatic setting), but to move the salinity labels out of the way, format the X axis by selecting the Low position for the tick mark labels (double click the axis and select the Patterns tab).

Add the first XY series to the chart. Select and copy the green range of cells (A7:D8), select the chart, and use Paste Special from the Edit menu; Select the New Series, Series in Rows, Categories in First Row, Series Names in First Column options. Excel adds the series as another column series.

Change the new series to an XY series. Select the series, and choose Chart Type from the Chart menu. Select XY (Scatter) from the list of chart types on the left, and choose the subtype with markers and straight lines. Notice that Excel has placed the series onto the secondary axes, and the new axes appear on the top and right edges of the chart.

Put the XY chart onto the primary axis. Double click on the series, and on the Series tab, select Primary. You could instead rescale the secondary X axis to a minimum of 0.5 and a maximum of 3.5, and the secondary Y axis to match the primary Y axis, and then hide both secondary axes, but it's easier to create and maintain the chart if it uses only the primary axes.

Format the series. Double click the series and on the Patterns tab, choose anything but the ugly default pink lines and markers.

Add the error bars. Double click the series, and select the Y Error Bars tab. Click in the Custom (+) box, and select the 95% CI range (B9:D9) with the mouse. Click in the Custom (-) box and select the same range.

Add the second XY series to the chart. Select and copy the blue range of cells (A12:D13), select the chart, and use Paste Special from the Edit menu; Select the New Series, Series in Rows, Categories in First Row, Series Names in First Column options. Fortunately, Excel remembers that the last series we added was changed to an XY chart and was plotted on the primary axis, so it adds this series as another primary axis XY series.

Format the new series. Double click the series and on the Patterns tab, choose something more appropriate than the default yellow lines and markers.

Add the error bars. Double click the series, and select the Y Error Bars tab. Click in the Custom (+) box, and select the 95% CI range (B14:D14) with the mouse. Click in the Custom (-) box and select the same range.

Remove the unwanted Axis legend entry. Select the legend, then click on the label (not the colored symbol) for Axis, then press the Delete key.

If desired, adjust the legend. When you widen the legend, watch the faint dotted lines that separate the legend entries. Eventually it will reach a width where two entries will fit side by side rather than one above the other. Shrink the height of the legend accordingly, move it into position, and widen the plot area.

If desired, apply data labels to the data points of the scatter series. While custom labeling of dummy series points can be done manually, this tedious task is made much easier by using Rob Bovey's Chart Labeler, a free Excel add-in available for download at http://appspro.com, or John Walkenbach's JWalk Chart Tools, another free Excel add-in from http://j-walk.com/ss/excel. These utilities are easy to install and easy to use.

A few more formatting steps help improve the readability of this chart. Even using the lightest gray in Excel's default color palette, the gridlines are too dark. You can redefine one of the unused colors in the palette (such as the ugly olive green) to a very light gray. Choose Options from the Tools menu, click on the Colors tab, select the color you want to change, and click Modify. On either of the tabs, define a new color for the unwanted palette entry. Everything in the workbook that used the old color is now changed to the new color. This changes the palette of the active workbook only. From the Color dialog, you can also import the palette of another open workbook.

Select this new ultralight gray for the gridline color. The light gray previously used for the gridlines can now be used for the X axis, and the Y axis is hidden altogether. Double click on the axis, and on the Patterns tab, choose None for Line. You can remove the series lines that connect the markers. Finally, for the No Herbivore series, change all the elements from black to medium gray: marker foreground color, error bar line color, data label font color. You can even change the legend entry font color. Single click twice on the legend entry, first to select the whole legend, then to select the individual legend entry, then change the font color.

Jon Peltier is president of Peltier Technical Services, Inc., which provides custom Excel solutions to help clients in engineering, finance, marketing, and other fields analyze, visualize, and understand information more quickly and more thoroughly.

Peltier Technical Services uses the power of Excel's VBA programming environment to integrate Excel's worksheet functionality and charting capabilities with the presentation and reporting abilities of PowerPoint, Word, and other applications. The company web site houses one of the most extensive collections of information about Excel charting, including tutorials, tips, special techniques, and sample VBA code for generating customized charts and charting features.