The process of rotating data in Excel, such that rows become columns and columns become rows, is pretty straightforward. Copy, and then right-click on the destination and select the ‘transpose cells’ paste option.

transpose
Right-clicking to transpose data.

Things get a little more complicated if you want to transpose a series of cell references or formulae e.g. “=A14” or “=NORMSINV(A14)-NORMSINV(1-B14)”. If you don’t have all your cell references in absolute format, Excel will get the transposition all wrong. One way of getting round this is to find and replace (CTRL-H) all your = signs in the array you want to transpose, with a symbol that Excel finds meaningless, like #. You can then copy and paste-transpose your # cell references, and once you find and replace the #s with =s (in both your original and transposed arrays), you’ll have achieved the transposition you’re after.

In A, the transposed cell reference gets messed up (Excel transposes the direction of the reference during the transposition). In B, CTRL-H has been used to find and replace all =s with #s. The cell references look to have transposed correctly. The correct transposition is confirmed in C once CTRL-H has been used to re-replace all the #s with =s.
In A, the transposed cell reference gets messed up (Excel transposes the direction of the reference during the transposition).

In B, CTRL-H has been used to find and replace all =s with #s. The cell references look to have transposed correctly.

The correct transposition is confirmed in C once CTRL-H has been used to re-replace all the #s with =s.

 

 

Leave a reply

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> 

required


*