XY Scatter Plot with 1st of month on X axis

**teylyn** · 11-09-2008, 11:29 PM

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

**Andy Pope** · 11-10-2008, 05:02 AM

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.

**teylyn** · 11-10-2008, 03:33 PM

Thanks Andy,

I had a feeling the solution would be down this track.

Teylyn

**acavrak** · 03-02-2009, 03:11 PM

hello;

Can you tell me step by step, as best you can. on how you added that other series to the X axis so that the months would appear?

Also is there a way in excel 2007 to have the month name appear on the x-axis only. So no day or year?

thanks

**teylyn** · 03-02-2009, 04:26 PM

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

**jfcarroll** · 10-16-2013, 09:27 AM

Thank you so much, especially for the Excel file example. I have been wanting to do this for years.

One item that was tricky for me: The Axis Labels are turned off, while the Data Labels for the "Date Axis" series are displayed. It took me a while to figure that out.

Thanks again!

**bclerget** · 11-23-2014, 06:14 AM

I have been reading this forum because I had a direct method for XY Scatter with monthly ticks on the Xaxis working in Excel 2003 but I had difficulties with Excel 2007. I have used your file to work again on this question and I have finally been able to use again my previous method in Excel 2007 now.
It is very surprising because it means that it exists an old bridge in the Excel codes forgotten by the developer itself that I have discovered by chance.
The method:
1. Start by creating your XY Scatter.
2. Modify the graphs type into Line. Yes do it!
3. Now Format the X axis.
- Choose the fixed minimum and maximum values.
- Major Unit is fixed: 1 month (or more is you prefer less ticks)
- Base Unit is fixed: Days.

And that is it. See the new graph in the file attached.
In the 'number' tab you can format the date as you like.

Hope it can be useful to others.

B. Clerget CIRAD, France

**tomdaniel** · 09-29-2016, 05:14 PM

I see this is an old thread, but just wanted to point out that B. Clerget's line graph method does not work for time series longer than one year. The line graph assumes all values are in the same year and plots successive years on the same one year axis, at least in my 2010 version of Excel. Back to generating an extra series for the x-axis labels - that does work!

**MrShorty** · 09-29-2016, 05:25 PM

@tomdaniel: It seems to work just fine for me for data series longer than one year. If I go into B. Clerget's spreadsheet and edit the data to span multiple years (may 2008 to april 2010), edit the manually entered axis limits (format x axis dialog to be 1 may 2008 to 1 may 2010), it seems to do just fine charting data spanning multiple years.

**tomdaniel** · 10-04-2016, 03:17 AM

To: MrShorty

Thanks for the prompt reply! It's taken me awhile to respond, because I wasn't able to get the technique to work on a time series plot I initially wanted to fix.

The problem is that I have two series that start at different times on the same axis.

If I change the plot to a line plot, then Excel assumes that the same time (X) axis applies to both plots, and it messes up the data pairs.

In this case, the technique DOES work IF you select only the earliest-starting time series and change ONLY that curve to a line plot. If the other series(s) stops after the one that starts first, I think you'll also have to fill in the first series with null values to the maximum x (time).

So, my problem is solved, and I've got the plot formatted as I wanted without having to generate a fake time axis!

Thanks to you and B. Clerget for helping!