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:
| 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.
|
Back to the top
Help and Support
