In an Excel chart, how do you craft X-axis labels with whole number intervals?

Question

I have a sorted list of values that I'd like to see graphed in an Excel chart just to see the shape of the curve. Here is the initial chart:

Looks ok, but the X-axis is ugly, so lets clean that up a bit by spacing out the tick marks ("Interval between tick marks") and making the X-axis labels into nice round numbers ("Specify interval unit"):

Well that didn't quite work:

Is there any way to get X-axis labels like "0, 500, 1000, ..." or "1, 500, 1000, ..." without adding an extra column of data just for those labels? I'm going to be creating a lot of graphs and that will double the size of data for each one.

Answer 1

You can make the changes you'd like on a Value Axis, but the chart you've created doesn't use that, it uses a Category Axis. So, without changing your chart type, you can't do what you'd like, the version of Office is generally irrelevant for this issue.

You can approximate a Value Axis by using the Category Axis Date Option (as shown in your example), but you'll have to convert your data values to serial date values, then set a Min and Max (this is not a pretty option, but it is possible), and there's still no way to assign a 0 starting point, so the best you could do is still 1, 501, 1001,...

Generally, if you use a Line or Area chart, Excel makes the X Axis a Category Axis. If you create an XY (Scatter) Chart, it creates a Value Axis. John Peltier's Blog has a great discussion of the differences and even a few work-around suggestions for Filling under an XY Chart to mimic an area chart.

You'll probably find that the easiest way to get what your looking for (and keep the same chart appearance), is to do what you were trying to avoid, and include a helper data series for your label, then just include it on each chart. That will give you the most flexibility with the least effort and redundancy.

Answer 2

The easiest fix here is to make an XY Scatter chart, using the line without markers option. It won't be filled beneath the line, but you will have more control over the X axis.