A commonly used chart type for statistical analysis is a Main Effects Plot. I won’t go into the statistics behind this chart type here, but I want to show how easy it can be to construct such a chart. For this example, suppose there are three main effects, designated X1, X2, and X3, and an experimental matrix is designed such that each is set to either a high value (+) or a low value (-). The means of an output variable is calculated for the high and low input values, and these are placed on a chart.

To make the chart in Excel, set up your values as shown below. Cells C1 through E1 contain the names of the three main effects, and F1 contains “Mean”. A2, A4, and A6 also hold the main effects’ names, while B2 through B7 contain alternatively plus and minus signs. A1, B1, A3, A5, and A7 are blank. The mean for tests with factor X1 at its high level is in cell C2, and at its low level is in cell C3; the corresponding values for X2 and X3 are in D4:D5 and E6:E7. The overall mean is filled in F2:F7.

Select this whole range, A1:F7, run the chart wizard and create a line chart (Excel 97-2003) or go to the Insert tab and choose a line chart (Excel 2007). The blanks in A1:B1 tell Excel to use columns A and B for the two-row category axis labels, and row 1 as series names. The blanks in A3, A5, and A7 center the X1, X2, and X3 category labels under each pair of +/- category labels. Very little formatting is needed. I left the X1, X2, and X3 series with their default line and marker formats, and changed the Mean series to a dashed line with no markers.

This is another example of what Excel can do if you look beyond the defaults and beyond the standard chart types. It also validates one of my favorite expressions: if you spend five minutes with the data, you can save five hours of frustration.