Charts of data are all zero

[corus]

A set of data (numbers) I've been given won't show as a chart of that data. Having selected the rows and columns and then either selecting a 3D column or XY scatter produces a chart where all the y values are zero, and the x values are at a single point. The title of that chart is strangely given as one row of that data. Believing that the numbers may be taken as text (though they're not in quotes), I've changed the font and the numbering format of the cells, all to no avail. I believe that there was an error when first loading the sheet regarding the format, but that no longer shows.
What is wrong with the data and how can I get a graph or chart of the data?

corus

 

[IRstuff]

There is often a residual conversion error that requires some sort of brute force approach to re-formatting.

As an experiment, try selecting one of the trouble cells, hit F2 and then <Enter>. If the left-right justification switches, then it had a residual text format. If the table is relatively small, you can repeat that to get all the entries back into numeric format.

TTFN

 

[corus]

Thanks IRStuff, your method worked but would only work for a single cell. Eventually I found that by entering 1 into an empty cell and using paste special and selecting the range I wanted and using multiply then all the cells changed into numeric.

corus

 

[cpretty]

I have had this same issue. It has come about through pasting data from a database into Excel. The database has a field incorrectly formatted, bringing the format into Excel. For some reason the number stays as text, even though the format is number.

My solution is exactly the same as yours.