98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article
Excel articles
Discover The Difference Between Line And XY Scatter Charts In Excel
Tue 23rd August 2011
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
![](https://www.stl-training.co.uk/asset/image/icon/expand_off.gif)
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsStandard Chartered
Project Manager Carol Harwood Project Management - Framework & Processes Fantastic directions for getting here, the day started with no complications. Birmingham City University
Personal Assistant Carol Heeley Excel Intermediate Really enjoyed the day - thought some would be over my head, as I don't use excel so much for calculations but more for data management. Found the shortcuts extremely useful, covered some stuff I knew, but most of the stuff was new to me and didn't realise quite how much Excel could do for me! Lone Star Management Europe Ltd
PA Louisa Pantellis Excel Introduction Very good course and Paul is excellent as a teacher |
PUBLICATION GUIDELINES