If you’ve ever worked with matrices in Google Sheets, you might have wanted to pull out just the diagonal elements—the ones running from the top-left to the bottom-right. Most solutions you’ll find online are long, messy, or rely on scripts. But the good news? There’s a much simpler way to do it, and it works for numbers, text, or even a mix of both.

In this tutorial, I’ll walk you through a clean, easy-to-understand method using Google Sheets’ MUNIT, IF, TOCOL, and ARRAYFORMULA. By the end, you’ll be able to extract diagonal values from any matrix—square or rectangular—without breaking a sweat.

Example Dataset

Let’s start with a sample 5×5 matrix:

The diagonal elements are 1, 7, 13, 19, 25. Our goal is to pull these into a single column or row.

Step 1: Understand the Diagonal

In a square matrix, diagonal elements are where the row number equals the column number:

• B2 → row 1, column 1 → diagonal
• C3 → row 2, column 2 → diagonal
• D4 → row 3, column 3 → diagonal
• E5 → row 4, column 4 → diagonal
• F6 → row 5, column 5 → diagonal

All other numbers sit off the diagonal.

We can isolate diagonal elements by combining our matrix with an identity matrix using IF logic. An identity matrix has 1s along the diagonal and 0s elsewhere.

Example:

By checking where the identity matrix equals 1, we can keep the diagonal values and leave non-diagonal positions blank.

This approach works for numbers, text, or mixed data—unlike simple multiplication of a matrix with an identity matrix, which only works for numeric matrices.

Step 2: Extract Diagonal Elements Using MUNIT in Google Sheets

Google Sheets has a neat function called MUNIT that generates an identity matrix. Here’s the logic:

1. Create an identity matrix of the same size as your matrix: MUNIT(size).
2. Use IF logic to keep the elements where the identity matrix equals 1 (the diagonal) and leave all other positions blank.
3. Only diagonal elements remain.

For our 5×5 matrix, the formula looks like this:

=ArrayFormula(IF(MUNIT(5), B2:F6, ))

This will give you:

Step 3: Make It Work for Any Matrix Size

What if your matrix isn’t square? No problem. We can dynamically detect the size:

MAX(COLUMNS(B2:F6), ROWS(B2:F6))

Then, update the formula:

=ArrayFormula(IF(MUNIT(MAX(COLUMNS(B2:F6), ROWS(B2:F6))), B2:F6,))

Step 4: Flatten Diagonal Elements

To get all diagonal elements in a single column, use TOCOL:

=ArrayFormula(TOCOL(IF(MUNIT(MAX(COLUMNS(B2:F6), ROWS(B2:F6))), B2:F6,), 1))

Output:

1
7
13
19
25

Want it in a row instead? Swap TOCOL with TOROW.

Step 5: Simplify with LET

If you plan to reuse this formula often, you can make it cleaner using LET:

=ArrayFormula(LET(r, B2:F6, TOCOL(IF(MUNIT(MAX(COLUMNS(r), ROWS(r))), r,), 1)))

This keeps your formula readable and easier to maintain.

Why This Works So Well

• Simple: No complicated nesting or scripts needed.
• Dynamic: Works for any size matrix.
• Flexible: Handles numbers, text, or a mix.
• Readable: Anyone looking at your sheet will immediately understand it.

Most online solutions rely on QUERY, MMULT, or scripting, and often only work with numeric data. This method is straightforward, efficient, and works in any scenario.

Practical Applications

• Numeric matrices: Extract totals or metrics along the diagonal.
• Text matrices: Pull out labels or headers from the diagonal.
• Mixed matrices: Works when you have both numbers and text in the same matrix.

Related Tutorials

• Find Min or Max in a Google Sheets Matrix and Return Row Data
• Sum of Matrix Rows or Columns Using SUMIF in Google Sheets
• How to Use the MMULT Function in Google Sheets