How to transpose rows and columns in Excel 2013

Download Excel File

TRANSPOSE literally means changing the orientation of a cell range or an array.  Thus, TRANSPOSE enables to swap data from rows into columns and vice-versa.

We can transpose the data in Excel 2013 in two ways:
1. Static Transpose – using "Paste Special"
2. Dynamic Transpose – using TRANSPOSE function

Static Transpose using "Paste Special" function

This method is very useful when we have to quickly transpose data without changing anything in the future. The output data is STATIC i.e. it does not have any linking or formula attached to it.

Following steps will be helpful for static transpose using paste special option:

1. Select the cell range that you want to transpose and copy the same (Ctrl+C)

2. Select the cell where you want the output to start

3. Go to "Home" tab >>> "Paste" button >>> Paste Special >>>Transpose

OR  Right click >>> Paste Special >>> Transpose
Shortcut: Ctrl+Alt+V >>E  OR Alt+ESE

4. Press OK and its done. The transposed data will get pasted starting from the selected cell.

Dynamic Transpose using TRANSPOSE function

TRANSPOSE function gives the output with linking. Hence, if the original data altered, the transposed data will reflect the change automatically.

TRANSPOSE function is an "Array" function i.e. it takes cell range as input (quite logical..!!) and needs combination of "Ctrl + Shift + Enter" keys to insert. Array functions are enclosed by curly braces {}.

One critical difference between Paste Special and TRANSPOSE function:

For using TRANSPOSE function the output range should be selected first and it should be an appropriate array/cell range i.e. if the input range is 2x3 cell range, the output range selected should be 3x2 cell range.

1. Select the output cell range where the transposed data is required. You can watch the selection matrix in the "Name Box".

2. Keep the cell range selected and type below formula:

       =TRANSPOSE (input cell range)

       "input cell range" is the input data range which we want to transpose

3. Press "Ctrl + Shift + Enter" after entering the formula. This will automatically enclose formula with curly braces {}.

Important Note: Once the TRANSPOSE function is applied, you cannot edit or delete any of the individual cells in the output range.