Creating diagonal matrix from one half of the matrix

Question

In excel sheet, I have computed one half of my diagonal matrix. Now, I would like to transpose the upper half so I can mirror it do the lower halfof the matrix. On the diagonal I have zeros. How can I achieve this? When I select the cell range of my upper half of the matrix and transpose it to the lower part it just does not work. My matrix should be 388X388 cells so You can imagine that I would like to avoid copying and transpozing each row individually. PS: I can not work with VBA. Thank you

EDIT:

I have tried the OFFET function according to the answer below, but I am still getting this message. What it says is basicly, that there is a problem with that formula, but it does not specify what is the source of the problem. In comments below I am attaching also a link to office support page, where you can find the exact translation of this alert message. I have also tried to use classic cell addressing A1, but with same result.

Answer 1

Try using the OFFSET function. This is a reference function that allows you to specify a cell that is a certain number of rows and columns from a starting cell. Combine this with ROW() and COLUMN() functions to get the value from later columns as you drag the formula down rows and later rows as you drag the formula across columns.

The simplest case is assuming you have data starting from A1:

You can then put this formula into cell A2: =OFFSET($A$1,COLUMN()-1,ROW()-1)

When you fill it into the remaining cells, it will transpose your matrix.

If your matrix doesn't start in A1, you'll just need to change the amount you subtract from COLUMN() and from ROW() to allow for where it actually starts.

You can also easily fill the formula into all blank spaces:

1. Enter the formula into A2
2. Select the entire matrix
3. Press Ctrl+g, then select Special, then select Blanks and click OK
4. Hold Ctrl and click on A2
5. Press F2
6. Press Ctrl+Enter