EXCEL 2010 tick marks on horizontal axis.

Question

I have an XY chart, and the data on the horizontal axis spans from 4,998 to 11,001.  Through the "Format axis", "Axis options", I made the minimum and maximum values on the horizontal axis fixed at 4,000 and 12,000, with tick marks at every 1,000, as in Plot 1.  But that leaves a large part of the plot area unused, between 4,000 and 4,998, and between 11,001 and 12,000, and I do not like its appearance.  If I set the minimum to 4,998 and the maximum to 11,001, and set the major units to 1,000, there are tick marks at 4,998, 5,998, 6,998, etc., and end at 10,998, as in Plot 2, which is not what I want because I want ticks at easy-to-read labels, as in Plot 1.  In other words, I want to set the minimum to 4998, maximum to 11,001, tick marks at every 1,000, but put tick marks on the horizontal axis at 5,000, 6,000, 7,000, etc.  Does anybody know how I can do that?  I am using Excel 2010 with Windows 7.  Thanks.

Answer 1

Hello,

this can only be done with a helper series. With your second chart screenshot, add a data series whose Y values are all 0 and the X values are 5000, 6000, etc. Remove the X axis labels, tick marks and grid lines. Make the data marker of the new series a

• sign, so it sits on the X axis and looks like a tick mark. Add data labels for the value below the marker. Add error bars to emulate the vertical grid lines. 

Here is a screenshot with values that are not just 2 but 50 ticks off the full 1000 grid. Vertical grid lines are error bars, not chart grid lines.

Answer 2

That worked perfectly.  Thanks.