Date Axis with Centered Years
by Jon Peltier
Thursday, January 15th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In The Increasing Patriotism of Iraqis (actually just a comment on the labeling of years on x-axes of graphs), Andrew Gelman showed the following chart from The Increasing Patriotism of Iraqis. The chart came from a paper by the author of The Monkey Cage blog.
Andrew commented that the X axis should have years centered between the tickmarks, since a year is a span of time, not an event.
I decided to demonstrate how to produce a date scale axis with centered year labels in Excel. Using the following simplified data, I reconstructed the chart of Elkins and Sides as an Excel line chart.
The year labels have a number format of “YYYY” so only the four-digit year is shown. If the months and days are also shown, we see that the year labels actually appear at the start of each year (which is evident if we examine the data).
To “fix” the labels, I will add a second line chart series, move the original series to the secondary axis, and only display the primary axis year labels. Below is the data used for this second series, as well as the chart with this series added. Although the X values (category labels) are different for this second series, in a line chart, all series in each axis group use the same labels as the first series in the axis group.
Move the original series to the secondary axis (secondary axis group) by choosing Secondary in the Format Series dialog. Note that both series now use the X labels of the second series.
When you move a series to the secondary axis group, Excel usually provides a secondary value (Y) axis but not a secondary category (X) axis. Delete the secondary Y axis and add a secondary X axis. This axis appears at the top of the chart, overlapping with the title.
Adjust the scale of the secondary date axis, so it starts at the beginning of 2004 (i.e., January 1) and ends at the start of 2008.
Finally, hide the secondary category axis by changing all of its formatting properties to None. Also hide the second series by chaning its line and marker properties to None.
Without too much muss and fuss, we’ve created a nicer date scale axis, with the year labels centered within the corresponding years, rather than at the start of each year.
Related Posts:
- Clustered-Stacked-Column Combo Chart With Lines
- Area Chart – Invert if Negative
- Individually Formatted Dual Category Labels
- Area Chart With Gap
- Integer Values on Line Chart Category Axis
- Clustered-Stacked Column Charts with Vertical Separators
- Why Are My Excel Bar Chart Categories Backwards?
- Growth Rates in a Panel Chart
- Clustered-Stacked Bar Charts
- US Employment Slump Chart – How To
Posted: Thursday, January 15th, 2009 under Chart Axes.
Comments: 11
Comments
Comment from Andy Pope
Time: Thursday, January 15, 2009, 9:34 am
Hi Jon,
How about this alternative which uses a xy-scatter chart with a series for data, fake tick marks and fake axis labels.
Assumes original dates and data is in A2:B5
in A6:A10 enter date for 1st of each year 2004 to 2008. In C7:C9 enter zero.
in A11 enter the formula =A6+((A7-A6)/2) . and copy down to A14.
in D11:D14 enter zero.
Now create a xy-scatter, markers only, on the range A2:D14. Format 1st series to have a Line.
Format 2nd series to have small cross as marker
Format 3rd series to have X value data label but no marker. Align data labels below and set number format to YYYY.
Final format X axis to start 1/JAN/2004 and end 31/DEC/2007. No tick marks or labels required.
Comment from Jon Peltier
Time: Thursday, January 15, 2009, 9:42 am
Andy -
I use both ways in my own work. In Excel 2003 the XY series option is a nice one, because the XY series can coexist on the line chart’s date axis, and there’s no need to use the secondary axis. But sometimes it’s easier to let an axis provide its own labels, as above.
In Excel 2007, an XY series ignores the line chart date axis even when both are plotted on the primary axis and there should be no confusion. This has broken a good number (a bad number?!) of client solutions for no good reason. You can still plot the XY series on the secondary axis, but now the axes must manually be synchronized, and you lose the ability to use the secondary axis for another purpose.
Comment from derek
Time: Thursday, January 15, 2009, 2:03 pm
Andy, I tried a pure line chart version of your idea earlier today, using the “all in one column” method of faking multiple x ranges. and adding a third series to define the ends of the scale to avoid manually changing the scale. My write-up was interrupted by the need to get some work done, after which you posted. Darn that paying job :-)
My intuition, that it would be no more fuss than Jon’s method, was very wrong; for instance, I had to go into Tools.. Options to make the line interpolate between points, and so on. The only advantage left was that in the end I had something reasonably robust if frequently-changing data was to be applied to the chart: it had one axis left on auto instead of two axes that needed manual aligning.
Jon, would a line chart version also break in Excel 2007?
Comment from Jon Peltier
Time: Thursday, January 15, 2009, 5:33 pm
Derek -
If I understand your approach, it should work fine in 2007. Since it entails multiple series of the same type using the same axis, the problem I’ve butted against is not a problem.
Comment from Bob
Time: Thursday, January 15, 2009, 7:21 pm
We just downgraded to Office 2007 at work.
A very complex chart / project timeline tool was rendered obsolete by the Excel 2007.
Joy and bliss here.
At least everyone is in the same boat…
Bob
Comment from Jon Peltier
Time: Thursday, January 15, 2009, 7:40 pm
Bob -
You have my condolences. I have had a few projects obsoleted by 2007. I also have been able to salvage some complex charts, often by resorting to the “hard” way of achieving an effect or by sacrificing something else.
Comment from Andy Pope
Time: Friday, January 16, 2009, 4:09 am
Derek,
I wrote xy-scatter rather than Line chart because as you point out the actual data line becomes broken otherwise.
Comment from Jon Peltier
Time: Friday, January 16, 2009, 6:59 am
Andy -
Except as Derek pointed out, you can change how blanks are treated and avoid the gaps. I am thinking that Derek’s technique is probably the best discussed here. I think I’ll have to rewrite the post, put all of the techniques here, and put his first.
Comment from Andy Pope
Time: Friday, January 16, 2009, 7:21 am
The line chart is easier.
If you fill column B’s empty values with =NA() you do not need to go via Tools > options…
Comment from Jon Peltier
Time: Friday, January 16, 2009, 7:58 am
Andy -
I think a single trip to Tools > Options (or wherever it’s been moved to in Excel 2007) is easier and cleaner than filling blank cells with formulas.
Comment from derek
Time: Friday, January 16, 2009, 8:15 am
My data layout was like this. Apologies for the eccentric method of presenting the info, as I’m between ISPs at the moment. Does anyone know of a file hosting service that’s as convenient for spreadsheets as flickr, photobucket et al. are for pictures?
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.