Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
One comment on “Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical
  1. Shirly from Sri Lanka says:

    Thank you…

Leave a Reply

Your email address will not be published. Required fields are marked *

*