How to chart year-year comparison when data is in different worksheets

I have a workbook with two worksheets, each for the separate years 2017 and 2018, each containing columns with a date and a rate. I can easily create a chart for one year showing how the rate changes over the year. I want to create a chart which shows both years by comparison, the current year 2018 being in progress and incomplete.

If I try to put both years’ data on the same worksheet it fouls up the dates. I can’t find a way of having the x-axis (date) treat the date as MM-DD, ignoring the year, so that both line charts can show across the same x-axis to enable the comparison.

Thanks for any advice!

Can you post a sample of how the dates get 'fouled up' when you append the data?

Sorry about the typo

First sheet '2018'
Date Rate
09-Jan-18 45
09-Jan-18 90
09-Jan-18 135

Second sheet '2017'
Date Rate
11-Jan-17 36
11-Jan-17 72
11-Jan-17 108

I don't see any difference between the dates.

I've attached the full data which better shows how the dates, though formatted the same, fall on different days.

i think I get what you mean now, you want both series of dates plotted on the same chart as 2 different lines as a comparison ignoring the year?

Originally Posted by kersplash

i think I get what you mean now, you want both series of dates plotted on the same chart as 2 different lines as a comparison ignoring the year?

Exactly! 🙂

Please check out the attached.

Formatted the dates and added them as separate series in the chart.

On second thoughts this might be wrong.

That's precisely what I was unable to produce. I see you simply reformatted the dates to get rid of the year, right? I'd assumed that irrespective of formatting Excel would 'know' what the real dates were and that it wouldn't work that way. Nice solution! Why did you think it might be wrong?

As the series for the dates is based off 2017 only which means that unless the measurements are taken at exactly the same point in time during the year, the rate for 2018 may end up in the wrong position.

Originally Posted by kersplash

As the series for the dates is based off 2017 only which means that unless the measurements are taken at exactly the same point in time during the year, the rate for 2018 may end up in the wrong position.

You've put me on the right track. I can tweak it from here. Thanks!!

I've actually come up with a nicer idea. Look at the attached.

2 charts overlaying each other, one transparent.

Originally Posted by kersplash

The blue series is 2017, so full year.
The orange series is 2018, so part year.
2 separate charts....overlapped to make them look like they are one chart.

Add data to the bottom of the 2018 table and it will update on the chart.
You may have to play with the vertical axes on both charts if the values go beyond the 2017 data.

Good one! 🙂 Thanks again.