Excel Scatter Chart Problems

Packard bell / Easynote tj61
February 9, 2010 at 11:21:44
Specs: Microsoft Windows Vista SP1, 1.398 GHz / 510 MB
I have repeatedly tried different types of information with one x-axis and one y-axis variable and tried to draw it in excel on xy scatter. Repeatedly it ignores my y axis information. i.e. It highlights the x-axis stuff and just puts y=o for everything on y!!.

I went into the formating and found out that the Series Y Value is blocked on ={1} unlike the Series name which says series range next to it or the Series X Value whic also says Series range. This is more clearly put in the image here:

Plz help me i have to hand in a physics report soon and really need graphs!!!

Thans in advance! :D

See More: Excel Scatter Chart Problems

February 9, 2010 at 13:42:32

I have no experience with scatter charts, but what I see from you image is that you are plotting a single variable against a value of 1.

The ={1} means that for however many data points there are in the X series, the Y series value is one.

Excel uses {} to represent an array, in this case an array (or series) of 1's.

There is no reason why you can't delete ={1} and add the second set of values.

A scatter plot is usually a means of comparing two different items and showing them as a single plot, typically to see if there appears to be a relationship between the two different items.

Assuming you have two sets of data - say hourly indoor temperature and hourly outdoor temperature, you can combine them into a single plot, as a series of dots (can be linked by a line). Each dot occurs at the point where for the same time point the indoor temperature intersects the outdoor temperature.

Anyway - just delete ={1} and put in your values

(Also check that the data you are using is actual numbers and not text that look like numbers - does this data plot OK in a typical line chart?)

If this doesn't work, post some data - use the <pre></pre> tags at the top of the reply box. Data between these tags pretty much retains its formatting.


Report •

February 10, 2010 at 06:24:27
Dude thanks fo your reply,

I tried what u told me which was to delete the ={1} but when i put the values in it changed to this:

what it does is it takes my y values and puts them as my x and then totally neglects my x-values (seen by the colour difference in the second picture i think).

Thanks again for the help plz respond quickly because i need to do my project!! Thank u.

Report •

February 10, 2010 at 08:26:19

I took your data from the image you posted, converted it to values and placed it in columns A and B (A1 to B14) using Excel 2007.

I selected the cells A1 to B14, then from the ribbon used Insert - Charts - Scatter and selected the first chart type (dots, no lines). The chart appeared as expected, using both data series.

It is possible that your data is text not numbers.

Take two new columns, say C and D and select C1 to D14, Format as numbers with two decimal places.

In cell C1 enter =VALUE(A1).
Drag and extend down to D14

Now select the new columns and Insert the scatter chart.

Please let us know if this works.


Report •

Related Solutions

February 10, 2010 at 09:02:00
Hey Guys,

Thanks for the quick answer but your solution doesn't seem to work because everytime i format the cells i insert the sample into the cell and not my results ( right) !! I have also tried on a different friend's computer and there also the results have worked fine. Also what i did is i tried to just do another simple xy scatter graph with random digits i choose and same result x-values were not taken!!

Here the updated pics on the suggestions u made me:

Thanks in advance for the help

Report •

February 10, 2010 at 10:04:35

When trying to format the cells the value shown in 'Sample' is the data in the cell or the top left cell in the selection.

For a reason that I do not understand Cell A1 and presumably others do not have the right data, and what is showing does not seem to match what is stored.

Try selecting cells A1 to B14 and Format and select General.
Do they still show 1.5 etc.

(I presume that your decimal separator is 0,00 and not 0.00)

Also in E1 enter =A1+1
What do you get


Report •

February 10, 2010 at 10:14:32

If you go to cell A1, right-click and Format Cells...
Does it start in Custom formating
Is the format by any chance d.m


Report •

February 10, 2010 at 10:34:07
Once again thanks for the quick response:
here are the updates:

This is what happens when I first enter the data
This is what happens when I select general
This is what I get when I enter =A1+1
This is what I get if I go to cell A1, right-click and Format Cells... before the previous steps.

Look forward to hearing from you

Report •

February 10, 2010 at 10:44:40

The cells have somehow become formatted as dates.

40299 is 01 May 2010
40180 is 02 January 2010

The values are Excel date numbers.
Excel stores dates as a number starting at 1 for 01 January 1900

As the column was formatted to show the dates as the number for day and the number for month with a '.' in between, they looked like the numbers 1.5 and 2.1

The scatter chart could not use dates, so it reverted to zeros when you selected the column.


Open a new workbook
Select the whole of the first worksheet and right-click and make sure all cells are formatted as General

Now enter your numbers from the keyboard - do not copy and paste.

Select A1 to B14 and format as numbers with 2 decimal places.

With the selection goto Insert - Chart - Scatter and create your new scatter chart.


Report •

February 10, 2010 at 11:38:13
Thank you for your response:

Your solution seemed to work perfectly until the step with numbers where it changed the numbers to 40 thousand something again and I promise :P i typed them in and i did not type 40 thousand in!!!!

Here is a picture to prove:

This is problem is killling me because i have been trying to fix it for like 10 straight hours :P!!!

Thanks for all your support until now though!

Report •

February 10, 2010 at 12:17:52

Did you actually start with a new workbook.

Close Excel.

Open Excel

In the new workbook, this time select cells C1 to D14
Right click and format as General

Enter new whole numbers only - 1, 2, 3 etc.
Select the cells again
Format as number with 2 decimal places.

Create a scatter chart from cells C1 to D14

If OK, start putting you old numbers into C1 to C14.

Again - do not copy and paste - these numbers must be entered from the keyboard.


Report •

February 11, 2010 at 12:16:55

Sry for the late response.

Good news everything worked with the whole numbers and even the scatter chart worked.

However, as soon as i started typing my numbers it automatically changed to 40000 again!!! For example my first x value is 1.5 i typed it and pressed my down button to type in my next piece of information and it changed to 40299.

Here is the update:

Thanks for all the support until now,

Talk too you soon!

Report •

February 11, 2010 at 12:49:16

For some reason your copy of Excel is taking values in the format 1.5 as a date and converting them to a date value.

0.88 for instance, is not seen as a date by Excel, so it leaves it as the original number.

As a short term fix try entering numbers as a formula =1.5/1 instead of 1.5

What is your default date format in windows.
In control panel look in regional and international settings and post short and long-date formats.


Report •

February 12, 2010 at 06:06:48
I wanna thank you so much for all your support. I Love you :P!!!! The problem was to do with the time and date settings because i live in germany therefore i had to type decimals with a coma and not a dot. Just one more thing how can i change it so that when i type a dot it stays a decimal because rite now its set on coma:P. Also if its something to do with control panel plz be specific cuz im a real noob.

Thanks for all your support,



Report •

February 12, 2010 at 07:10:50

You can make Excel use the dot . decimal separator without changing what the rest of Windows uses.

Click the Office button (top left)
At the bottom click Excel Options
and in the dialog box that opens select the Advanced Options tab

Remove the check (tick) from the box labeled 'use system separators'
Put a . in the decimal separator box and a , in the thousands separator.
Click OK

(I have never used this, so I can only guess that it will work !)
Good luck


Report •

February 12, 2010 at 09:22:49

Ur amazing thank you for all your support everything worked and i finished my physics reports:D

Thank you so much



Report •

February 12, 2010 at 10:18:07
You're very welcome



Report •

Ask Question