Formatting Date Axis on a Scatter (XY) Plot - Grid lines by Year Month or Day?

Is there any way in this cruel world to get Excel to format a date axis on a scatter plot by year? We would like the major gridlines to cross at January 1st each new year. If you look at the Major/Minor units under the Axis Options menu, Excel's formatting seems to only allow a number of days for the gridline intervals. This is utterly nonsensical when viewing how the x axis comes out (see screenshot).

Unfortunately putting in 365 doesn't solve this because of leap years and inconsistencies in real world dates.

Due to how we receive our data, we cannot use a line chart which enables different axis options. We need to use an XY scatter chart. The line chart screws up the date-to-value relationship, whereas the XY scatter chart preserves it.

Excel Scatter Chart Format Date Axis.jpg

Originally Posted by MrShorty

a date axis is not available to a scatter chart

That's what I feared...

Originally Posted by MrShorty

In order to make good suggestions, we may need to know something about how you receive your data and why that screws up a line chart.

Here is a mock example of how we receive our data. We can't make any changes to this format. Basically the dates run left to right and each row is a new trend. The dates are unevenly spaced, and the trends will not all have values recorded for each date. There are numerous trends that we do not need to add to the chart. In reality, we have 80 rows, and usually only select 3 to 6 of them to plot.
Attachment 628757

Here is an example of the XY scatterplot. Notice how within the Select Data menu, it allows you to choose the X-values and the Y-values. This gets everything to always display in the right place (correct y value matched to each proper date). Notice how the green dataset has data back to 2005.
Attachment 628759

When changing the chart time to a line chart, the association between the dates and the Y-values gets lost. Notice how there is only one field now within the Select Data menu. Now notice how all the trends are scrunched into the same dates even though the green trend has data spanning back many years before the others.
Attachment 628761

Originally Posted by MrShorty

Attachments did not attach. Be sure you are using the main text editor (click go advanced or edit post or reply to thread) and scroll down to the "manage attachments" link that will bring up the working file uploader.

Apparently now I get an error saying that new users can't use images or links of any kind until they post a few times. Weird since my original post had the image just fine...

Originally Posted by MrShorty

While waiting, a few questions for you:

I understand that you may not be able to make changes to the format or arrangement of the data as it comes in to Excel. However, are you allowed to make a copy of the data in another part of the spreadsheet that would be arranged/formatted more conducively to a line chart? I see many situations where the OP says not to touch the imported data, but my solution is to use lookups/filters/pivots/etc. to make a rearranged copy of the data.

Yes, we usually can make new sheets or copies of the data. That would be the preferred solution instead of doing battle with excel.

As an aside, typically our managers/coworkers may gripe about making copies for various reasons that I completely don't agree with. For example, they often want the data linked all the way back to its source, but that just creates more problems 10 times out of 10. I can't tell you how many sheets of ours crash because there's links upon links upon links. Other times, entire workbooks have been wiped out because someone opens a linked workbook without opening the source workbooks first. Frankly, the flexibility that being able to manipulate the data gives us far outweighs the nonsense and BS that comes up trying to force the data as entered into a chart.