Simple Useful Excel Formulas and Tips can be found in this Page:
1. Transpose Option:
This Excel Option is used to Convert Row Data to Columns and Vice Verse.
Command: Copy – Paste Special – Transpose
- Row to Column: Assume we have excel sheet (Sheet1) that has a Row (Cells A1 to F1) filled with series of numbers and We have to switch this data to Column data (from Cells A2 to A7) as in below picture.
- Select Cells A1 to F1 and Press Ctrl+C to Copy
- Click on Cell A2. Right click and Choose Paste Special
- In Popup option of Paste Special, Select the check box “Transpose” and Click ok
Data is converted from Rows to Columns. The same method can be used to convert Column data to Rows.
Excel VBA Code for Paste Special with Transpose
To Implement the transpose option in any Automation, use the below code and modify according to your need.
Sub Transpose_Data() Sheets(1).Range("A1:F1").Copy Sheets(1).Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub
In this code, we first copy a Range of Row or Column or Table and apply Paste Special with Transpose Option. This simple VBA option can also be converted to a Excel formula with the help of User Defined Functions in Excel.
Learn here on how to make your own Excel Formulas and functions.