TRANSPOSE function

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

Often, you must switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. However, in doing so, you create duplicated data. If you only want unique data then you can enter a formula instead using the TRANSPOSE function. For example, in the following picture, the formula =TRANSPOSE(A1:B4) takes the cells A1 to B4 and arranges them horizontally.

Original cells above, cells with TRANSPOSE function below

Note: If you have a current version of Microsoft 365 , then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, input the formula in the top-left-cell of the output range, then press Ctrl+Shift+Enter to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.

Step 1: Select blank cells

Firstly, choose some blank cells. But ensure to pick the equivalent number of cells as the initial range of cells, but in the opposite direction. For example, there are 8 cells here that are arranged vertically:

Cells in A1:B4 in the setup of using a TRANSPOSE Function

So, we have to chooset eight horizontal cells, like this:

Cells A6:D7 selected

This is where the new, transposed cells will eventually turn up.

Step 2: Type =TRANSPOSE(

With those blank cells still chosen, input: =TRANSPOSE(

Excel will closely resemble this:

=TRANSPOSE( of TRANSPOSE Function

Pay attention to the eight cells are still picked even though we have started writing a formula.

Step 3: Type the range of the original cells.

Now type the range of the cells you want to transpose. In this example, we want to transpose cells from A1 to B4. So the formula for this example would be: =TRANSPOSE(A1:B4) — but don’t press ENTER yet! Simply stop typing, and move to the next step.

Excel will look similar to this:

=TRANSPOSE(A1:B4)

Step 4: Finally, press CTRL+SHIFT+ENTER

Now press CTRL+SHIFT+ENTER. Why? Because the TRANSPOSE function is solely integral to array formulas, and that’s how you end an array formula. An array formula, in short, is a formula that manifests itself in multiple cells. Because you chose numerous cells in step 1 (you did, didn’t you?), the formula will get transferred to several cells. Here’s the result after pressing CTRL+SHIFT+ENTER:

Result of formula with cells A1:B4 transposed into cells A6:D7

Tips for using the TRANSPOSE Function

  • There is no need for any manual input. After typing =TRANSPOSE( you can use your mouse to select the range. Simply select and drag from the beginning of the range to the end. But remember: press CTRL+SHIFT+ENTER once you at the endpoint, rather than just ENTER alone.
  • Need to transpose text and cell formatting too? Try copying, pasting, and using the Transpose option. But remember that this forms duplicates. So if your original cells change, the copies will remain unchanged.
  • There’s more to learn about array formulas. Create an array formula or, you can read about detailed guidelines and examples of them here.

Technical details of the TRANSPOSE Function

The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function has to be set as an array formula in a range with the same number of rows and columns, respectively. This is because the source range contains columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.

TRANSPOSE Function Syntax

TRANSPOSE(array)

The TRANSPOSE function syntax has the following argument:

  • array –   Required. An array or range of cells on a worksheet that you want to transpose. The transpose of an array is generated by using the first row of the array as the first column of the new array. Then, the second row of the array as the second column of the new array, and so on. If you’re unsure of how to enter an array formula, see Create an array formula.

See Also

Transpose (rotate) data from rows to columns or vice versa

Create an array formula

Rotate or align cell data

Guidelines and examples of array formulas

Leave a Reply

%d bloggers like this: