One data series and two y-axes in an Excel chart?

  • Thread starter Thread starter kkarvinen
  • Start date Start date
K

kkarvinen

I would like to produce graph of a single data series such that the
left y-axis would represent the absolute value and the right axis the
relative values (in percents) of the data points. An example of such a
chart is the last graph on the page

<http://www.abs.gov.au/websitedbs/D3310116.NSF/0/c7e40ae1fa39e31e4a2567ac001ffb61?OpenDocument>

with cumulative frequency on the left axis and cumulative percentage
on the right axis.

Is it possible to create charts like these with Excel?
 
kkarvinen,

Yes, you can create a chart similar to your example chart by using an
XY-Scatter Chart.

As an example, to rebuild the chart on the web site set the data up like this:

a b c d
200 0 0 0
210 4 1 1
220 12 2 3
230 24 3 6
240 44 5 11
250 72 7 18
260 92 5 23
270 100 2 25

Please note that the data in column D is the cumulative values of column C.
Go through the following steps to build the XY-Scatter chart:

(1) Using the Chart Wizard, create a simple XY-Scatter chart based on
columns A and B (start with columns A and B only for now).
(2) Double-click on the X-axis and set the minimum scale to 200 and the
maximum scale to 270.
(3) Go to Chart -> Source Data -> Series -> Add. Set the X-axis values to
column A and the Y axis values to column D. Hit the OK button to get out of
the dialog box.
(4) Double-click on the new second series. In the Format Data Series
dialog box go to the Axis Tab and choose the “Plot Series on Secondary Axisâ€
option.
(5) Double-click on the primary Y-axes (left side of chart) and set the
maximum to 100, the minimum to zero, and the major unit to 20.
(6) Double-click on the secondary Y-axes (right side of chart) and set the
maximum to 25, the minimum to zero, and the major unit to 20.
(7) Click inside of the legend and delete the reference to Series 2.
(8) Double-click on the series. On the Format Data Series dialog box under
the Patterns tab set the Marker to None or customize it to look like the
marker on the first series. The effect is to make the points for Series 1
and Series 2 appear as one marker.
 
Back
Top