Basic Concepts of Matrices

Definition 1: An r × c matrix (also called an array) is a rectangular array (or table) with r rows and c columns. We can represent such a matrix as A = [aij] where 1 ≤ ir and 1 ≤ jc. Thus aij is the element in the ith row and jth column.

In Excel a matrix is represented as a rectangular range. E.g. B3:C7 can be considered to be a 5 × 2 matrix.

Two matrices A = [aij] and B = [bij] are equal, denoted A = B, if they have the same size and shape (i.e. the same number of rows and columns) and all the corresponding elements are equal (i.e. aij = bij for all i, j with 1 ≤ i ≤ r and 1 ≤ j ≤ c).

Definition 2: A square matrix is one where r = c. The main diagonal of a square k × k matrix A is a11, …, akk. The trace of A is a11 + … + akk. If all the elements in the square matrix A are 0 then the matrix is called the null matrix. If all the elements on the main diagonal are 1 and all other elements are 0 then the matrix is called an identity matrix, denoted I (or Ik to emphasize that it is a k × k identity matrix).

A triangular matrix has either all zeros in the triangular portion below the main diagonal (an upper triangular matrix) or all zeros in the triangular portion above the main diagonal (a lower triangular matrix). A diagonal matrix is one that is both an upper triangular matrix and a lower triangular matrix, i.e. all the entries off the main diagonal are zero.

Example 1: Define matrix A = [aij] as follows:


A is a 3 × 3 square matrix whose main diagonal is 3, 5, 2. The trace of  is 3 + 5 + 2 = 10. The element in the 2nd row and 3rd column is a23 = 4. A is an upper triangular matrix.

Definition 3: A vector A is an r × c matrix in which either r = 1 or c = 1. If c = 1 then A is called a column vector. If r = 1 then A is called a row vector. We can represent a column vector as [ai] where 1 ≤ ir and a row vector as [aj] where 1 ≤  jc.

When r = c = 1 then A is called a scalar. In this case A is the same as an ordinary number (thus [a] is considered to be the same as a).

If A is a row vector [ai] the length of A, denoted ‖A‖ is

Length matrix

(and similarly for a column vector). A unit vector has length 1. To normalize a vector is to divide the vector by its length so as to yield a unit vector.

Excel Functions: Excel provides the following functions, where we use the same symbol  A for both the matrix and the range in the worksheet which contains the matrix.

ROWS(A) = the number of rows in matrix A

COLUMNS(A) = the number of columns in matrix A

Also note that the element in the ith row and jth column of A, i.e. aij, is given by the formula INDEX(A, i, j).

Real Statistics Excel Functions: The following supplemental Excel functions are included in the Real Statistics Resource Pack:

ISCELL(A) = TRUE if A is a scalar and FALSE otherwise

ISSQUARE(A) = TRUE if A is a square matrix and FALSE otherwise

LENGTH(A) = the length of matrix ; i.e. =SQRT(SUMSQ(A))

NORM(A) = array function which outputs a normalized version of array A; i.e. = A/LENGTH(A)

DIAGONAL() = array function which outputs a square matrix whose main diagonal consists of the elements in A and which has zeros everywhere else

DIAG(A) = array function which outputs a column vector with the diagonal of A

TRACE(A) = trace of A

IDENTITY() = outputs an identity matrix of the highlighted square shape

MPOWER(A, n) = An

You can also explicitly identify the size of the identity by using the formula IDENTITY(k). In this case the output is a k × k  identity matrix.

Example 2: Find the length of the vector A in range B4:B7 of Figure 1 and normalize this vector.

Normalize matrix Excel

Figure 1 – Normalizing a vector

The result is shown in Figure 1. The length of A is shown in cell B9, which contains the formula =SQRT(SUMSQ(B4:B7) or the supplemental formula =LENGTH(B4:B7). The value for this example is 8.124.

To normalize vector A simply divide each element in A by its length. E.g. cell D4 contains the formula =B4/B$9. The normalized vector, A′ is shown in the range D4:D7. The length of this vector is 1, as can be seen in cell D9.

Example 3: Create a column vector with the elements on the diagonal of the matrix in the range A5:D8 of Figure 2:

Diagonal matrix extraction

Figure 2 – Extracting the elements from the diagonal of a square matrix

Using the approach from Table Lookup, we can create a set of indices 1 to 4 (since the matrix is 4 × 4) and then the vector of diagonal elements contained in range G5:G8 is calculated by the array formula:


9 Responses to Basic Concepts of Matrices

  1. Einar says:

    Thank you for this tutorial! It is great. I was a bit confused at first by the:


    But then I realized that F5:F8 meant that the formula was to be repeated for each row in G in turn.

  2. vicky prasetia says:

    sir, i have question for you. How to create matrix like identity matrix from random matrix?

    • Charles says:

      I don’t quite understand your question. By a random matrix do you mean a matrix whose elements are random numbers? By create do you mean in Excel?

  3. Sajjad says:

    Can I have fundamental concept of matrices. I mean why do I need Matrices in my life and how I am going to use matrices in my every day life.

    As I can use Trigonometry for building a accurate Building or A Robot. ( As I can use trigonometry to calculate accurately angles and Ratios of Physical objects)

    • Charles says:

      Most people probably don’t need to use matrices in their every day life, but if you want to perfrom certain statistical analyses matrices is very helpful.

  4. Jonathan Bechtel says:

    Hi Charles,

    For your example of normalizing the Vector A [6, -1, 2, 5] the corresponding values [0.738, -.12309, 0.246, 0.614] are correct but they do not add up to 1. They add up to approximately 1.477.

    Is this a typo or is it something bigger that I’m not understanding?

    Thank you.

    • Jonathan Bechtel says:

      Actually, nevermind, I understand now. If you determine the length of that vector by taking the square root of the summation of their squares then that equals 1.

      Got it.

  5. george rosensteel says:

    is there an Excel array function to determine the eigenvalues of a square, real symmetric matrix?

Leave a Reply

Your email address will not be published. Required fields are marked *