Excel help

Emp_Fab

Anyone know where I can upload a copy of it so can play with the file yourselves?

viz

Emp_Fab said:

If I go to edit the chart, it gives me this box...  I'm sure the secret lies in here but I'm not clever enough to work it out.

Yes, do what I said. 

Emp_Fab

https://file.io/WiyIO8iAR8ck

drofluf

Emp_Fab said:

https://file.io/WiyIO8iAR8ck

appears to be deleted?

stickyfiddle

Emp_Fab said:

I added the first column back, selected everything, insert, chart, scatter.....

Ok, so firstly, do what I suggested as plan A… it's the right answer. 

 But if you want to understand it better.... what’s happening here is it’s reading the red section as your data name, and a20:b20 and c20 as data. 

Going into the select data box, you want to click Edit under horizontal axis labels and select your column a data there. 

Then delete everything in the left hand box. 

Then click Add, set data name to B1 and B1:B[end of range] as the data range. Then do the same for col C

The biggest issue is that Excel can't show times on an axis when it sees col A as Date-Time, it needs to know you want it represented as Time alone. I suspect this is because showing minute-wise data across multiple days isn't good practice for large data sets like this. 

Emp_Fab

drofluf said:

Emp_Fab said:

https://file.io/WiyIO8iAR8ck

appears to be deleted?

PM incoming

Emp_Fab

stickyfiddle said:

Emp_Fab said:

I added the first column back, selected everything, insert, chart, scatter.....

Ok, so firstly, do what I suggested as plan A… it's the right answer. 

 But if you want to understand it better.... what’s happening here is it’s reading the red section as your data name, and a20:b20 and c20 as data. 

Going into the select data box, you want to click Edit under horizontal axis labels and select your column a data there. 

Then delete everything in the left hand box. 

Then click Add, set data name to B1 and B1:B[end of range] as the data range. Then do the same for col C

The biggest issue is that Excel can't show times on an axis when it sees col A as Date-Time, it needs to know you want it represented as Time alone. I suspect this is because showing minute-wise data across multiple days isn't good practice for large data sets like this. 

Ok - thanks for the help mate (and everyone).  Changing the cell format of column A to just hh:mm:ss doesn't fix the graph.

Doing what you've detailed above;  Select Data, edit Horizontal Axis Labels to the A column data, then clicking on 'Remove' in the left box deletes everything - including the edited right box contents.

stickyfiddle

Ok, weird as it worked on my system. Feel free to ping it over and I'll have a proper look!

drofluf

I think I see the problem. There's a warning on each cell in columns B & C that says "number stored as text". I can change cell by dell but that's a pain for the size of your dataset

Emp_Fab

stickyfiddle said:

Ok, weird as it worked on my system. Feel free to ping it over and I'll have a proper look!

Unique link coming via PM 

drofluf

And a solution!

Put the number 1 into a spare cell

Copy it

Then do Paste Special>Multiply

Jonathangus

drofluf said:

I think I see the problem. There's a warning on each cell in columns B & C that says "number stored as text". I can change cell by dell but that's a pain for the size of your dataset

Ahem...

Jonathangus said:

Looking at those screenshots, the numbers in columns B and C are aligned left, which suggests Excel could be treating them as text, not numbers.

  

Emp_Fab

YESSS!!!

Success!!

Thank you Drofluf!

How the hell did you find that out?

drofluf

Emp_Fab said:

YESSS!!!

Success!!

Thank you Drofluf!

How the hell did you find that out?

In my version of excel there was a little green triangle on the top left corner of the offending cells, I hovered over and got the warning message. 

The multiply by 1 (other arithmetic operations are possible) was I trick I’ve used when I’ve had similar data issues. Weirdly they also behaved as numbers on my initial check when I added columns b & c together 

Emp_Fab

Bizarre.  The data came from an ancient system and  the newest export format it has is Excel v8! (It also offered Lotus 123 options!).

I expect importing that into the latest Excel was a less than perfect operation. 

Strange - I tried setting the cell formats to number and that didn't help but doing what you did fixed it.

drofluf

Does the old system have a csv export option? Often the safest choice. 

Format in Excel is essentially a presentation tool, it doesn’t change the underlying data 

Jonathangus

drofluf said:

Does the old system have a csv export option? Often the safest choice. 

This.  I train users on a system which has an 'export to Excel' feature - and doing so always breaks all the number formats.  The answer is always to ignore Excel and choose CSV, then open the CSV file and save as Excel.

Emp_Fab

I will take that on board.  Thank you to everyone for your kind assistance.

stickyfiddle

Jonathangus said:

drofluf said:

Does the old system have a csv export option? Often the safest choice. 

This.  I train users on a system which has an 'export to Excel' feature - and doing so always breaks all the number formats.  The answer is always to ignore Excel and choose CSV, then open the CSV file and save as Excel.

Ah this takes me back to my days of packaging tariffs, bringing clients’ SAP extracts (or worse!) into our in-house system. Every single file always had problems, and always unique to each client, even if they were using the same system as another… 

CSV was always the path of least resistance!!

drofluf

stickyfiddle said:

Jonathangus said:

drofluf said:

Does the old system have a csv export option? Often the safest choice. 

This.  I train users on a system which has an 'export to Excel' feature - and doing so always breaks all the number formats.  The answer is always to ignore Excel and choose CSV, then open the CSV file and save as Excel.

Ah this takes me back to my days of packaging tariffs, bringing clients’ SAP extracts (or worse!) into our in-house system. Every single file always had problems, and always unique to each client, even if they were using the same system as another… 

CSV was always the path of least resistance!!

Until there’s a comma in the source data….