How to Convert Rows Into Columns in Microsoft Excelby Aaron Parson
Excel 2010 and 2013 offer two methods for converting rows of data into columns and columns into rows. The simplest method copies your data and pastes it in a new orientation. To retain your data in both orientations and have the converted copy update automatically, use the TRANSPOSE function instead. TRANSPOSE also works with data in a table, whereas the copy and paste method does not.
Copy and Paste Method
Copy the entire range of data you want to flip by selecting it and pressing "Ctrl-C." Cutting the data with "Ctrl-X" won't work for this task.
Right-click the empty cell you want to use as the new top-left cell of data. You can't use a cell that's part of the current selection -- even if you want to overwrite the old range, start by right-clicking a cell in a blank part of the spreadsheet. Mouse over "Paste Special" and pick the "Transpose" icon, which shows a rotating block of cells. As a shortcut, after copying a range, right-click a cell and press "S" and then "T."
Delete the original range of data and drag the transposed selection by its border to reposition it, if desired.
TRANSPOSE Function Method
Select the entire range of blank cells that you want to fill with transposed data. Assuming you want to transpose all of your data, select as many rows as your original data has columns and vice versa.
Press "F2" -- or click in the Formula Bar -- type the formula "=TRANSPOSE(" and then select your original range of data to fill in its location.
Close the TRANSPOSE function's parenthesis and finish by pressing "Ctrl-Shift-Enter" to set the formula as an array. With "Enter" alone, the function won't work. If you press "Enter" by accident, reselect the blank cell range, press "F2" to reopen the formula and push "Ctrl-Shift-Enter" to finish it correctly.
- check Pressing "Ctrl-Shift-Enter" marks a formula as an array, which is necessary for functions that use data across multiple cells. You can identify array formulas by the curly braces surrounding them, but you can't create an array by adding these braces manually -- only by pressing "Ctrl-Shift-Enter."
- check The screenshots in this article demonstrate transposing a vertical range of data into a horizontal range, but the process works identically when moving in the opposite direction.
- close Cells containing the TRANSPOSE function update in real time as you change the original data. If you delete the original data, all transposed cells turn to "0." This does not occur when using the copy and paste method.
- close With the copy and paste method, cell references might point to incorrect locations after pasting. To prevent this problem, switch every relative reference in the original data to an absolute reference before copying: select a cell reference in a formula, such as "A1," and press the "F4" key until the reference has dollar signs preceding both the row and the column. For example, instead of "A1," the reference should read "$A$1." If your references exclusively point to other cells within the transposed data, however, you won't run into trouble even if you use relative references.
- photo_camera Image courtesy of Microsoft
Click here to provide feedback on this article