Often, mathematical data processing requires performing rather complex operations on integer arrays. In most cases, it doesn't matter for the program whether the values of the variables are written in columns or rows - the required parameters can be set in formulas. But if you need to perform some actions with the whole matrix, you should take into account that Excel will be able to correctly perform the task only if the variables are located in columns.
Instructions
Step 1
However, this situation most often occurs when performing multivariate statistical analysis using the Analysis Package add-in. Usually the matrix needs to be “rotated” just for the convenience of further work. In both cases, the matrix needs to be reflected or transposed. Then the rows will be “flipped” into columns.
Step 2
To transpose, simply select the required matrix with the cursor and copy it to the clipboard using the keyboard shortcut Ctrl + C, Ctrl + Insert, or by selecting the "Copy" item in the "Edit" menu. Place the cursor in cell A1 if you are going to place the transposed matrix on a new sheet. In the same way, you can insert the transposed matrix into the original sheet, removing the primary data. It is not very convenient to place the transformed matrix on the same sheet while keeping the old one and it is advisable only for arrays of small volume.
Step 3
Then choose Paste Special from the Edit menu. You will be presented with a "Paste Special" window, where you can specify various additional parameters. The very last block of functions will contain two checkboxes: "skip empty cells" and "transpose". Check the box on the latter. If the values of the variables are hammered in "by hand", just press OK to execute the operation. This will be enough to correctly reflect the original matrix.
Step 4
If the values in the array are the result of calculations using some formulas, for correct transposition, you will need to specify additional parameters in the "Paste Special" window from the "paste" group of commands. If you no longer need the original formulas, select Values or Values and Formats. The latter is selected when it is necessary to preserve the original format of the cells.
Step 5
When choosing one or another method of insertion with the preservation of formulas, you will have to take into account the version of your Office. Starting from the tenth version of Office, including MS Excel 2002, formulas can be transposed without taking into account the automatic "sliding" of variables: the program will independently take this factor into account and display the values correctly. If you are using an older application, when the formulas are transposed, the values may change beyond recognition, as a result of which you get a matrix that is completely different from the original one.
