Microsoft KB Archive/214011

= How to create a column chart with two Y-axes in Excel =

Article ID: 214011

Article Last Modified on 2/8/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac

-



This article was previously published under Q214011



IN THIS TASK

 * SUMMARY
 * Create Chart with Two Y-Axes



SUMMARY
This step-by-step article shows you how to create a chart with two y-axes, so that both axes can be clearly seen.

In general, a combination or overlay chart must have a second y-axis. However, Microsoft Excel places the column series that is plotted on the secondary axis in front of the column series that is plotted against the primary axis. In many cases, the overlay series obscures or overlaps the primary series.

back to the top

Create Chart with Two Y-Axes
To plot an overlay column next to a stacked column, you need to stagger the data. This creates data with an overlay series that is interleaved, and room remains for the overlay in the gaps between the stacked series. This eliminates the overlap that results if you choose to arrange the series as contiguous rows and columns. To correctly display an overlay column in Microsoft Office Excel 2003 and in earlier versions of Excel, follow these steps:   Instead of laying the data out as follows:   A1:         B1:   South  C1:  East D1:   North  E1:    Total Sales A2:  ABC   B2:   10     C2:  5    D2:   12     E2:    27 A3:  DEF   B3:   20     C3:  15   D3:   24     E3:    59 A4:  GHI   B4:   30     C4:  25   D4:   36     E4:    91 A5:  JKL   B5:   40     C5:  35   D5:   48     E5:    123 A6:  XYZ   B6:   50     C6:  45   D6:   60     E6:    155 Lay the data out as follows:   A1:         B1:   South  C1:  East   D1:  North  E1:   Total Sales A2:  ABC   B2:   10     C2:  5      D2:  12     E2: A3:        B3:          C3:         D3:         E3:   27 A4:  DEF   B4:   20     C4:  15     D4:  24     E4: A5:        B5:          C5:         D5:         E5:   59 A6:  GHI   B6:   30     C6:  25     D6:  36     E6: A7:        B7:          C7:         D7:         E7:   91 A8:  JKL   B8:   40     C8:  35     D8:  48     E8: A9:        B9:          C9:         D9:         E9:   123 A10: XYZ   B10:  50     C10: 45     D10: 60     E10: A11:       B11:         C11:        D11:        E11:  155  With the text laid out as in the second worksheet example, select cells A1:E11. Click the Chart Wizard button on the standard toolbar. In the Chart Wizard - Step 1 of 4 Chart Type dialog box, click Column under Chart type, and then click the Clustered Column under Chart sub-type. Click Finish. In the chart, click the fourth data column ("Total Sales"). On the Format menu, click Selected Data Series.</li> On the Axis tab, click Secondary axis, and then click OK.

Notice that the columns are now displayed appropriately.</li></ol>

Note To enhance the appearance of the chart in Excel 2003 and in earlier versions of Excel, follow these steps:
 * 1) In the chart, double-click the overlay column.
 * 2) On the Options tab, set the Gap width to a smaller number so that the last column fills its area to match the other series.

To correctly display an overlay column in Microsoft Office Excel 2007, follow these steps: <ol>  Instead of laying the data out as follows: <pre class="fixed_text">  A1:         B1:   South  C1:  East D1:   North  E1:    Total Sales A2:  ABC   B2:   10     C2:  5    D2:   12     E2:    27 A3:  DEF   B3:   20     C3:  15   D3:   24     E3:    59 A4:  GHI   B4:   30     C4:  25   D4:   36     E4:    91 A5:  JKL   B5:   40     C5:  35   D5:   48     E5:    123 A6:  XYZ   B6:   50     C6:  45   D6:   60     E6:    155 Lay the data out as follows: <pre class="fixed_text">  A1:         B1:   South  C1:  East   D1:  North  E1:   Total Sales A2:  ABC   B2:   10     C2:  5      D2:  12     E2: A3:        B3:          C3:         D3:         E3:   27 A4:  DEF   B4:   20     C4:  15     D4:  24     E4: A5:        B5:          C5:         D5:         E5:   59 A6:  GHI   B6:   30     C6:  25     D6:  36     E6: A7:        B7:          C7:         D7:         E7:   91 A8:  JKL   B8:   40     C8:  35     D8:  48     E8: A9:        B9:          C9:         D9:         E9:   123 A10: XYZ   B10:  50     C10: 45     D10: 60     E10: A11:       B11:         C11:        D11:        E11:  155 </li> With the text laid out as in the second worksheet example, select cells A1:E11.</li> On the Insert tab, click Column in the Charts group, and then click Clustered Column.</li> In the chart, right-click the fourth data column ("Total Sales"), and then click Format Data Series.</li> In the Format Data Series dialog box, click Series Options, click to select the Secondary Axis check box, and then click Close.

Notice that the columns are now displayed appropriately.</li></ol>

Note To enhance the appearance of the chart in Excel 2007, follow these steps:
 * 1) In the chart, right-click the overlay column, and then click Format Data Series.
 * 2) In the Format Data Series dialog box, click Series Options, and then set the Gap Width to a smaller number so that the last column fills its area to match the other series.

back to the top

Additional query words: XL2000 XL2002, XL2003, XL2001, macXL2001, XLx, macXLx macXL2004, XL2004, XL2007

Keywords: kbtshoot kbprb kbexpertisebeginner kbchart kbhowtomaster KB214011

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.