The term XY graph refers to a graph where the values are plotted on the X and Y (horizontal and vertical) axes, but in particular, it includes mean scatter graphs and line graphs. Learning to produce these graphs with Microsoft Excel is crucial if you're going to use the program for data analysis.
Producing an Excel plot of X- and Y-axis points is achieved with built-in functions, and they're easy to find and use. After learning how to create the graphs, though, it's a good idea to learn a bit more about deciding which XY graph is best suited to your specific set of data.
Basic XY Graph: Scatter
Scatter graphs are what most people think of when referring to an XY graph. Points are plotted according to their positions on both the X- and Y-axis, essentially treating the two points as coordinates. You need two columns or rows of data that you want to plot. If you have a series of sequential values for one of the axes (increasing time, for example), it's better to use a line graph.
Video of the Day
Highlight the data and go to the Insert tab. For both newer versions of Excel (from 2013 onward) and older ones, the option you want is in the Charts group, but for the older versions it is labeled Scatter, while on newer ones it's just a scatter plot icon that says Scatter (X, Y) or Bubble Chart when you hover over it. Choose this option and then Scatter (new versions) or Scatter with only Markers (old versions) to bring up a graph.
The chart appears on-screen, along with Design, Format (in Excel 2013 and later) and Layout (before Excel 2013) tabs along the top of the screen. You use these tabs to adjust the axis labels, change the appearance of the plot, add trendlines and more.
Although most references to an XY plot have a scatter graph in mind, a line graph is a similar plot, and it may be more suited to your data. The key difference is that a series of data defined by positions on the X- and Y-axes is connected by a line running from the lowest to the highest x value. This makes it more suitable for cases where a clear sequential order to the data points is clear – if the x values represent months of a year, for example.
Creating a line graph in Excel is similar to creating a scatter graph. You highlight the data and go to the Insert tab. Choose the Insert Line or Area Chart (from Excel 2013 onward) option from the Charts group, which is shown by an icon with a set of jagged lines, or Line (on older versions), which also has the icon displayed above it. From there, choose the type of line graph most appropriate for your data. Line with Markers works well because you can see the specific data points and the overall progression represented by the line.
Deciding Which XY Graph to Use
The main challenge when you're making an XY graph in Excel is deciding which of the options to use. Generally speaking, line graphs are usually better when your independent variable is time, whether it's seconds, minutes, hours, days, months or years. This is because there is a definite progression from one data point to the next, and the connecting line captures the sequential nature. For example, you can create a line graph of monthly sales data for your store.
Scatter graphs are particularly good for looking for patterns in large data sets, particularly where there is no clear order to the data points. For example, you might make a scatter graph of the height of a specific type of plant against the distance from the crest of a hill. If there is a relationship between the two, the scatter plot shows a clear trend.