Home » Questions » Computers [ Ask a new question ]

Excel automatic axis scale completely inappropriate for data

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:

Asked by: Guest | Views: 254
Total answers/comments: 4
Guest [Entry]

"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."
Guest [Entry]

"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?"
Guest [Entry]

"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?"
Guest [Entry]

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