Suppose that we have a table with the integers 1 through 5 listed in the first column, and the square of those integers in the second column, calculated through formula: for example, cell B1 contains =A1^2, and so forth. In the end, the table looks like this:
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |
| 4 | 16 |
| 5 | 25 |
Now say that we want to transpose this table, so that instead of a 5x2 table, it'll be 2x5 table with the integers 1 through 5 in the top row, and the square of those numbers in the bottom row. We can actually do this easily by copying and instead of simply pasting, right-click the house and select Paste Special. When the option box comes up, check the Transpose box and hit OK. The table should look like this as envisioned:
| 1 | 2 | 3 | 4 | 5 |
| 1 | 4 | 9 | 16 | 25 |
However, these two tables are not linked by any formulas. Suppose that in the original table, we want to replace the value 5 with 10. Next to it, 100 will automatically appear since the calculation is based on the formula. However, the transposed table will not automatically change. This may be problematic if the data need to be linked.
Fortunately, there is a way to do transposing but through copy / paste special, but rather through formulas, just like =SUM() or A1^2. However, do realize that the output is going to be a 2x5 table, not a single cell. Here is where array formulas can perform this task. To begin, select and activate a range of 2 rows by 5 columns. While those cells are highlighted, begin typing =TRANSPOSE(A1:B5). As before, the range may be selected by the mouse. Now, if we hit enter right at this stage, we would get an error. The key to using array formulas is to press Control + Shift + Enter to enter the formula. Upon hitting those three keys, the transposed table will appear. Not only will it appear, it will be linked to the original table. Any change in the original table will automatically be reflected on the transposed table.
If we click on any cells that comprise the transposed table, we see that their formula all say {=TRANSPOSE(A1:B5)}. The curly brackets are indicative of the cell being part of an array. Another characteristic of an array is that its cells can't be modified. Try editing the content of any of the cells and an error message appears. The only way to exit this message is to press Esc.
In future posts, we will see how array formulas can perform multiple calculations on one or more items.
