DIAG

=DIAG(array,[k])

Download the Template

In the template file, navigate to the Matrices worksheet to see the DIAG function in action.

Description

The DIAG function is used to convert a vector to a Diagonal matrix, or vice versa. A Diagonal matrix is a matrix where all of the off-diagonal elements are zero.

If array is a vector (row or column), the function returns a diagonal matrix D with the vector making up the elements along the main diagonal. When using the k parameter, the matrix is square but resized to n+|k| x n+|k| with the vector on the kth diagonal.

If array is a matrix, the function returns a vector with elements from the kth diagonal of the array.

Multiplying by a Diagonal Matrix Scales

Multiplying an nxm array by an mxm Diagonal matrix will scale each of the columns of the original array by the corresponding diagonal elements. For example, if you multiply the ONES(3,3) matrix by DIAG({1;2;3}), you will end up with {1,2,3;1,2,3;1,2,3}. The first column is scaled by 1; the second column by 2; the third by 3.

=MMULT(ONES(3,3),DIAG({1;2;3}))
={1,2,3;1,2,3;1,2,3}

TRACE of a Matrix

The TRACE of a matrix is the sum of the diagonal elements, so...

TRACE(matrix) = SUM(DIAG(matrix))

Lambda Formula

This code for using DIAG in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Convert a Vector to a diagonal Matrix or vice versa, using the kth diagonal.
* k=0 (default) main diagonal, k>0 above, k<0 below
*   Vector -> (n+|k|)x(n+|k|) matrix with vector on kth diagonal
*   Matrix -> kth diagonal vector
*/
DIAG = LAMBDA(array,[k],
LET(doc,"https://www.vertex42.com/lambda/diag.html",
    version,"1/2/2026 - Added [k] option",
    nR, ROWS(array),
    nC, COLUMNS(array),
    k, IF(ISOMITTED(k),0,k),
    isVector, OR(nR=1, nC=1),
    IF(isVector,
        LET(
            v, TOCOL(array),
            nV, ROWS(v),
            m, nV+ABS(k),
            MAKEARRAY(m,m,LAMBDA(i,j,
                IF( j-i = k,
                    LET(p, IF(k>=0,i,j),
                        IF(AND(p>=1,p<=nV),INDEX(v,p),0)
                    ),
                    0
                )
            ))
        ),
        LET(
            nK, IF(k>=0, MAX(0, MIN(nR, nC-k)), MAX(0, MIN(nR+k, nC))),
            i_0, IF(k>=0,1,1-k),
            j_0, IF(k>=0,1+k,1),
            IF(nK=0, NA(),
                MAKEARRAY(nK,1,LAMBDA(t,_,INDEX(array,i_0+t-1,j_0+t-1)))
            )
        )
    )
));

Name: DIAG
Description: Return the Diagonal matrix or vector for a given array
Arguments: array
Function:

LET(doc,"https://www.vertex42.com/lambda/diag.html",
    r, ROWS(array),
    c, COLUMNS(array),
    IF(c=1,
        MAKEARRAY(r,r,LAMBDA(i,j,IF(i=j,INDEX(array,i),0))),
        IF(r <> c, "Error: Not Square",
        MAKEARRAY(r,1,LAMBDA(i,j,INDEX(array,i,i)))
        )
    )
)

DIAG Examples

Test: Copy and Paste this LET function into a cell
=LET(
    vector, {1;2;3;4},
    DIAG(vector)
)

Result: {1,0,0,0;0,2,0,0;0,0,3,0;0,0,0,4}

=LET(
    matrix, {1,0,0,0;0,2,0,0;0,0,3,0;0,0,0,4},
    DIAG(matrix)
)

Result: {1;2;3;4}

=LET(
    k, 1,
    array,
      {"A1","B1","C1";
       "A2","B2","C2";
       "A3","B3","C3"},
    DIAG(array, k)
)

Result: {"B1";"C2"}

Test: Copy and Paste this LET function into a cell
=LET(
    matrix, {"A",1,2;1,"B",2;1,2,"C"},
    DIAG(DIAG(matrix))
)

Result: {"A",   0,   0;
           0, "B",   0;
           0,   0, "C" },

Revision History

• 1/2/2026 - Added the optional kth diagonal parameter to the Excel version. No longer returns error for a non-square matrix.