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.

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.

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:

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.