Thread: XY Scatter Plot with 1st of month on X axis

Hi,
I'm attaching a file with some data that feeds a XY scatter plot with lines connecting the data points. The X axis shows dates, which are interpolated from the data. I'd like the axis to have markers at

1-Jun, 1-Jul, 1-Aug, etc

but I have not found a way to do this. I can play with the axis scale settings and start at 1-Jun, but I cannot set the Major unit to be 1 month. I can set it to 30 days, but our calendar system being what it is, that gets me

1-Jun, 1-Jul, 31-Jul, 30-Aug

and so on.

Any ideas how I can see the 1st of each month?????

thanks for reading

Teylyn

In order to control the exact formatting of dates on the x axis you will need to use an additional series and it's data labels.

Hi Acavrak,

to answer your second question first (although I don't have Excel 2007 in front of me now, I think this would work): If you X axis has dates, you need to format the axes numbers with the custom format "mmmm" to show full month names or "mmm" to show just the first three letters of the month. At least that's how it works in Excel 2003.

Now to the first question: The principle is to create a data series that has the labels you want and all values of that series are zero. After you add this series to your chart, you hide the axis labels and instead show data labels for the new series, which are positioned below the data point. You can format data markers that look like axis tick marks.

Here's the step by step

1. Somewhere in your sheet, create a two column table, in Andy's attached file it's in column J and K. In column J enter the dates you want displayed on the chart and in column K enter zeros in each line.

2. Highlight the dates and zeros and add them to the chart as a new data series.

3. Format the new data series. On the tab Patterns:
- no line
- the custom marker style + in size 6
on the tab Data Labels:
- tick X value

4. Format the data labels on the Alignment tab:
- horizontal: center
- vertical: center
- Label Position: below

5. Format the axis with
- major tick marks: none
- minor tick marks: none
- tick mark labels: none

If the data labels sit a bit too close to the x axis, try resizing the plot area until it suits.

Hope that helps.

cheers