Consecutive months for X-axis labels

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

But I'd like the t axis labels to be the months in mmm format: Jan Feb Mar Apr ... . Each 3 letter month should be just below the t axis, centered between vertical grid lines.

Are you required to use an xy scatter chart? It seems to me that this will be easier by using a line chart with a date axis.

1) Make sure your dates are real date serial numbers and not some kind of text string (something like this if you are unfamiliar with how Excel stores dates and times: http://www.cpearson.com/Excel/datetime.htm
2) Make a line chart with the dates as the horizontal axis and whatever as the value series.
3) If Excel does not make these choices automatically, format the horizontal axis so that:
3a) It is a date axis
3b) with month as the base date
3c) position axis between tick marks
3d) number format as "mmm"
3e) any other formats, as needed.

That's how I would do it. If this much be an xy scatter chart and not a line chart, then we can talk about some more complex things to give the same effect, but they will definitely be more difficult.

Here's what I did:

1) As I noted above, it will be important for your dates to be true date serial numbers and not text strings. Your "dates" are text strings, so I used the DATEVALUE() function to convert the text strings to date serial numbers (again, review the link above if you are unfamiliar with how Excel stores and uses dates and times).
2) With the dates converted to serial numbers, I rearranged the data into a table like

Please Login or Register  to view this content.

3) Select this table and insert a line chart
3a) In my copy of Excel, Excel did not recognize that the dates should be the x axis data, so I opened the select data dialog and made sure that the column of dates was the horizontal axis data.
4) Format the axis so that the base unit is days, and the major unit is 1 month.
5) Other formats as needed.

even though I selected "Solid line" on the "Format Data Series" page, no line displays. Why not?

I am not sure why it works this way (I don't use line charts much myself), but I went into the "hidden and empty cells" dialog and found that it had "gaps" selected. I selected the "connect data points with line" setting and the lines appeared. I don't understand it (there are no "gaps" in the data in the spreadsheet -- perhaps Excel is thinking that you do not have data for the days/months in between so it considers those "gaps"), but that seemed to fix that concern. https://support.office.com/en-us/art...b-9ca49cb92274

Technically, both charts are not using a date axis, though you are using a "date" type number format for the labels. The line chart uses a date axis, where you have told it to place a tick mark+grid line for the first of each month. The scatter chart uses a value axis which doesn't really know what "months" are, and you have instructed it to give you a tick mark + grid line every 31 days (5 Jan, 5 Feb, 8 Mar, 8 Apr, 9 May, etc.). This is fine, except that, when formatted as "mmm", the reader does not know what date corresponds to the gridline, and may misinterpret something. For example, the Aug 17, 2019 point on the line chart is about halfway between the grid lines marked Aug and Sep. I would tend to naturally assume that this means that this date is about halfway through August. On the scatter chart, this point is about right on the grid line marked Aug, so I might assume it is very close to 1 Aug. Only you would know if this is a real concern for you. My inclination would be to stick with the line chart with a date axis rather than the scatter chart.

FWIW, I think your number formatting with the spaces is probably the easiest way to shift those axis labels.