XL: How to Create a Column Chart with Two Y Axes

ID: Q121819

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

SUMMARY

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

MORE INFORMATION

To work around this problem, follow the procedure below.

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

use the following format:


   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

In Microsoft Excel 97

To work around this problem in Microsoft Excel 97, follow these steps to create the sample chart:

Using the second layout, select cells A1:E11.

Start the ChartWizard. In step 1, click Column under Chart Type, and click the first Chart Sub-type. Click Finish.

In the chart, click the fourth data series ("Total Sales").

On the Format menu, click Selected Data Series.

On the Axis tab, click Secondary Axis and click OK.

The columns will now be displayed appropriately.

In Microsoft Excel version 5.0

To work around this problem in Excel 5.0, follow these steps to create the sample chart:

Using the second layout, select cells A1:E11. Start the ChartWizard, and select Column Chart view 1 in Step 2.

After the chart has been created, double-click the chart to put it in edit mode.

Select the fourth data series ("Total Sales").

On the Format menu, click Selected Data Series.

On the Axis tab, click Secondary Axis.

The columns will now be displayed appropriately.

To enhance the appearance of the chart further, follow these steps:

On the Format menu, click Chart Type.

Click the Options button, and then click the Options tab.

Set the Gap Width to a smaller number, so that the last column fills its area to match the other series.

In Microsoft Excel version 4.0

To work around this problem in Excel 4.0, follow these steps to create the sample chart:

Using the second data layout, select cells A1:E11. Start the ChartWizard, and select Combination Chart view 2.

After a chart has been created, activate the chart window by double- clicking in the window.

On the Format menu, click Overlay.

Select Column as the Overlay Chart Type, and set the First Overlay Series to 4. Click OK.

The columns will now be displayed appropriately.

NOTE: To plot an overlay column next to a stacked column, you will want to stagger the data. This will create data with an overlay series that is "interleaved," and room will be left for the overlay in the gaps between the stacked series. This eliminates the overlap that results if you choose the series as contiguous rows and columns.

To enhance the appearance of the chart further, follow these steps:

On the Format menu, click Overlay.

Set the Gap Width to a smaller number, so that the last column fills its area to match the other series.

REFERENCES

For more information about Creating Charts , click the Index tab in Microsoft Excel 97 Help, type the following text

Charts, creating

and then double-click the selected text to go to the "Create a chart" topic.

"User's Guide," version 5.0, pages 265-276, 322-323
"User's Guide 1," version 4.0, pages 428-431

Additional query words:

Keywords : kbualink97 kbdta kbchart xlchart
Version : MACINTOSH:4.0,5.0,5.0a; WINDOWS:4.0,4.0a,5.0,5.0c,97; Win95:7.0; winnt:5.0
Platform : MACINTOSH Win95 WINDOWS winnt
Issue type : kbhowto