Originally Answered: Time scale on time series analysis in microsoft excel?
In a blank column, for instance, to the left of your x-axis labels, put the year next to the first instance of a given year-- so to the left of "2008 25.1" put "2008". Do this for each year. So, let's say your current x-axis labels are in column B starting at B2 (2008 25.1, 2008 25.2, 2008 25.3....)
Then in A2, you'd put 2008, in A14, you'd put 2007 (or whatever the next year is). You can do this with a formula (put the following in A2 and fill all the way down as far as necessary):
=IF(COUNTIF( B2:B$1000, LEFT(B2,4)&"*")= 12,LEFT(B2,4), "")
or just do it by hand.
Then, assuming you already have a chart made, right click on the graph itself, go to Source Data..., click the Series tab, at the bottom in the box labeled "Category X-axis labels", enter the new source column for your x-axis labels. In the example above, it would currently read something like =$B$2:$B$361 and you would change it to read =$A$2:$A$361. It will now use the contents of Column A instead of Column B as the labels. This will of course put the year in the January spot for each year. If you wanted it to appear in the middle, you could try putting it in either the June or July spot and then playing with the formatting of the X-axis to get it to look right. You can do this by right-clicking directly on the line for the X-axis and selecting "Format Axis..." Note that you can also change the alignment of the labels (vertically, horizontally, etc.)
As a LAST resort if the above doesn't work for you, you can always eliminate the x-axis labels entirely and replace them with one long, thin text box (from the toolbar on the bottom of the screen) in which you then enter from left to right:
"1979 1980 1981 1982 ... ", Kind of primitive, but it will work if you're desperate....
OK, same principle. Your data are in column B. Your labels are in Col A. I assume you're making a line graph, right? With temp on the y-axis and year on the x-axis?
Erase your years in Col A and put this formula in the top row (directly to the left of the temp for Jan 1980):
=IF(MOD (ROWS($2:2), 12)-1=0, 1980+COUNT (A$1:A1),"")
Note this first cell should be cell A2 (I am assuming you have a column header, like "year" in cell A1.
Fill all the way down (through the temp for Dec 2008).
Highlight both columns of data (year and temp). Insert Chart as usual. After the chart appears, right click on the x-axis to format it as I described above, go to the Scale tab, change the "number of categories between tick mark labels" to 1. Click OK. You will need to stretch out the graph wide enough so the years are not all smashed together. You can also, under Format Axis..., change the alignment of the labels.
It doesn't make any sense to me that you're using an X-Y scatterplot based on what you've described. Your x-variable (time) is a completely independent ordinal variable. There is no expectation that TIME will vary with TEMPERATURE, only the opposite. An X-Y scatterplot only makes sense when you are looking to see if the X and Y variables affect EACH OTHER. For instance, if you have 100 people and you want to look for a relationship between BMI and # of years of education completed, an X-Y graph is appropriate. In your case, your 30 years (or 360 months) of data is equivalent to those 100 people; it's NOT equivalent to # of years of education. Here's the distinction: in my example 2 or more people may end up with the same years of education but different BMIs, or 2 or more people may end up with the same BMI but different years of education. In yours, however, it is impossible that 2 avg. temps will end up in the same month category; only the reverse. Unless you have some other variable you didn't mention, your data is best suited to a line graph.