2

I want to compare several data values of several years over the course of a year. Something like this. For that, I changed all the data to be within the same year and I can put them in a graph together but unfortunately the data is not displayed accurate with the correct course. The problem is that I have different dates in each year and would like to display that in the chart that e.g. the 15th of a month is in between those month in the chart so you can distinguish.

When I plot them for each year, it is all fine, when I try to add another year in, you can see here that it doesn't show it correctly. The 2013 line includes data until november but in the joined diagramm, it stops in June. That is the main problem, that it doesn't display 2013 in the correct way.

My table structure is below. I used the 00 year in combination with the value in my attempt to merge them. 2019 has much more values than 2013.

this enter image description here

I hope someone can help me

ADDITION:

I think it has to do with this because I noticed how they have different horizontal labels and the 2013 (series 2) has empty ones in there as well, 2019 does not. I noticed that both series are always connected to the same date range, so either the dates of 2013 or 2019 and not for their own.

enter image description here

Improve this question
6
  • How did You extract time, simply difference of 365 days should be fine. How you set null? Is it =NA() function ? Try to change the null value to empty or =NA() (it shall give you #N/D in the cell) and the plot should work then.
    – Bomba Ps
    Commented May 6, 2021 at 8:40
  • They are measurements so the dates are set and null is just when I don't have values from the measurements. When I changed the nulls to an empty sell it just doesn't show them on the graph but other than that, nothing changes. What do you mean with difference of 365 days? I updated the second picture so you can see it better.
    – user1332448
    Commented May 6, 2021 at 8:49
  • Did 2 tables for 2013 and 2019 have the same date format?
    – Lee
    Commented May 6, 2021 at 9:10
  • Yes, I inserted pictures
    – user1332448
    Commented May 6, 2021 at 9:16
  • It does not look to me like it stops in June, based on the shape of the line it is displaying all the data but compressing it into less horizontal space. Has it added a second X axis at the top of the chart perhaps, which would have independent scaling? Can you check the X axis is formatted as a Date type rather than text or automatic?
    – AdamV
    Commented May 6, 2021 at 9:32

1 Answer 1

Reset to default
0

A line chart uses the same X values for all series. I wrote about this in a tutorial called Multiple Time Series in an Excel Chart. Long story short, your data is like the top of the figure below, with separate dates and values for each year. Arrange them instead as in the middle of the figure, with all dates (without years as in your example) in one column, then your values in subsequent columns. You don't even need to sort by date.

Make your chart, then right-click on it and choose Select Data. Click the Hidden and Empty Cells button, and select Connect Data Points With Line as shown in the bottom of the figure.

Multiple Timelines

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .