# 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