Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical
I previously posted this example, of transposing a range by copying it, and selecting the Transpose method in the Paste Special dialog box.
There are plenty of projects that require an immediate transposition using a formula to avoid the burden of manual Copy and Paste Special for Transpose every time a header cell changes.
I showed an example formula on this page that uses the INDIRECT function to transpose a list from vertical to horizontal.
Today’s example shows a different formula solution that transposes a list from horizontal to vertical, using the INDEX and ROWS functions.
In the above picture, the formula =INDEX($B$2:$F$2,ROWS($A$3:A3)) is in cell A3 and copied down to cell A7.
The yellow cells in range B2:F2 hold the original header labels which, if changed, will be automatically reflected in the vertical list in range A3:A7.
Thank you…