Need a chart with x-axis (date) data in two columns

GarrettS

New Member
Joined
May 27, 2010
Messages
15
I am trying to create a chart from four columns of data:

Column 1: Date
Column 2: First Data corresponding to the adjacent date in column 1
Column 3: Date
Column 4: Second Data corresponding to the adjacent date in column 3

I tried a scatter chart with two series, the first with column 1 as X axis and column 2 as Y axis, and the second series using columns 3 and 4, but instead of using the data in columns 1 and 3 for the x axis it uses the row number of columns 2 and 4.

How can I chart this correctly?

Thanks in advance.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can plot noncontiguous ranges by adjusting the series references to eg:

X Values =(Sheet1!$A$2:$A$10,Sheet1!$C$2:$C$10)
Y Values =(Sheet1!$B$2:$B$10,Sheet1!$D$2:$D$10)
 
Upvote 0

GarrettS

New Member
Joined
May 27, 2010
Messages
15
I'm not sure what you mean.

First I highlight all four columns and choose a scatter chart. The default is four series, two for the columns I intend to be plotted and two for the date columns.

I delete the date series and am left with my data correctly plotted on the y axis. The X Values field for both series' are left blank while the Y Values field is the single column the data is in: =$Q$2:$Q$11287 and for the other series: =$S$2:$S$11287

The X axis however goes from 0 to 11287. If I fill $P$2:$P$11287 into the X axis for series 1 and column R for X axis for series 2, the chart x axis goes from 1 to ~45000 but the data is still plotted from 1 to 11287. If I enter both of my data columns for the X axis data for either series, excel crashes.
 
Upvote 0

GarrettS

New Member
Joined
May 27, 2010
Messages
15
Sorry for the confusion, I think I poorly explained my data.

The data in the 2nd and 4th columns are different series. One is a bid and the other an offer. The dates in columns 1 and 3 cover the same date range but may be at different points during that range.

So 10am on Feb 20th, 2011 I want to be plotting the bid and the offer, but these points are not in the same excel row.
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need 2 series, eg:

Series1

X Values =Sheet1!$A$2:$A$10
Y Values =Sheet1!$B$2:$B$10

Series2

X Values =Sheet1!$C$2:$C$10
Y Values =Sheet1!$D$2:$D$10

and an XY Scatter chart.
 
Upvote 0

GarrettS

New Member
Joined
May 27, 2010
Messages
15
Thanks. This is what I have tried but instead of using the selected X Values range, Excel is still using the row number for the x value.
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: select the first two columns and plot an X-Y scatter chart. Select the second two columns and Copy. Activate the chart, Edit > Paste Special, tick New Series and X values in first column.
 
Upvote 0

Forum statistics

Threads
1,190,587
Messages
5,981,799
Members
439,735
Latest member
Tony_P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top