Microsoft Excel does not have the built-in worksheet functions to calculate the cross product of two vectors. Therefore, some techniques are required to do the calculation — either by setting up your own formula in a worksheet or by creating a custom VBA function. The article will walk you through both techniques.

Math Recap – Cross Products with 3D Components of Vectors

Let’s begin with a quick recap of the basics of the math operation for the multiplication of two vectors in a three-dimensional space.

We have two vectors a and b, where i, j, k are standard basis vectors. (a₁, a₂ and a₃ are vector components of a, and b₁, b₂, b₃ are vector components of b.)

(Note: some people may be more used to reading a_x, a_y and a_z instead, which represent the vector components along the xyz axes. In this article, let’s sick with the 1-2-3 convention as it’ll make working in Excel easier.)

The product of vectors a and b can be presented in matrix form as below (also known as a “format determinant”):

Next, the matrix can be expanded with Sarrus’s Rule into:

Lastly, we further expand it with cofactor expansion:

Here, ((a2b3 – a3b2) , (a3b1 – a1b3) , (a1b2 – a2b1)) is actually the end point of the cross product vector.

In order to remember this sequence for calculating the 3 components for such an end point, you can memorize the mnemonics “xyzzy” or “12332”, which means:

1. The first group (x) is  23 minus 32
2. The 2nd group (y) is adding 1 to each number: 31 minus 13
3. The 3rd group (z) is adding 1 to each number in the 2nd group: 12 minus 21

We are going to use this sequence shortcut during the development our VBA custom function a later section of this article.

Direction of resultant vector To find the direction of the resultant vector, you can use the right-hand rule.

Another case of cross product is with the scalers (magnitudes) of vectors a and b, and the sine of the angle between a and b.

Because the calculation of this is very straightforward, this will not be covered in this article. We’ll concentrate of the calculation with vector components.

Download examples

First, download a sample workbook with the examples in this article.
(Please enable macros when using the file)

Vector Cross Product in Excel

We can set up our worksheet to calculate cross product with Excel formulas. Because Excel does not come with built-in functions for calculating cross products, we have to setup the calculations in the worksheet carefully.

Excel setup for scenario 1 – with 3D components of vectors

See in the picture below the Excel formula in cells C7, D7 and E7 are set up:

The result looks like this:

So, we have the answer of: a x b = -3i – 6j -3k

Cross Product with VBA

To avoid the complexity with setting up the formula in Excel every time you need to calculate the cross product, you can create your own VBA custom function.

The macro “CrossProduct” below uses the mnemonics of “12332” (or “xyzzy”) with a For-Next loop to calculate the three vector components of the cross product.

The array “a” in the function stores the mnemonics in 3 groups of 4 items: “2332”, “3113” and “1221”, which are used to construct:

1. a₂b₃ – a₃b₂
2. a₃b₁ – a₁b₃
3. a₁b₂ – a₂b₁

Function CrossProduct(v1 As Variant, v2 As Variant) As Variant
Dim i As Integer
Dim x As Integer
Dim a
Dim answer(1 to 3)  'define a array with 3 slots from 1 to 3
'this array contains the sequence of vector components
'with 3 groups of 4 index numbers
a = Array(2, 3, 3, 2, 3, 1, 1, 3, 1, 2, 2, 1)
For i = 1 to 3
    x = (i - 1) * 4
    answer(i) = _
        v1(a(0 + x)) * v2(a(1 + x)) - v1(a(2 + x)) * v2(a(3 + x))
Next
CrossProduct = answer
End Function

Because the function returns an array (instead of a single value), when applying it in the worksheet, we have to apply it as a Array Function. See in the picture below, range C7:E7.

• First, select range C7:E7
• Type =CrossProduct(C4:E4,C5:E5)
• Finish by pressing CTRL + SHIFT + ENTER.

(Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.)

The macro can be enhanced to give you an option to output the answer in either horizontal or vertical orientation.

In the enhanced VBA function below, an optional argument “orientation” has been added, with a default value of 1. A “Select Case” statement has also been added, so that:

• When orientation  = 1, return answer in horizontal orientation
•  When orientation  = 2, return answer in vertical orientation

Function CrossProduct(v1 As Variant, v2 As Variant, _
    Optional orientation As Integer = 1) As Variant
Dim i As Integer
Dim x As Integer
Dim a
Dim answer(1 to 3)  'define a array with 3 slots from 1 to 3
'this array contains the sequence of vector components
'with 3 groups of 4 index numbers
a = Array(2, 3, 3, 2, 3, 1, 1, 3, 1, 2, 2, 1)
For i = 1 to 3
    x = (i - 1) * 4
    answer(i) = _
        v1(a(0 + x)) * v2(a(1 + x)) - v1(a(2 + x)) * v2(a(3 + x))
Next
'to control the orientation of output
Select Case orientation
    Case 1  'default, horizontal
        CrossProduct = answer
    Case 2  'vertical
        CrossProduct = Application.Transpose(answer)
End Select
End Function

We can now apply the VBA function to return the answer in vertical orientation. In the screenshot below, we can see the value of 2 has been assigned to the last argument in the function.

Conclusion

In this article, we have discussed the calculation of the cross product of two vectors. The challenge actually also applies to other cases of matrix calculations, for instance, adjoint matrix, cofactor matrix, determinant, matrix multiplication, etc.

While Excel provides no built-in features for working with such type of mathematics, these can be tackled with formulas as well as VBA custom functions (together with array function input techniques).