Excel: Unexpected Scale with Combination Chart

• Microsoft Excel for the Macintosh, versions 1.0, 1.5 and 2.2

SUMMARY

When using combination chart number 2 or 3 (two axes) with positive and negative values, Microsoft Excel defaults to setting the category axis at the zero value of the main chart, or the left value axis. This means that regardless of where the values for the overlay fall, its zero value will not match the category axis of the graph.

For the category axis to match, the minimum and maximum values of both scales must be the same or a multiple of the same number. These values can be manually adjusted in the Scale dialog box. To adjust the scale, click the overlay value axis and choose Scale from the Format menu.

MORE INFORMATION

Below is an example of how to make the minimum and maximum values proportional:

Open a new Microsoft Excel worksheet and type the following values:

   In cells A1:A5, type 1,5,-5,-8,9
   In cells B1:B5, type 50,20,-300,125,76

The right value axis of the resulting chart crosses the category axis at -50. In Excel versions 1.50 and earlier, click the right value axis and choose Overlay Chart Axis from the Format menu. In Excel 2.20, click the right value axis and choose Scale from the Format menu.

Set the maximum value to 240 and click OK. Both axes will now cross at 0. This occurs because the maximum for the left value axis (8) times 30 equals 240, which is the same factor that is necessary to get the minimum value for the left value axis (-10) to equal the minimum value for the right axis (-300).