Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel 2007 help? Graphing data

Apr 21, 2009 9:51AM PDT

How do i get Excel (2007) to graph one set of data on the X axis and another on the Y axis, instead of showing two separate lines? There should only be one line, relating the two sets of data.

Discussion is locked

- Collapse -
Use the Chart Wizard?
Apr 21, 2009 9:34PM PDT
Tutorial

Just a note though. Perhaps I misunderstood your post, but any graph needs an X axis and a Y axis to produce a one line graph.

I'm not sure how one set of data for the X axis and one set of data for the Y axis is producing a 2 line graph for you.

Mark
- Collapse -
2007
Apr 23, 2009 4:55AM PDT

Its different in 2007, there is no "Wizard", its automated. Meh, i geuss i'll do it by hand.

- Collapse -
My bad.
Apr 23, 2009 5:40AM PDT

You're right, and my error. You clearly said Excel 2007 and the link I gave was for Excel 2003, so my apologies for that.

Like you say, there doesn't seem to be a chard wizard in Excel 2007. instead the steps are governed by the ribbon menus that appear when you take certain actions. I pulled this from one site I found;

"The Chart Wizard

A full discussion of Excel's many charting options is beyond the scope of this handout. However, it is fairly straightforward to create a simple chart (and some complicated ones) using the chart wizard. Just highlight the data you wish to base your chart on (including header rows, if you have any) and click on the appropriate chart button under the Data tab. If you use the old F11 shortcut, a chart will be created automatically for you.

Once the chart is created, a new Chart Tools section is added to the right side of the Ribbon. The three tabs therein (Design, Layout, and Format) allow you to make different kinds of changes. Roughly speaking, Design allows you to make changes to the basic options regarding the chart (whether it's a worksheet or an object, what kind of chart is it, etc.), Layout allows you to add/edit/delete chart items like the title, legend, and so on, and Format allows you to change the appearance of the chart."

It still mentions the chart wizard but I don't believe it appears the same as the old wizard use to do. I hope that helps.

If not, can you tell us more about the particular problems you are having?

Mark

- Collapse -
Thanks
Apr 23, 2009 5:50AM PDT

I don't have the data on my at the moment, so i don't know if that will fix it, but thanks for the post.

The specific problem i have is that i have two sets of data, and i want to graph one vs the other (x and y) to compare the two. However, when i put in the two sets of data in two columns and highlight them and make a graph, it graphs two lines instead of comparing one to the other. I need one set of data to represent all the "X" values and the other to represent all the "Y" values, so that i can see a correlation between the two.

- Collapse -
OK, but that sounds normal to me.
Apr 23, 2009 6:15AM PDT

You have two axis, the X axis and the Y axis;

X axis - Horizontal. This holds the data that is static for the experiment or the comparison, eg time, dates, days of the week, etc.

Y axiz - Vertical. This holds the data that you see changing as the experiment or comparison proceeds.

So highlighting two columns of data to make a graph, Excel automatically assumes one is the x axis and the other the y axis.

In older Excel charts, the y axis data is called "Series", and if you want to plot multiple lines over the same range, (eg time, dates, etc), you needed to add a new series.

In your case you want to plot one set of data against the other, to compare the two lines. But those two sets of data seem to me to be "Series". You still need the range over which the two sets are measured, eg the x axis.

Since you are not where the data is, this may be difficult to follow and try out until you get back, so good luck wen you can do that.

Mark

- Collapse -
Hmm
Apr 23, 2009 6:22AM PDT

I think you may have the situation reversed there. Excel is automatically making TWO lines, but i want it to make ONE, like you said with one being the X and one being the Y.

- Collapse -
Yep.
Apr 23, 2009 6:27AM PDT

You're right again. Perhaps I should take lessons from you, and not the other way around, Happy

I just found another link, this time specifically for making charts in Excel 2007;
http://www.homeandlearn.co.uk/excel2007/excel2007s3p2.html

Compare that with what you are doing, and tell us what is different, if anything.

That tutorial makes a bar chart, but I assume that the same procedure applies for a line chart.

Mark

- Collapse -
Still no luck
Apr 23, 2009 7:49AM PDT
- Collapse -
Well.
Apr 23, 2009 8:54PM PDT

I 'think' I see the problem, but I am having as much difficulty as you, (using Excel XP 2002s chart wizard), that you are.

But this is what I am seeing;

1] You need to sort the "Period" into ascending order, (along with the 2π√(M/k) column of course).

2] The Period is not 0.952 to 1.98. It is 0 to 2 and that is what the X axis should be showing. You can of course choose any start point and end point that you wish, but the X axis has to be divided into equal segments.

Assuming that the Period is 'Time', (which is why it has to be sorted into ascending order), the Y axis points are plotted along the X axis, at the appropriate points along that axis.

3] You realize that the differences between each Y axis value is the same? Starting with the 2nd lowest 2π√(M/k) point, deduct the lowest and you get 0.1012. The same for each point.

I thought that would produce a straight line, but of course it doesn't because the periods between each Y axis reading is different.

A "Eureka" moment? I think that is the problem. You've taken, or obtained, the Y axis readings at non-standard points along the X axis. It seems to me that the X axis has to be regular points along it, then the readings for the Y axis are what they are at "specified points" along the X axis.

4] I've just tried this. Swapped over the Y axis data and X axis data so that, now, the 2π√(M/k) readings are the X axis and the Period data is the Y axis. This is what I get using the Chart wizard.

http://img13.imageshack.us/img13/9026/mychartv2.jpg

You will see the various data columns I tried. But the columns starting at B14 and C14 are the ones I got to work. You will see the "Source Data" dialog as well

But one thing I had to do. I didn't highlight any of the data before creating the chart. I opened the Chart Wizard first and entered each axis data myself. In Excel 2007 there is no Chart Wizard, so I assume you have to goto Insert first then locate the Charts panel. I don't know how easy that will be.

That worked for me, but whether it is what you want only you can say.

Mark

- Collapse -
Thanks
Apr 24, 2009 4:57AM PDT

I've gotten it to work, thanks for helping me out!

- Collapse -
Great news.
Apr 24, 2009 5:20AM PDT

And if I helped at all then you're welcome.

If you sorted it out yourself, that's even better, and congratulations.

Mark

- Collapse -
Help!
Nov 7, 2010 1:17PM PST

How did you do this? I have a lab report due soon, and I am incredibly frustrated that my Excel 2007 can't do a task as simple as this.

- Collapse -
Are you sure that's the right way to say it?
Nov 7, 2010 7:20PM PST

"Excel can't do a task as simple as this". Isn't there even a small possibility that the correct expression would have been "I can't do a task as simple as this with Excel 2007". That's even more frustating.

Personally, I've always (from Excel 95 onward) found making and customizing a graph in Excel one of most difficult, counterintuive and incomprehensible things in that program (there are others, like array formulas and using the analysis toolpack). And I doubt if Office 2007 and 2010 made a positive difference. So all I can offer you:
http://office.microsoft.com/en-us/excel-help/CH010064816.aspx
http://www.google.com/search?q=microsoft+excel+2007+graphs+tutorial

Best of luck with your lab report.

Kees