Y Values Showing up as Zeros in Graphs

When I import the data from text/csv, everything looks okay, but when I go to plot the imported numbers in a graph, the y-values are all treated as zeros. While they appear as the correct numbers in the sheet, when I look at them under "Select Data" for the graph, it shows = 0,0,0,0,0,0,0... The only way to get around that is to individually evaluate each cell by double clicking and then hitting enter. When I do this, the values automatically populate correctly in the chart and under "Select Data" the evaluated values show up correctly while the unevaluated ones still show as 0. This solution is not at all feasible for the quantity of data that I'm processing. The cell formats are General but changing them to Number does not solve the issue, and they are constants, not formulas, so technically there shouldn't be anything to evaluate. Any ideas on how to solve this problem?

Importing from a CSV often ends up with numeric values being stored as text, even though they may still look like numbers.

If your data is in a column you can highlight the column and then "Data / Text to Columns / Finish" and it will automatically make them all number formats in one go.

If they're not in a single column you can copy any truly empty cell (by that I mean a blank cell from a new sheet and not one that merely looks blank but may not be), highlight the range to be converted to numbers, right click and select "Paste Special / Add / OK".

BSB

And I suppose it depends on how you're importing the data. If using VBA then you could add some code to convert to numeric values at the same time and not have to worry about it in future...

BSB

The "Text to Columns" solution works. However, I can only do one column at a time and it's purely manual, so not that much more efficient than before. The file I'm importing has both numbers and text so I'm not sure how to automatically assign the correct data types using VBA. I don't understand why Excel 365 is unable to do this automatically when my old version of Excel could do it seamlessly without the fancy new importing/data connection wizard.

If you were doing it cell by cell originally then the Text to Columns method would be more efficient as you can do an entire column in one go.
The other method I mentioned can be used on entire sheets if you so desire.

As for why 365 doesn't handle this as seamlessly as previous versions.... That's what Microsoft call "progress"!

I tend not to use the wizards if it's a task I'm going to perform more than once as I find it much easier to write VBA to do exactly what I want.

If you'd like some help with that, attach a sample workbook that shows before and after results and we can go from there.

BSB