I have built a chart that shows data for three time periods: last 7 days, last 30 days and last 8 quarters. I use a list-box to allow users to select which date range. The cell value returned by the list box triggers some logic that pulls in the correct data to my source data cells.

The problem I'm having is with the date ranges that are displayed along the vertical axis. The dates are correct for the last 7 days and last 30 days but for the last 8 quarters it shows a tick mark for every month even though there are only 8 dates available in the date row of my source data.

Example, in my source data, the dates listed (in consecutive cells) are 1/1/05, 4/1/05, 7/1/05, etc. But when they get displayed in the chart, Excel is adding tick marks for all the intervening months (2/1/05, 3/1/05, 5/1/05, 6/1/05, etc).

It's not a big deal but this makes the chart area look unnecessarily cluttered and harder to read; it also makes it look like there are 24 data points (8 quarters X 3 mos. per) when in fact there are only the 8 quarterly data points.

As for Format Axis, everything is set to auto. I cannot use fixed values because sometimes I need days and sometimes I need months. Any ideas on how I can force Excel to not add the tick marks for the intervening months?