![transpose an array in excel transpose an array in excel](https://www.get-digital-help.com/wp-content/uploads/2015/06/How-to-use-the-TRANSPOSE-function.png)
There are two reasons for this step: Getting to know the exact range for the transposed cells and at the same time already having the formats of the original cells. Now, instead of values, just the original formatting is transposed.
![transpose an array in excel transpose an array in excel](https://i.stack.imgur.com/Lkc8m.png)
So if the cell range of your source data changes, you usually have to set up the array formula again.įollow these steps for setting a the array formula for pasting cells linked to their sources and transposing them (the numbers are corresponding with the picture on the right hand side): Furthermore it comes with the disadvantages of all the array formulas: Their size is not (easily) changeable. Despite the advantage of not having to do anything manually, this way is slightly more complicated. With an array formula you don’t have to drag and drop cells manually. Steps for pasting as links and transposing in Excel at the same time The formula in the example above looks like this: =OFFSET($B$2,F$1,$E2) That’s why we fix the column but not the row. Similar for the columns: When we pull down the formula, we want the column index to increase.But when copying down the formula, the first row should be fixed with a $-sign. That’s why we don’t add a $-sign in front of the column (“F”). We want this number to increase when we pull the formula to the right hand side. The second part of the OFFSET formula defines, how many rows your target cell is away from the base cell.This base cell should not change when we copy and paste the formula so that we insert the $-signs. In our case, the base cell is the left top corner cell of the range we want to transpose and link.The following number match to the picture above:
![transpose an array in excel transpose an array in excel](https://www.educba.com/academy/wp-content/uploads/2019/01/Rows-to-Columns-Example-3-2.png)
with ROW() or COLUMN()) but this is the fastest and easiest method. There are certainly more elegant ways (e.g. Roughly said, you define a base cell and tell Excel which value counting from the base cell to return.įirst: Prepare the number 0-2 (the maximum number of rows – 1) on the top and 0 to 1 on the left hand side. The OFFSET function is powerful but unfortunately especially for beginners not very easy to use. Workaround: Use the OFFSET formula for pasting links and tranpose them at the same time