The TRANSPOSE Function in Excel – Flip Your Rows to Columns and Columns to Rows

Transposing Excel data means flipping your rows to columns and columns to rows.  In the example above, I want my transposed data to update if I change one of the numbers in my original data.   Transposing through pasting options, is not a good solution here, because my transposed data would not change if my original data changed.  Going through and setting each cell equal to another would be tedious and give lots of opportunities for human error.

The best solution in this case is the TRANSPOSE function, which is an array function in Excel.

Array functions work differently than regular functions.  They deal with blocks of cells, instead of individual cells.  You’ll sometimes hear them called CSE functions, because you end them by pressing Ctrl + Shift + Enter on your keyboard.  If you follow the instructions below, you should be able to do the TRANSPOSE function without any trouble.  To learn more about array functions, go to http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx.

The syntax of the TRANSPOSE function is simple:

=TRANSPOSE(array)

Use the TRANSPOSE Function

1. Count the number of rows and columns in the array you want to transpose.  In the example above, my original data (in columns A:F) has 5 rows and 6 columns.
2. Select the space you want to transpose into – you need to select the inverse of the data you have above, meaning that in this case, I’ll select 6 rows and 5 columns.  These cells should all be empty, or you’ll overwrite the data in them.
   
3. Begin the function.  For the array argument, select the array of data you want to transpose.  So, in my example, I’ll enter =TRANSPOSE(A2:F6).
   
4. On your keyboard, press Ctrl + Shift + Enter.  If you don’t use this key combination, you’ll get an error.  After you press Ctrl + Shift + Enter, Excel will add curly brackets ({}) around your function.
   

Important things to note about array functions:

• You cannot change part of an array function.  So you cannot select one cell in the array and try to overwrite the function or delete its contents.
• If you need to delete your array function, select the whole array, then press Delete on your keyboard.
• If you need to edit your array function, select the whole array and press F2 on your keyboard to edit.
• You can’t insert new cells into an array.