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

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.

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:

1. 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
   					

2. With the text laid out as in the second worksheet example, select cells A1:E11.
3. Click the Chart Wizard button on the standard toolbar.
4. 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.
5. In the chart, click the fourth data column ("Total Sales").
6. On the Format menu, click Selected Data Series.
7. On the Axis tab, click Secondary axis, and then click OK.
   
   Notice that the columns are now displayed appropriately.

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:

1. 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
   					

2. With the text laid out as in the second worksheet example, select cells A1:E11.
3. On the Insert tab, click Column in the Charts group, and then click Clustered Column.
4. In the chart, right-click the fourth data column ("Total Sales"), and then click Format Data Series.
5. 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.

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.