Ever spent time to transpose rows and columns in a data set? It can be a very tedious job to switch rows to columns or columns to rows. It gets even harder if your data is updated frequently. Not anymore, for you can transpose rows and columns in a table using a single formula, and the best part is, this also works with dynamic ranges.
Syntax
=TRANSPOSE(range)
Steps
- Select the empty range you want to copy your transposed data
- Type in =TRANSPOSE(
- Type in or select the range that is to be copied (i.e. B2:E7)
- Add ) to close the function parameters
- Press Ctrl + Shift + Enter to finish the formula and define it as an array formula
How
The TRANSPOSE essentially switches the two ranges of your selection. Unlike the other alternative where you can select the range and go to Paste Special > Transpose, this function links ranges dynamically. This means that every update in the source range will affect the target range as well.
When using this formula, please note that selecting a larger range will return #N/A! errors, while smaller range causes data loss. To update the formula at a later time, you should select the entire range again. This is an array function and Excel doesn't allow changing a single cell in a range.
Last important note is that you need to press Ctrl + Shift + Enter instead of Enter. The Ctrl + Shift + Enter combination tells Excel that your formula is an array formula. A quick note here, do not include the curly parenthesis ({}) you see in the formula. Excel will place those automatically when you press Ctrl + Shift + Enter.
{=TRANSPOSE(B2:E7)}
