Discover The Difference Between Line And XY Scatter Charts In Excel

Tue 23rd August 2011

Once you've used Excel to work with data you may then want to create different types of charts. Once you've used column, bar and pie charts you may then explore line and XY scatter charts. This article describes how these two types differ and summarises the key features of each.

Once you've used Excel to work with data you may then want to create different types of charts. Once you've used column, bar and pie charts you may then explore line and XY scatter charts. This article describes how these two types differ and summarises the key features of each.

Excel users tend to discover Excel charts in the order shown in the chart wizard, so column, bar and pie tend to be used first, and then line charts. Users of line charts will note that the lines correspond to the tops of the columns in a column chart. If the horizontal axis shows time information, such as years, then the line chart can appear to show a trend. However the horizontal axis of a line chart, like column or bar charts, shows categories of equal spaces, with one category representing each data value in the corresponding axis range. The spaces are equal no matter what the axis values, so the line is only a true forecast or trend if the spaces are equal.

If you want to show a true trend or forecast the XY Scatter chart is a better bet. This chart type actually shows a plot of data on a vertical Y axis and a horizontal X axis. Perhaps the word "Scatter" is misleading - this suggests data points plotted all over the place on the chart. A better description would be XY Plot, as this chart type will plot a true vertical against horizontal set of figures. So if you arrange your data with the X axis range as numbers, the X axis will be spaced correctly. So if the X axis numbers are 2, 6, 8, and 10 then the spacing will be accurately shown. Contrast this with the same axis data in a line chart where the spacing will be equal with one space per axis value.

For an example, suppose you have an Excel table with two columns of numbers. The first column starts at cell D1 and has a blank label. The numbers 2, 6, 8, 10 are in the cells D2 to D5. Note that the numbers aren't evenly distributed. The second column starts at cell E1 with the label SALES. The sales numbers are 10, 20, 30, and 40 are in the cells E2 to E5. Now we'll build each chart type, starting with the Line chart. By the way if you leave the label off the first column of data, Excel will use that data for the Line chart horizontal axis.

So highlight the cells D1 to E5 choose the Chart Wizard and select the Line chart. Then pick a line chart type and click OK or Finish. The Line chart then appears. Note that the horizontal axis shows the numbers spaced evenly even though the numbers are not, and so the points plotted on the line show as a line, with the sales values evenly spaced. The Line Chart actually treats the horizontal axis data as text, so you cannot format the axis, for example to set maximum and minimum values.

Next we'll create the XY Scatter chart. So again highlight the cells D1 to E5, choose the Chart Wizard and select the XY Scatter chart. Then pick an XY scatter type and click OK or Finish to complete. The XY Scatter chart then appears. Note this time that the horizontal axis shows the numbers on a scale which includes the numbers in our first column, with the sales data showing correctly on the line with a kink, to take account of the gap in the first column of numbers. This is an accurate plot of the data. Because the horizontal axis is numerical, you can format the axis and set maximum and minimum numbers.

Perhaps the XY Scatter chart should be renamed and placed before the Line Chart option in the Excel chart wizard. That way users would learn to plot data using two axes and see an accurate plot. Line charts have their place if you want to emphasise how values vary between categories, but the term may be a little misleading.

Interested in finding out more about Excel charts? You might consider attending a training course. This can be a really effective way to boost your Excel skills in a relatively short time.

Author is a freelance copywriter. For more information on excel training courses, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1874-discover-difference-between-line-and-xy-scatter-charts-in-excel.html