Excel help

Emp_Fab

Hi.

I've got a spreadsheet with three columns and several thousand rows.  I want to create a line graph with the date & time on the x axis and the values in columns B & C on the y axis.

Column A is a date and time field in the format "dd/mm/yyyy hh:mm:ss"

Columns B & C contain the data I want to chart.

Selecting all three columns and trying to create a chart from that creates a mess.  It's something to do with column A.

If I leave column A out, it makes a line graph but I need the date & time in. 

How can I edit column A so it works when I create a chart please? 

The other issue is this data has one row per minute of time, so that's 1440 rows per day.  Once I manage to get a line chart out of it, the x axis is going to be rammed with thousands of labels.  I really have to have the text on the x axis cut right back to say, hourly marks or even less otherwise it's going to illegible. 

How can I edit the axis labels to do this? 

Many thanks in advance!

drofluf

Think you want to use an "X Y Scatter" Chart, that should make column A the labels

Once you've created it you can then edit the axes to only show labels every 100 (or other number) data points

Emp_Fab

The first problem is getting a graph out of it!  With column A formatted as it is, I don't even get any lines - just a blank graph with what looks like a text heading made up of thousands of values from the data columns!

drofluf

To edit the axis double click on it and in the Units area select Major - it's not very obvious

P.S. I used a Mac version, yours may have slightly different terminology 

drofluf

Emp_Fab said:

The first problem is getting a graph out of it!  With column A formatted as it is, I don't even get any lines - just a blank graph with what looks like a text heading made up of thousands of values from the data columns!

As a test can you change Column A to be 1, 2, 3, 4 etc.?

If you can share the file happy to take a look? But understand that you may not be able to if it's for work

viz

Does it work if you just do A and B? Does B contain the number 1 only? As in, “at 2pm on the 13th, there was 1 event”

Sporky

So is it two series of data against a timeline? 

stickyfiddle

It’s because Excel is a POS with times for some reason. 

Best I can do so far is to reformat Col A to just show you the time (the day will still be there in the formula bar but you won’t see it in the cell. Then your chart will work. 

Then you can fix the axis by right clicking it, going to format axis, labels and specifying an interval (in minutes) that makes sense for your data 

(apols for camera caps - my excel is on the work machine but I can't upload pics direct from there) 

viz

If you make a graph with just B and C, then right-click on the graph and click on “select data”, and then click on one of the column names, select “axis”, then select column A on the spreadsheet itself, does that work?

Emp_Fab

It is indeed two series of data against a timeline. 

There's several suggestions here so I'll do my best to give a combined response..

Here is a shot of part of the data: 



And if I just select the data in columns B & C and insert a line chart, I get this.....

swiller

you need column headers on the dataset empfab. Then it wont get so confused.

Emp_Fab

Tried that.  Makes no difference at all.

drofluf

In your second screenshot it doesn’t look like you selected all of column B, just B21? That’s why you see 21.76 as the chart title 

But I think you still need to use an xy type, not line for your dataset. 

Sporky

I added headers as @swiller suggested, chose a scatter graph as @drofluf suggested, and changed to a line scatter. That made something fairly sensible. 

Emp_Fab

It's not me that's selecting only B21 in that shot.  That's what it changes to once I insert the chart.  I am selecting all the data, including the headers (or without. Makes no difference) and then Insert, Chart.  I then get that weird chart and the selection changes to show all of column C in blue and that one cell in column B in red. 

I've just tried what Sporky suggested - headers, scatter etc and it basically does the same thing! 

Emp_Fab

I've even copied the data into notepad then pasted it back into a fresh sheet to remove any formatting data and it does the same thing!

Emp_Fab

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.

Sporky

Emp_Fab said:

I've just tried what Sporky suggested - headers, scatter etc and it basically does the same thing! 

Leave the time column in too, and select all three columns' worth. 

Emp_Fab

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

swiller

the data colums are showing what is selected for the graph, there is only one cell (a30) selected for column A which shows in red square,. Need to have all data in all columsn selected.

e2a suggest delete graph, select data and insert graph for a fresh one.

Jonathangus

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.