Adjust tick marks to first day of month

**maacmaac** · 11-30-2010, 01:24 PM

I have a chart that is pulling data from a table with daily data. The data table starts in the middle of a month. I am charting ~ four months of data and I want the tick marks to be positioned at the first day of the month.

The problem I am having is the data table starts in the middle of the month, which is resulting in the tick marks being placed at the incorrect position on the x-axis. I tried to change the minimum value to the first day of the month but that causes the chart to plot a blank area. I have attached small sample for better clarification. Any comments are appreciated. Thanks

**maacmaac** · 11-30-2010, 11:03 PM

Bump no response

**Andy Pope** · 12-01-2010, 05:33 AM

You can make your own axis labels using a dummy xy-scatter series.
You can then link the data labels to the cells in order to avoid what appears to be a bug regarding data labels.

Use this free addin
http://www.appspro.com/Utilities/ChartLabeler.htm

**maacmaac** · 12-01-2010, 10:31 AM

Great tip. The only issue I am running into is if I change the scale so the chart looks better (i.e. I changed the min value from 0 to 9500; see attached example). The issue is I am losing the tick marks on the x-axis. Is there a way to display the tick marks if the scaling is modified? Thanks

**Andy Pope** · 12-01-2010, 10:37 AM

If you change the y axis you simply need to change the Y values used by the dummyaxis series.

range M2:M4 should be 9500 rather than 0.

Of course using a non zero base line in your area chart could be construde as distorting the data. Most readers expect filled in chart items such as areas and bars to come off of a zero baseline.