In Microsoft Excel 2010, I'm trying to draw a line graph with three different lines. I've got the graph looking the way I want, with one exception: Excel is treating my X-axis column like text labels, rather than data points along the X-axis. As a result, the gap between 0-85, 100-120, and 300-750 are all the same. How can I get Excel to treat these as numbers and space them accordingly along the X-axis?

Here is what I have so far:

enter image description here

In the Format Axis dialog box for the Horizontal Axis, I've got the format selected as "Number" with 0 decimal places:

enter image description here

And in the Axis Options tab, I've got the "Interval between labels" selected as "Automatic," and the "Axis Type" selected as "Automatically select based on data":

enter image description here

  • Are you using a line graph, or a xy-scatter plot (the correct choice?) – ja72 Oct 29 '13 at 19:53
up vote 7 down vote accepted

Make sure you have a scatter chart in Excel

Chart

Here is the difference:

Line Chart

Scatter Chart

You've discovered a subtle, but important difference in how Excel charts data. Generally, Line charts treat their horizontal (X) axis as categorical data, while Scatter (XY) charts treat theirs as numeric data. The result being, as you've discovered, that Line charts don't have the expected intervals on their axis when using numeric values on the horizontal axis.

There are two ways to address this shortcoming. The most efficient is to use a Scatter chart with lines connecting your points. This gives the appearance of a Line chart with the convenience of the values appearing at the correct intervals.

However, there are times when you may want to use a Line chart (e.g. your horizontal axis are dates and you want to use the built in aggregation for major and minor units to days/weeks/months). In this case, you'll need to add values to your horizontal (X) axis values, with no corresponding Y values (i.e. use =NA() for the "blanks", and Excel won't plot the values). Excel will provide an axis point for each value, it just won't be plotted.

EDIT: The same Chart can be created in either type, just with a differing amount of input data. Here's an example:

Line vs. Scatter

Neither of these solutions is the "correct" choice, but rather tools which give you flexibility to display your data as intended (once you get comfortable with the differences).

EDIT: Also, FWIW, your chart doesn't change values past Line Speed of 300. Unless you have a specific business reason to extend out to 750, it's worth considering shortening the horizontal axis to 300, and allowing the differences to be emphasized.

Line emphasized

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.