Excel automatic axis scale completely inappropriate for data

How can I make Excel use the correct scale on its graph axis? For example, I have a graph which looks like the following:

The x-axis has the wrong scale - it should range from about \$3000 to \$12000, and be normally distributed along this axis. I do not have any logarithmic axis, nor have I changed the scale of the axis in any way - it has all been left at the defaults. The values are correct in the source data, and I am not using any formulae on this data - it is just graphing the raw data.

How can I make it display this axis correctly so that my graph gives a true representation of the source data?

EDIT: I have had a look at the data points, and have noticed something really strange. For some reason, the X-Value of the data point is actually the row number of that data point, and the name of the data point is the X-Value (the value that should be on the x-axis). A sample tooltip is shown below:

This tooltip shows how the point name and the x-value are the wrong way around. However, I have the correct column for the x-values. How can I correct this?

-

You have to make sure there are numerical values in the X range, and either true blank cells or cells that contain #N/A if the source data isn't filled up yet. A formula returns #N/A if you use the function NA(), for example, =IF(A1="",NA(),A1).

This means that the source data can in fact extend beyond the amount of data in the sheet. But you have to be careful with it.

-

Okay, I have finally managed to solve this problem. Here is what I discovered:

The graph that I was using had as its data source

``````(\$E\$3:\$E\$86, \$F\$3:\$F\$86)
``````

When I went to post an example spreadsheet, I changed the source data location, and the error went away. So the problem is that I was trying to graph data that didn't exist yet (the rows were blank, or filled with zeroes), and Excel mis-interpreted the data.

So, my solution to this problem is:
Ensure that the graph does not try to read data below the last valid row. If the last row of the source data does not contain valid, don't try to graph it.

Hopefully this helps any one else who has the same problem as me.

-

The problem must be definitely in the data source. The data series must include only the Y data, the axis labels must contain the X data.

Can you provide a screenshot of the Data Source dialog box?

-
??? That's a scatter plot up there, not a line plot! – a_m0d Sep 17 '09 at 22:46
Yeah, I guess I was wrong, a line plot would have only one point per column. – djeidot Sep 18 '09 at 14:10
and the data points would be connected by lines ... – a_m0d Sep 18 '09 at 23:23
Not necessarily, a line plot can be formatted in order to hide the lines, showing just the points. – djeidot Sep 21 '09 at 17:01

I can't tell you exactly what is going on here, but I can suggest a couple of things.

First ensure that the data you are dealing with are actually numeric, and isn't text that looks like numbers. You can force it to be a number by adding a new data table with values from your original table plus zero, that is NewValue = OldValue + 0

Next, look at at how your chart axes are set up.

• Click on the chart.
• Click the chart wizard menu button

Step through the "Next's" to the "Chart Source Data Tab" which should show two tabs

1. Data Range and
2. Series

These two tabs define what values and labels are plotted in the chart.

Look in the "Series" tab which defines what parts of the data are used for the X-axis labels and axis labels and the Y-Axis Values and Labels.

I expect that once you checked that you are plotting what you want to plot that you will have cracked the problem

-

If you have data for both the X and Y axis, to create the graph you want:

• choose only the cells that represent your Y axis
• go to Insert -> Chart -> choose the line chart and click Next
• go to the Series tab; the last option there is Category (X) axis labels. Press the button next to that
• select the cells that will represent your X axis (the ones that range from \$3000 to \$12000)
• after this you can just hit Finish, you should be all set.
-
That's basically what I did. – a_m0d Sep 17 '09 at 6:23
That is very strange. The graph that I created does not look like the one you made. Maybe you should delete the graph and try again. – alex Sep 17 '09 at 6:29
already tried that a few times, but doesn't help. Always ends up the same – a_m0d Sep 17 '09 at 6:32
Maybe because you did a line graph rather than scatter graph like I did? – a_m0d Sep 17 '09 at 6:33
Choose a line graph and see what happens. – alex Sep 17 '09 at 6:35

Have you tried right-clicking the X-axis, and doing 'Format Axis...'? Maybe you should override an 'auto' value and use your own constant. After double-checking the data (including the cell formatting) I would try this.

-
Data is correct. Have tried formatting axis, and manually setting the max value and the tick marks, but that just squashed all the data. Number format is linked to source. – a_m0d Sep 17 '09 at 6:16
Please have a look at updated question. – a_m0d Sep 17 '09 at 6:22

Of course you've checked the source data, but what reason is there for a range of \$3000 to \$12000 being shown as a range of \$0 to \$120? This bit doesn't make sense. What is the lowest x value in the data? The graph shows a bunch of values just above \$0, with a maximum of about \$45.

It's possible that the data was pasted into an existing spreadsheet with some formatting of formula. Anything to suggest this?

Try to copy and paste (some of) the data into a new spreadsheet. Be careful to copy the data and not any formula. Any change?

-
No, the data was entered straight in. No formula, just raw data. I have a couple of different charts, all with the same problem. – a_m0d Sep 17 '09 at 5:33
Could you attach a screenshot of some sample data? Then we can compare the data and the graph! – outsideblasts Sep 17 '09 at 5:35
added screenshot showing tooltip of data point – a_m0d Sep 17 '09 at 6:35