Graphing feature in Excel not working

[Benne]

Hi!
I am using Excel 2007 together with the operating system: Windows XP
My problem affects the most basic feature of Excel, graphing. I am trying to create a graph with values for the x-axis being 0-85, together with three series. After my data has been input I select it by draging a box over it and then I press Scatter graph or any other graph for that matter. This results in a x-axis going up to 1.2 and y-axis going up to 1. It also give the graph the title of 85 and series 1 the same name, which is strange as 85 is meant to be the ending value of y.
When I select the graph in order to see from where it takes the data, I can see a green box covering A17-C17, then a blue box covering D17. A third purple box covers the rest of D(1-16).
It is very hard to describe the problem I am facing, but I would be very grateful for anyone who knows how to fix it, I have used Excel 2007 on another computer and then there is only a blue and green box, so if anyone knows how to remove the purple box it would be great. thx

 

[Jon Peltier]

It sounds like you have some non-numeric data where you should have numbers, so Excel cannot parse the range properly. It usually helps to set up the data a special way. Put your X values in the first column, Y values in subsequent column, keep the cell above the X values blank, and put labels in the cells above the Y values. This is a more foolproof way to ensure that Excel gets your range correctly.

If this doesn't work, you can select the chart, go to the Edit Data dialog, then series by series, edit the ranges containing X and Y values and series names. You also may have to fix the non-numerics. Sometimes imported data looks numeric but is interpreted as text. If you don't apply a horizontal alignment to the range (I rarely do to help with this issue), text is left aligned and numbers are right aligned. If you have left aligned numbers, select and copy a blank cell, then select the left aligned numbers, use Paste Special, and choose the Operation - Add option to add the blank (zero) to the numbers. This coerces Excel to interpret the cells at numeric values and converts them, so your chart should work.

 

[Benne]

How to fix this:

 

[Jon Peltier]

Delete the chart. Insert a row above your data. Keep A1 blank, enter labels in B1 through D1, say "First", "Second", and "Third". select this entire range and rebuild the chart.

 

[Benne]

K thx!
But now it looks like this :P

 

[Jon Peltier]

Select the range, and remove the horizontal alignment, You will see a lot of values left-aligned, which means they are text, not numbers. Excel plots text as if it had a zero value. It looks like these are all of the numbers containing a decimal point.

To fix: copy a blank cell, select the text you need to convert to numbers, use Paste Special, and choose the Operation - Add option. This adds the blank (zero) to the cell value, which Excel is forced to interpret as a number, and thus converts the text to numbers. You shouldn't have to recreate the chart, the points will move after making this correction.

 

[Benne]

Thx, but the problem actually had to do with me using a fullstop instead of using comma, really is stupid that Excel can't interpret fullstops oh well. THX for the help, really couldn't have done it without it!

 

[Jon Peltier]

This is a function of how the regional settings on your computer are set up. If you're set up to use comma decimal separators, then you have to enter numbers with commas as decimal separators.

 

[oldpockets]

Hi!

Hey, when the box is green it means that the data is in text format, likely as the decimal point is not really a decimal point. What i do is in an adjacent column write the formula "=VALUE(A1)" (if the original value is in A1) this copies the number across and changes it to a number. You can tell as usually numbers are right aligned and text is left aligned. Now do the drag down ("dark Black Cross" trick in the bottom corner of the cell) to drag this formula down the column to copy the rest of your data. Now when you do your chart, reference the cells with the new values and your set!

Good luck!!