How to align end-of-month chart points with date axis vertical gridlines?

[machinehead]

I'm charting 60 months of end-of-month data in a line chart. On the horizontal date axis, Excel treats months as beginning on the first day of the month (e.g., 7/01/2016).

But the plotted data points don't coincide with the vertical gridlines. For instance, the data point for 6/30/2016 plots to the left of the 7/01/2016 vertical gridline, while the data point for 7/29/2016 plots an equal distance to the right of the 7/01/2016 vertical gridline. This creates ambiguity in reading the chart, since the end-of-month dates are plotting as if they were mid-month dates, in relation to the vertical gridlines.

Changing the date axis to a text axis doesn't make any difference. Any way to force Excel to plot monthly data points ON the vertical gridlines?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I does not seem to be easy, does it. But I expect you knew that.

Using month numbers might work for you i.e. 1 to 60.

I cannot get whole grid lines but you can get tick marks on the x-axis at the right places by formatting the dates as "YYYY-MM" then use a Line Chart rather than a Scatter Chart. Tick Marks can be added via Axis formatting options.

Excel 2013
	C	D	E
1	Date	Axis	Qty
2	31/01/2011	2011-01	7
3	28/02/2011	2011-02	9
4	31/03/2011	2011-03	1
5	30/04/2011	2011-04	8
6	31/05/2011	2011-05	0
7	30/06/2011	2011-06	4
8	31/07/2011	2011-07	3
9	31/08/2011	2011-08	6
10	30/09/2011	2011-09	2
11	31/10/2011	2011-10	2
12	30/11/2011	2011-11	4
13	31/12/2011	2011-12	2
Sheet1

Cell Formulas
Range	Formula
C2	=EOMONTH(DATE(A2,B2,1),0)
D2	=TEXT(C2,"YYYY-MM")

I hope this helps,

Regards,

 

[machinehead]

Thank you, Rick.

In testing your solution, I saw for the first time that at the bottom of the "Format axis" dialogue box, the option is given to Position axis either "on tick marks" or "between tick marks."

Changing the setting to "on tick marks" solves my problem at one stroke. As usual, Microsoft's boffins have chosen precisely the opposite default setting that any sensible person would select.

Thank you for helping me discover this easy fix!

 

[RickXL]

You're welcome, I am pleased you have it working as you want.

Regards,