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

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 properly display an overlay column, 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. The columns are now displayed appropriately.

To further enhance the appearance of the chart, 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.

back to the top