Plot X And Y On Excel

Excel Graph X Y Coordinates
Plot X And Y Graphs On Excel
How To Plot X And Y On Excel

Use a scatter plot (XY chart) to show scientific XY data. Scatter plots are often used to find out if there's a relationship between variable X and Y.

If you have a scatter plot and you want to highlight the position of a particular data point on the x- and y-axes, you can accomplish this in two different ways. As an example, I’ll use the air temperature and density data that I used to demonstrate linear interpolation. In the scatter chart below, the blue line represents the available data points. The intermediate green point on the line was interpolated from the available data.

The different color patterns is the reason I want to manually plot the 'Xs'. However, I do not know how to determine the x-y coordinates of the cells containing the 'Xs'. I made a somewhat feeble attempt to record a macro that draws a line between the first two 'Xs' so I could see what needed to be done to complete the macro for the entire set.
Axis Titles is nothing but Horizontal Axis (X-Axis) and Vertical Axis (Y-Axis) Recommended Articles. This has been a guide to Excel Scatter Plot Chart. Here we discuss how to create Scatter Plot Chart in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles – Plots in Excel.
We will use the scatter plot matrix to do this. Enter the data into a worksheet as shown below. The data can be downloaded at this link. Select the data and the column headings. Select 'Correlation' from the 'Statistical Tools' panel on the SPC for Excel ribbon. Select the 'Scatter Plot.

It would be nice to know where that data point falls on the x- and y-axes, so let’s look at one of the ways to do that:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

The lines extending from the x- and y-axes to the interpolated point (x-value, y-value) can be created with a new data series containing three pairs of xy data.

Those pairs are as follows:

The first and second pair of data points comprise the horizontal line from the y-axis to (x-value, y-value) and the second and third points make up the vertical line extending upward from the x-axis.

So if we start with the data from our table of air density and temperature, then add a second series with those pairs of data (using a scatter plot with straight lines and markers), we get the following:

Then we can add a data label and change the horizontal and vertical lines to dashed lines for better readability:

Of course, since it’s a chart series it automatically updates. So if the desired x-value is updated, the horizontal and vertical markers update as well.

We could also use this method for multiple x/y pairs also:

I’ve used the above method for a long time, but the next method was recently introduced to me by Jon Peltier of PeltierTech.com. Instead of using a series to create the horizontal and vertical lines on the scatter chart, his method is to use error bars.

It still starts by adding a second series to the chart. But this time only select the individual x- and y-values to create a series with a single data point.

Next, we can add percent error bars to this single-point data series. To add error bars in Excel 2013/2016, with the data series selected, click the green plus sign to the right of the chart, select the box next to “Error Bars” and choose the percentage type.

Next, right-click on either the x- or y- error bar and choose “Format Error Bar”.

Change the direction to “Minus” and the percentage to 100.

Repeat the same for the error bar in the other direction to get the following chart:

Of course, you can also add a data label to this data point and change the color and/or style of the error bars.

For some time now, I’ve been interested in figuring out how to create a vector plot in Excel. I didn’t really have a need for it personally, but I thought it would be an interesting challenge and might be helpful to someone else. Here’s what I came up with, created completely in Excel:

I didn’t have any vector data handy, so I had to make some up. For this exercise our vector field will be defined by the following:

The first step was to define the grid for the vector field. I chose an evenly spaced grid with dimensions 0.3 x 0.3. I also decided, for simplicity, to place the tails of each vector, rather than the mid-point, on the grid. So, the first step then, was to create two columns for the x1 and y1 values, or the position of the tails of each vector.

In the columns immediately next to x1 and y1, I then created the x2 and y2 columns. These points will be the head or arrow end of the vectors. The head of the vector should be located at the position x1+f_x and y1+f_y. But, this absolute position makes the vectors REALLY LARGE relative to the grid spacing.

Typically, in a vector plot, what we care about is the relative size of the vectors compared with one another. To address this, I added a scaling factor in the calculation for x2 and y2. A value of 0.1 worked well in this case. The formula for x2 is shown below.

If you were trying to make a vector plot from existing data, you could add another column to multiply your data by a scaling factor.

Now that we have four columns of values that fully define the vectors, we can create our vector plot in Excel.

The vector plot is made from the scatter chart type in Excel. So, I started by inserting a blank one on the worksheet.

Each vector will be represented by a data series. To start populating the chart, I right-clicked on it and chose “Select Data” from the menu. Next, I added the new series by selecting the first row of x1 and x2 values as the “Series X-Values” and the first row of y1 and y2 values as the “Series Y-values”.

Next, I added an arrow head to the vector by changing its formatting. I selected the data series first, then navigated to the Chart Tools> Format tab to add the arrow head.

Now I have something that looks like a vector on my chart. I also made the plot area square so that it doesn’t skew the vectors.

All that’s left is to repeat this process for each vector. Easy, right?

I have better things to do than click buttons all day to add all the individual vectors to this chart. 🙂 I’m guessing you do too! So, let’s do it a little more smartly, shall we?

Instead of adding each vector manually by clicking buttons in Excel, I created a subroutine to add each series. The VBA for the subroutine is below:

Here’s what the subroutine does:

Activates the chart (mine was called “Chart7”) and selects the plot area
Starts a “For” loop to loop through each of the 49 rows of values.
Define the xvalues as the cells in the current row and columns C and D as a string that can be fed into the scatter chart
Define the yvalues as the cells in the current row and columns E and F
Add a new series to the chart using the x-values and the y-values
- Add the arrow head
- Make the color black
Finally, move on to the next row, until all 49 are done

The result, after removing the axis labels and adjusting the limits, is this:

Excel Graph X Y Coordinates

Looks like a vector plot, doesn’t it? 😉

Each row of x- and y-values from the table is represented as a vector, with the tail of each vector aligned to the pre-defined grid. To make the vector chart so that the midpoint of the vector was on the grid, I would have needed to define a third point for each vector.

Plot X And Y Graphs On Excel

What’s next?

I want to enhance the vector plot by adding a color scale so that the color of the vectors changes with their magnitude. Basically, the smallest vectors will be blue and the largest ones red. This will involve some linear interpolation with RGB values, like I’ve shown below:

Notes:

How To Plot X And Y On Excel

This method will only work for vector plots where the number of vectors is less than 255 because of Excel’s chart series limitation. Only 255 data series are allowed per chart.