Struggling to create linechart with multiple series. Possibly requiring it to be dynamic.

Hi all,

I always have a hard time creating these charts, but I've managed to retrace my steps from earlier projects.

I want to create a chart which have a fixed start/end point at 09:31/16:00 for the X-values.

I have two variable values, Low Time & High Time. These CAN happen at 09:31/16:00, but usually, they're somewhere inside that range.

For the Y values, I have arbitrarily chosen 0 and 10. I just want a linear line between the Low Time and High Time. I've set up my raw data looking like this. It's possible it could have been done in another way:

2.PNG

Steps:

1) Select range C22:J22

2) Create 'recommended chart'. I choose line chart.

3) Select chart and select data; "Add X-series" to chart. I here select C22:F22.

Format axis: a) Change numbers to hours; b) Change min./max. values to 09:30 - 16:00.

By the way - this step is weird. Sometimes, I can enter 09:30 and Excel automatically converts it to numbers. Other time, I have to enter 09:30 in a cell, convert that to a number and then copy and paste that into the settings. I'm not always able to enter it directly.

Some more formatting and the chart is done.

But what if I'd want to add say 50 rows worth of data to this chart?

Is that even possible?

I started doing it manually by 'Select Data' and 'Add Series". It's of course a cumbersome process.

Thanks in advance for any pointers.

Best regards,

Elijah

3.PNG

Originally Posted by Andy Pope

Your main problem with the current data layout is you have start and finish position on a single row.

Consider using formula to rebuild a set of chart data.
This will output all the data points in your data set.

You would need to extend the formula and the range used in the chart if you want to add more records.

Hi, Andy,

Thank you for taking a looking and providing a potential solution!

Is it not possible to handle this with all the data from one day on a single row? Something similar to this layout was actually suggested to me in the past on these forums. Prior to that, I had it set up differently with 2 rows per day and 1 row per time - 1 row per price.

If not - I may simply have to go with your solution - which admittedly does work.

Best,

Elijah

Originally Posted by Andy Pope

Yes i is but then you are back to your original problem of spending a vast amount of time on defining the data points of each series and then formatting.

Was that layout suggested in order to generate this chart? Seems a strange layout for any chart given how excel expects the data to be laid out for charting.

Correct. I had a different layout prior to that.

Anyway, I decided to just add in this manually. Took me 30 minutes or so, but now I'm done. If I were to do this a LOT - I would have to find a more efficient method. But this works for now.