0

I am trying to draw a chart in Excel using the following data:

------------
| x |   y  |
------------
| 1 | 1.5  |
------------
| 2 | 3.0  |
------------
| 4 | 5.9  |
------------
| 8 | 11.0 |
------------

Currently, if I use a line chart, then the data points on the x-axis will be spaced equally and the lines will not correctly show the trend of y where improvement is linear at first but slows down afterwards:

Line chart

If I use a scatter plot, then I can achieve correct spacing and correct lines showing the trend of y, but the data points on the x-axis will not just include the valid data points but rather, the whole range of 1 to 8 which is not what I want:

Scatter chart

The way I want the chart is to look is for the x-axis to only include the data points under the "x" column of the table, like the line chart, but space the points in a linear fashion so that the space between 2 and 4 will be double 1 and 2 and so on, like the scatter chart. Something like this (created by editing the scatter chart in Photoshop):

Desired chart

Is there any way to achieve this in Excel?

P.S. Even though I am a VBA novice, a VBA solution is also acceptable.

6
  • I'm not sure I understand the question. It sounds like maybe you're referring to the connecting lines between the data points. Showing just point markers is an option for a scatter plot. If that's not the issue, please add a screenshot showing what you're getting and describe what's wrong with it. – fixer1234 Jul 13 '19 at 4:50
  • Added images that show how the charts currently look and how I want the output to look. – didaz Jul 13 '19 at 5:55
  • 1
    There's no built-in way to do that. In a line chart, the X axis values are just category labels. In a scatter chart, the X axis values show the scale; they aren't intended as data labels. You would need to manually modify the chart to do that. You could: 1. leave off the axis labels, add an axis title, put the labels in the title, and then use spacing to make the values line up. 2. Let Excel label the axis normally, then cover the labels you don't want to see with white, borderless rectangles. (cont'd) – fixer1234 Jul 13 '19 at 6:43
  • 3. Leave off the axis labels, use the X values as data labels, and experiment with positioning (haven't tried that one, and don't have ready access to Excel to test it). – fixer1234 Jul 13 '19 at 6:44
  • Indeed after scouring the internet for hours with no useful results, I suspected this is likely not possible in Excel (even though this seems like basic functionality to me). I was also leaning towards your 2nd suggestion to hide the unnecessary numbers using white boxes but was hoping there would be a less tedious alternative. However, if some kind of VBA hack could produce such a chart or some (free) alternative software can achieve this quickly, I am open to suggestions. – didaz Jul 13 '19 at 6:55
0

I had explained the limitations in my answer.

Now I have understood that you want only scale to be appeared on the X-Axis.

In that case, dont select the Line Option but do asbelow :

  1. Select the Data
  2. Select the option Insert on the Top
  3. Select SCATTER
  4. Select 2nd Option : " With Smooth Line and Markers"
2
  • Please read the question carefully and look at the examples shown. This doesn't address what was asked. The question explains why this is not what is desired. – fixer1234 Jul 16 '19 at 0:16
  • This should have been a modification to your original answer. This answer by itself does not answer the author's question. Answers are not "responses" to a discussion, an answer should answer the question, and this answer does not do that. – Ramhound Dec 26 '19 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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