<--- Return to Excel Statistics Guide

Draw a Standard Vector Graph in Excel

Keith Greiner

August 9, 2020

This essay is about how to create a vector graph in Excel. With the creation of the graph, I also present a simple vector analysis in which two vectors are summed to show a third vector.

A vector analysis consists of one or more arrows that begin at the origin (0.0 , 0.0) of a graph, and extending out in the direction of a force, movement or action. If two forces are involved, the analysis allows the user to consider what would happen when there are two forces applied at different angles. Lets look first at an example.

A ship headed at a speed of eight knots, in a direction that is 10 degrees East of North. However, the ship is not actually moving in that precise direction because there is also an ocean current of two knots, pushing the ship off course from an angle of 70 degrees. What is the direction and speed that the ship is actually traveling?

The conditions can be graphed as shown above, with three arrows. The angle of the arrows from North, show the direction, and the length of the arrows indicates the speed.

Here, the blue line represents the course setting of the ship. It is 10 degrees East of North, and is 8 knots long. The red line represents the current. It is 70 degrees East of North and is 2 knots in length. The green arrow represents the combined direction and speed of the ship.

Lets see how this graph can be created in Excel, and how we can determine the direction and length of the green arrow. Excel does not have a wizard or guide that enables this type of graph, so this explanation will fill in where Microsoft left out critically important information.

Given that we know four things about the direction and speed of the ship’s course and the current, we first need to do some conversions so that our data will be in a usable form. The following segment of an Excel spreadsheet shows that conversion.

Here we see the speed of the ship’s course, and the current, in row 6, while we see the degrees of the set course in row 7. In row 8, the degrees have been converted to radians, because the Excel sine and cosine functions require radian units as input. You’ll see the Excel code for radian conversion, in row 14. Just keep in mind; the code you see in rows 14 through 16 is actually what is behind the calculations in rows 8 through 10.

So now, using the sine and cosine functions we find the x and y values that allow us to know the (x,y) point on the graph where we would plot a point representing the heading and direction of the ship, as well as the heading and direction of the current. For the ship, the (x,y) point is (1.3892, 7.8785). For the current, the (x,y) point is (1.8794, 0.6840).

A standard vector chart has an origin that has an (x,y) point of (0.00, 0.00). Therefore, presented as a table, the origin and ending points for the ship’s course may be shown in two 2x2 Excel tables as,

The combined speed and direction is simply found by adding these two tables, with the result shown below.

That is, 3.2686 = 1.3892 + 1.8794 and 8.5625 = 7.8785 + 0.6840.

Now, create a scatter chart in Excel, but don’t yet add any points You might be tempted to highlight the rows and columns to be graphed, and let Excel do the work of putting the points onto a graph. In my system, that approach doesn’t work. Therefore, avoid using the wizard, as it will give you an incorrect graph. Instead, open to the page that looks like (or similar to) the following.

On my computer the sequence is Insert --> Chart --> Scatter_Chart --> Straight_Lined_Scatter. You will now have a blank chart space with nothing in it. While hovering over the blank chart space, right-click Select_Data.

Here is what I see on my computer. The actual image is darker than shown below. When I start the Apple Grab program, the dialog box was greyed and stayed that way through the Grab progress.

Now click on “Add” and fill in the following with the location of your data instead of mine.

After clicking “OK” you should see a graph with a line that begins at the (0,0) point and extends to (1.3892, 7.8785). The line can be edited to be formatted as an arrow. Use the same procedures to add the line for the current, and the sum of the two, and you have the graph shown above.

Finally, you need to convert the sum line to degrees and length. The following image shows how I do it, with a segment from the Excel sheet.

<--- Return to Excel Statistics Guide