If you work extensively with Excel at home, at work, or in a home office you have the luxury of setting up your worksheets exactly as you want them. However, each person has their own quirks and methods for working with Excel. Some prefer organizing data into columns and others into rows.

If someone gives you an Excel file and you prefer the data to be displayed in a different arrangement, you can easily turn columns into rows and rows into columns using Excel’s built in Transpose function. Use the example below to learn how.

Turn Columns into Rows

Suppose someone gives you an Excel file with the data arranged into columns and you prefer the data to be in rows.

Excel Data in Columns

Begin by selecting the area into which you want to transpose the data from columns to rows. Notice in the example above that the data occupies A1 to B6. That’s a 2 by 6 (2×6) data table. To select the area for transposition, you need the opposite or a 6 by 2 (6×2) area. Starting in cell A12 (or wherever else you want the transposed data), mark out an 6×2 area.

Select Area to Transpose Data

Notice that the cells we chose include A12 to F13, a 6×2 area. With this area selected, click on the formula bar above. Make sure the area you selected is still selected before you start typing the formula. Now type the following formula into the formula bar

=transpose(a1:b6)

but don’t hit Enter yet. Entering this formula into Excel differs from most other formulas. You need to hold down the Ctrl and Shift keys when you hit Enter. So press Ctrl + Shift + Enter. Your Excel worksheet should now look like this:

Excel Columns Turned into Rows

The Ctrl + Shift + Enter key combination put a set of braces around the formula. This tells Excel that the formula’s output will be an array of data rather than just a single cell. Oddly, you cannot type the braces in yourself; you must use the Ctrl + Shift + Enter key combination.

More about Excel’s Transpose Function

The steps to turn columns into rows are quite simple as long as you remember to complete them in the following order:

  1. Select the area into which you want to turn the columns into rows
  2. Type in the Transpose formula with the selection still active
  3. Press Ctrl + Shift + Enter to tell Excel that the result of the formula will be an array rather than a single cell

Of course, you can transpose rows into columns using the same steps above. Just be sure to select the correct number of empty columns and rows before you type in the formula. If you have an equal number of rows and columns to transpose (e.g. 6×6, 8×8, etc.) you still need to select the empty rows into which Excel will place the transposed data.

You need not transpose all of the data you have in column and rows. You can choose to transpose only the data you want by selecting the correct number of empty cells and properly indicating the columns and rows to transpose when you type in the formula.

There is one caveat to using the transpose function in Excel. Excel will never transpose data and then automatically delete the original data. You must transpose the data first, delete the old data, and then cut and paste the newly-transposed data into the cells where the old data used to reside.