Non-contiguous ranges for chart

  • Thread starter Thread starter Mike Boardman
  • Start date Start date
M

Mike Boardman

Basically, my daughter's problem is this:

She needs to create a chart in Excel from the data below, which shows prices
for two areas (London and Provincial) across the years in the top row.

1834 1852 1862 1864 1865 1868 1869 1871 1874
London £68.05 £68.05 £68.05 £80.00
Provincial £63.00 £75.00 £75.00 £75.00 £82.10


This produces a chart with a line for each set of data but they are
effectively in one line.

She has put them in rows like this because she (and I!!!) am stuck for a
better way of doing it. What we would really like to do is have the two sets
of data on separate rows in such a way that they appeared one below the
other. I suspect that this may be inherently impossible because of the way
the data has to be structured, but I am hoping that someone will have a
bright idea that is currently eluding me.

TIA
 
You could plot one of the lines on a secondary x-axis --

Select a cell in the table, and click the Chart Wizard button
Select the XY (Scatter) chart type, and a subtype with lines
Click Finish

Select the Provincial series
Choose Format>Selected Data Series
On the Axis tab, select Secondary axis, click OK

Select the chart
Choose Chart>Chart Options
On the Axes tab, under Secondary axis, remove the check mark
from Value (Y) axis, and add a check mark to
Value (X) axis
Click OK

Select the secondary X axis, at the top of the chart
Choose Format>Selected axis
On the Scale tab, enter the minimum and maximum dates for the
Provincial data, e.g. 1865 as a minimum
(optional) On the Font tab, select a font colour to match the series
Click OK

Select the primary X axis, at the bottom of the chart
Choose Format>Selected axis
On the Scale tab, enter the minimum and maximum dates for the
London data, e.g. 1865 as a maximum
(optional) On the Font tab, select a font colour to match the series
Click OK
 
Back
Top