How do I plot scattered points on an Excel chart?

Q When using Excel to plot line graphs, it seems to require the points on the X ordinate are equally spaced. I often have experimental points that are not equally spaced, so I end up plotting them by hand on a Powerpoint slide. Can I convince Excel to plot a line graph using unequally spaced results?
Reg Austin

A You didn’t send an example spreadsheet or figures to illustrate the precise nature of your problem but we sense two possibilities here.

Read more: Q&A articles | PC tips

The first – and most obvious – is that you’re using a standard line graph, rather than a scatter graph. A standard line graph distributes categories evenly across the horizontal (X) axis, with values plotted on the vertical (Y) axis. A scatter graph (or XY chart), on the other hand, has two value axes, with points displayed where X and Y values intersect – allowing for uneven distribution along the horizontal (X) axis. Our suspicions that this is the cause are raised by your mention of ‘line graphs’.

If this is the case then all that’s required is to select the scatter graph type, rather than line graph when creating the chart. In Excel 2007 and 2010, for example, click Scatter in the Charts group of the Insert tab on the Ribbon and choose a preferred style. In Excel 2003 choose XY (Scatter) from the Standard Types tab of the Chart wizard.

If this doesn’t work then it’s likely that the values in your X axis are stored as text, rather than as numbers: this could have occurred if you copied and pasted the values from another document, say, or from a website. Fortunately, it’s easily fixed. In Excel 2007/10, first select the data range (the values in the X column) then click the Ribbon’s Data tab and choose Text to Columns from the Data Tools tab.

In Excel 2003 just select Text to Columns from the Tools menu. In all cases you’ll be faced with a Covert Text to Columns wizard that you’ll need to work through: this tells Excel how the text it is about to convert is formatted. As your X column is almost certainly full of numbers, select Delimited and then use Tab or Space as the delimiter character and click Finish. Now plot a scatter graph and the job should be complete.