Office & Productivity Software forum

General discussion

Excel 2007 help? Graphing data

by tipoo_ / April 21, 2009 9:51 AM 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
You are posting a reply to: Excel 2007 help? Graphing data
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel 2007 help? Graphing data
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Use the Chart Wizard?
by MarkFlax Forum moderator / April 21, 2009 9:34 PM PDT

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.

Collapse -
by tipoo_ / April 23, 2009 4:55 AM PDT
In reply to: Use the Chart Wizard?

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

Collapse -
My bad.
by MarkFlax Forum moderator / April 23, 2009 5:40 AM PDT
In reply to: 2007

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?


Collapse -
by tipoo_ / April 23, 2009 5:50 AM PDT
In reply to: My bad.

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.
by MarkFlax Forum moderator / April 23, 2009 6:15 AM PDT
In reply to: Thanks

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.


Collapse -
by tipoo_ / April 23, 2009 6:22 AM 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 -
by MarkFlax Forum moderator / April 23, 2009 6:27 AM PDT
In reply to: Hmm

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;

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.


Collapse -
Still no luck
by tipoo_ / April 23, 2009 7:49 AM PDT
In reply to: Yep.
Collapse -
by MarkFlax Forum moderator / April 23, 2009 8:54 PM PDT
In reply to: Still no luck

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.

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.


Collapse -
by tipoo_ / April 24, 2009 4:57 AM PDT
In reply to: Well.

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

Collapse -
Great news.
by MarkFlax Forum moderator / April 24, 2009 5:20 AM PDT
In reply to: Thanks

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

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


Collapse -
by LimeLily / November 7, 2010 1:17 PM PST
In reply to: Thanks

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?
by Kees_B Forum moderator / November 7, 2010 7:20 PM PST
In reply to: Help!

"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:

Best of luck with your lab report.


Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions

Does BMW or Volvo do it best?

Pint-size luxury and funky style

Shopping for a new car this weekend? See how the BMW X2 stacks up against the Volvo XC40 in our side-by-side comparison.