How to Make a Graph on Excel With X & Y Coordinates

Microsoft Excel is a powerful tool for manipulating and displaying data, and producing an x-axis and y-axis graph using the software is a key skill for anybody presenting data. Only one type of graph presents the x and y values for a set of data on a graph in Excel: the scatter chart. It's easy to master scatter charts, though, provided you have the right type of data in your spreadsheet.

Image Credit: Thomas Barwick/DigitalVision/GettyImages

Data Format for Scatter Charts

To produce an Excel plot of x vs. y values, you need to have compatible data. Each point on a scatter chart represents one x value and one paired y value, so you need two columns (or rows) of data where the two neighboring points are related to each other. For example, suppose you have data showing how many lessons each student in a class attended and their scores on an end-of-year test. One column of the data would be the number of classes attended, and the next column would be the final test score, with each row representing a student. By default, the left column is the x-axis data, and the right column is the paired y-axis value. Continuing the example, list Mark's attendance in cell B2 and his test score in B3, and record Mary's attendance in C2 and her test score in C3, and so on.

As long as you have data for both the x-axis and the y-axis, you can make a scatter plot. You can also have multiple y values for each x value if you plot each set of y values as a separate series.

Create an XY Graph in Excel

When you have suitable data, it's easy to create an x- and y-axis graph in Excel. Highlight the two columns (or rows) of data you want to turn into a graph, noting that the left column represents the x-axis and the right column represents the y-axis. Go to the "Insert" tab and look for the "Charts" section. In Excel 2007 and earlier versions, click on "Scatter" and choose one of the options that appear in the drop-down menu. In Excel 2010 and later, look for the icon with dots plotted in the xy plane that says "Insert Scatter (X, Y) or Bubble Chart."

The simplest xy graph ("Scatter with only Markers") is the only option that works when your data isn't in x-axis order with the smallest x value first and increasing the largest value, but it also works when the is in x-axis order. The remaining options work best when your data is in x-axis order, and in these, neighboring points are connected by lines. Two options use smooth lines with or without markers and work best for plotting functions, and two use straight lines with or without markers and are best for data composed of separate values like the attendance vs. test score example above.

Choose a graph, and it displays based on the data you select. You can add another set of y-axis values by right-clicking the graph and choosing "Select Data" and then either expanding the range of cells in the "Chart data range" field or clicking "Add" in the "Legend Entries" section and selecting the x and y data sets for the second series. You can also use this dialogue box to switch around the x-axis and y-axis values.

Plotting Coordinates on an XY Graph

Use the same approach to produce an Excel plot of xy coordinates. If you have a two-dimensional set of coordinates such as the latitudes and longitudes of different locations, you can plot the locations of objects in this plane. For example, plot the longitudes of different locations in the first column so it is plotted onto the x-axis and their latitudes in the second column for the y-axis, including negative values as needed. Choose a "Scatter with only Markers" graph to get the best result.

references