# How can I fill the diagonal of a matrix with zeros?

I have an empty matrix:

I would like to fill the diagonal of a matrix with zeros:

Is there any clever way to do it?

The matrix can be pretty large and it's tedious to do this manually. I could use some AutoHotkey scripts but I wonder whether there is some better way. I am aware of the question How can I dynamically format the diagonal cells in Google Spreadsheet? but I want out whether filling the diagonal with zeros can be done without scripting.

• You could compare if (B\$1 = \$A2). If yes, then 0 else "". And then paste to all, after that copy and paste values only. – Aprian Mar 28 '14 at 1:51
• Assuming that you always have identical sets of row and column labels. – MaryC.fromNZ Mar 28 '14 at 11:10

This formula in essence will do that.

## Formula

``````=ARRAYFORMULA(IF(COLUMN(B1:S1)=ROW(A2:A19), 0, ""))
``````

## Explained

The `IF` statement, in combination with the `ARRAYFORMULA`, validates whether the column indices match the row indices. When they do, show a zero and in all other cases, just show nothing.

## Note

Now that you have the solution you want, you're not able to make use of it by adding something in that range. If you do, it will destroy the `ARRAYFORMULA`. Making a print-out seems like the only benefit.

• Nice solution. And once the 0's are in, you can always copy/paste values so you can manipulate the rest of the range. I agree with your note, if you have other data in the range before you set the diagonal 0's, you're boned. – OnenOnlyWalter Aug 5 '14 at 0:31

The proposed formula seems to be working only if the top-left corner of the matrix is in A1. It would be nice to have something that also takes an already existing matrix and sets only the diagonal to 0, leaving all other elements unaltered.

So I put together this improvement:

``````=arrayformula(if((row(B14:L24)-row(B14)) = (COLUMN(B14:L24)-COLUMN(B14)),0, B14:L24))
``````

which preserves the original matrix in B14:L24, and sets only the diagonal to 0.