Format Horizontal Axis to display first of each month

[Prevost]

Hi There. I know this question has been asked a million times but I can't seem to find an answer that works for me. I am using Excel 2013 and am trying to make a chart that displays energy use per month. I have 3 columns, the start date, the end date and the energy use. How I am setting it up now is that I choose either the start of end date and the energy use and the plot and x-y scatter chart with lines. It then creates a scale on the horizontal axis. What I want to do (and am wondering if this is possible) is to just display the first of each month on the major grid lines, so that there are effectively a months worth of time passing between each tick. I know that this will not make the tick spacing equal but I am wondering if Excel has already realized this and will plot the data accordingly. I would still like to display the data points so that the user can view the positioning of the point between two major grid line ticks and take a reasonable guess as to where in the month that point begins (or equally, where it ends).

Can anyone help me? Thanks!

 

[HappyMeal]

Hey Prevost!

Right click the horizontal axis > Format Axis..

In the Axis options click on fixed for all five sections (Minimum/Maximum/Major Unit/Minor Unit/Base Unit)
change the Minimum: to the date you want to start at 01/01/2014 for example
change the Maximum: to the date you want to end at 01/12/2014 for example
Major Unit: 1 Months
Minor Unit: 1 Days

That should get horizontal axis to display the first of each month.

Hope this helps,
HappyMeal

 

[Prevost]

That's what I am missing, I don't have a fixed button or box to click on! I have my chart with dates displayed on the horizontal axis and then when I click on the horizontal axis, I get the Format Axis menu on the right hand side and then I click Axis Options (the text) and then Axis Options and I can see the Minimum/Maximum/Major Unit/Minor Unit but there is no Base Unit box. Instead there is a display unit box. Is there something that I am missing?
Thanks!

 

[HappyMeal]

What type of chart is it? can you provide a screenshot of the chart and axis options?

HappyMeal

 

[Prevost]

I will get a screen shot tomorrow as I don't have that computer with me right now. The type of chart is an x-y scatter with lines.

 

[Prevost]

Okay, so here is some generic data that resembles what I actually have. I have used Excel 2011 for Mac but am trying to do the same on Excel 2013 for Windows 7. Here is a screen shot of trying to create a Scatter - Straight Marked Scatter chart. And that is the Axis options window I get. Excel defaults to a numeric horizontal axis with this type of chart for my data.

Here is when I use create a Line - Marked Line chart with the same data. Excel recognizes the dates as dates and plots it on the horizontal axis...any idea why the difference in date recognition for the two charts?

Thanks!

 

[Prevost]

So I figured it out for Excel 2013 on Windows 7...I had to choose a different type of chart, a line chart rather than a scatter chart (although I choose scatter chart with lines so it doesn't make any difference!). It was there like you said, but do you know what's going on for Mac Excel in the post above?
Thanks!