2

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

Graph

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:

Sample tooltip

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?

Improve this question
1
  • 1
    If the X values are plotted as the row numbers of the points, then something is wrong with the data. There must be non-numeric values in the chart data range (non printing characters, like a space or even ""). Fix the data and the chart will work.
    – Jon Peltier
    Apr 8, 2016 at 14:49

7 Answers 7

Reset to default
3

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.

Improve this answer
2
  • 1
    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, 2009 at 6:16
  • 1
    Please have a look at updated question.
    – a_m0d
    Sep 17, 2009 at 6:22
2

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.

Improve this answer
1

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?

Improve this answer
3
  • 1
    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, 2009 at 5:33
  • 1
    Could you attach a screenshot of some sample data? Then we can compare the data and the graph!
    – outsideblasts
    Sep 17, 2009 at 5:35
  • 1
    added screenshot showing tooltip of data point
    – a_m0d
    Sep 17, 2009 at 6:35
1

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.
Improve this answer
10
  • 1
    That's basically what I did.
    – a_m0d
    Sep 17, 2009 at 6:23
  • 1
    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, 2009 at 6:29
  • 1
    already tried that a few times, but doesn't help. Always ends up the same
    – a_m0d
    Sep 17, 2009 at 6:32
  • 1
    Maybe because you did a line graph rather than scatter graph like I did?
    – a_m0d
    Sep 17, 2009 at 6:33
  • 1
    Choose a line graph and see what happens.
    – alex
    Sep 17, 2009 at 6:35
1

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

Improve this answer
1

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?

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

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .