How to Calculate Eigenvectors in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

Eigenvector is related to linear algebra. When a set of equations forms a square matrix, then we will be able to form eigenvectors of that matrix. Eigenvector is very useful in differential equations and their associated applications. In this article, we will discuss how to form eigenvectors in Excel in detail.


What Are Eigenvectors?

A square matrix is a must requirement for Eigenvectors. It is a non-vector component that we get from the scalar multiplication of the given matrix. The equation related to the eigenvector is:

Av=λv 

A= Given a matrix of size n x n

v=Eigenvector

λ=Eigenvalue

It is called the Eigenvector equation.

The eigenvector is also known as the characteristic vector, and the eigenvalue is known as the characteristic value.


How to Calculate Eigenvectors in Excel: with Detailed Steps

The calculation of an eigenvector is a complex and time-consuming process mathematically. But here we need to do this in Excel. We will explain mathematical steps in this article and apply the vector formula of Excel to solve the mathematical steps. Look at the below section for details.


📌 Step 1: Insert Identity Matrix and Other Supporting Elements

First, we need to calculate the eigenvalues before going for eigenvectors. We already showed the mathematical expression eigenvector, that is,

Av=λv

or, Av-λv=0

or, v(A-λ)=0

Finally, v(A-λI)=0

As the A is a matrix, we need another matrix with the scaler λ. So, we multiplied λ by the Identity matrix. The Identity matrix will not change the value of the equation. In the calculation of eigenvalues, the determinant, det(A-λI) considered to be 0.

  • We will consider the following matrix.

Sample matrix for Eigenvectors

  • We insert an Identity Matrix, I of size 3×3 in the dataset.
  • Also, added other supporting components to the dataset.

  • Now, find out A-λI by putting the formula on Cell F10.
=B5:D7-I4*B10:D12

Matrix subtraction for calculating Eigenvectors


📌 Step 2: Calculate the Determinant Using Excel MDETERM Function

Excel has a default function, which is named MDETERM, for calculating determinants.

  • Now, we will find out the det(A-λI) using the following formula on Cell H5.
=MDETERM(F10:H12)

Calculate determinant of matrix for Eigenvectors


📌 Step 3: Enable Excel Goal Seek Feature

Now, we will apply the Goal Seek feature to determine the eigenvalues.

  • Go to the Data tab,
  • What-If-Analysis option from the Forecast group.
  • Then, choose the Goal Seek option.

Enable goal seek feature in Excel


📌 Step 4: Calculate Eigenvalues

As the given matrix is 3×3, we will get 3 eigenvalues. And for each eigenvalue, we will get one eigenvector.

  • We add new cells in the dataset for 3 eigenvalues and eigenvectors.

The Goal Seek window appears by following Step 3. We will insert cell references for two sections and set the determinant to zero (0).

  • Look at the following image to set the cell.
  • Finally, click on the OK button.

Calculate Eigenvalue for Eigenvectors

Here, the eigenvalue is the changing cell that we will get by using the Goal Seek feature.

  • After processing, we see the optimized eigenvalue.

This is the 1st eigenvalue.

  • Now, copy Cell I4 by pressing Ctrl+C.
  • We will paste this value on Cell L5 as λ1. Go to the Values option of the Paste Special section.


📌 Step 5: Find Eigenvectors for Each of the Eigenvalues

We already calculated the 1st eigenvalue. Now, we will find out the corresponding eigenvector of the calculated eigenvector.

  • Now, copy matrix A-λI using the following equation on Cell B15.
=F10:H12

  • We need to copy the 1st and 2nd column of matrix A-λI on Cell E15 using the following formula.
=F10:G12

We get a 3×5 matrix. This will be used in the calculation of the eigenvectors. Each eigenvector must be a column matrix. As the given matrix is of size 3×3, the eigenvectors will be of size 3×1.

  • We will use the MDETERM function to calculate the eigenvectors. Look at the formulas used on Cells G15:G17.

Formula in Cell G15:

=MDETERM(C15:D16)

In Cell G16:

=MDETERM(D15:E16)

In Cell G17:

=MDETERM(E15:F16)

Calculate Eigenvectors from eigenvalue

  • Now, optimize the eigenvector and set that on Cell L7 using the following formula.
=G15/MIN(ABS($G$15:$G$17))

Calculate Eigenvectors from eigenvalue

  • Now, use the paste-special technique to keep the values only.


📌 Step 6: Calculate Rest of the Eigenvalues and Eigenvectors in Excel

We will repeat Steps 4 and 5 again to get the rest of the eigenvalues and their corresponding eigenvectors.

  • Previously, set the value of λ as negative. Now, set this a positive value and apply the Goal Seek feature.

  • We get a new eigenvalue and eigenvector for that eigenvalue as shown before.
=G15/MIN(ABS($G$15:$G$17))

  • Now, set the eigenvalue to 0 and apply the Goal Seek feature again.

  • Finally, we get three eigenvalues and their corresponding eigenvectors.

Finally, we get V1, V2, and V3 are the 3 eigenvectors of three eigenvalues -6,15,3 respectively. All eigenvectors are column vectors.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we described the eigenvalues and eigenvectors. We showed the calculation of eigenvalues and eigenvalues in Excel in detail. I hope this will satisfy your needs. Please give your feedback and suggestions in the comment box.


<< Go Back to | Vectors in ExcelExcel for Math | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo