These Excel TRANSPOSE function examples show how you can flip data around, changing vertical ranges to horizontal ones, or vice versa. There is a video, written steps, and a sample file for free download.

The Excel TRANSPOSE function changes the layout of data, from horizontal to verical, or from vertical to horizontal. It can be used on its own, or combined with other Excel functions.

Watch this short video to see 3 examples of ways that you can use the Excel TRANSPOSE function. There are written instructions below the video.

The TRANSPOSE function can change the orientation of data, or be used with other functions. For example, you can:

- change horizontal data to vertical, in a different location on the worksheet
- combine TRANSPOSE with other Excel functions, to show a person's total salary over their best 5 consecutive years

**NOTE**: You can also change the orientation of data without using the TRANSPOSE function. Instead, use a Paste Special command, as described below.

The TRANSPOSE function has the following syntax:

**TRANSPOSE(array)**- array is an array or a range of cells to be transposed

The TRANSPOSE function has a couple of traps, unless you are using newer versions of Excel, with dynamic array functions:

- TRANSPOSE must be entered as an array formula, by pressing
**Ctrl+Shift+Enter**. - The TRANSPOSE destination range must have the same number of rows and columns, respectively, as the array has columns and rows

For the 1st example shown below, there are 2 sets of instructions

- For Excel versions
**with**Dynamic Array formulas - For Excel versions
**without**Dynamic Array formulas

To check if your version of Excel has Dynamic Array formulas:

- Select a blank cell, then type:
**=SO** - If the screen tip shows SORT and SORTBY, you have the new functions.

If data is arranged horizontally in a worksheet, you can use the TRANSPOSE function to list the data vertically, in a different location.

For example, in a summary sheet, or dashboard, a vertical layout might be best. With the TRANSPOSE function, you could link to your original horizontal data, without changing its layout.

To transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range, follow these steps:

- Select the top left cell in the range where you want to display the data vertically -- cell B4 in this example.
- Type this formula:
**=TRANSPOSE(B1:E2)**- Press Enter, to complete the formula
- The formula will automatically spill into adjacent cells (B4:C7), to transpose the data. There is a light blue border arounde the range.

**NOTE**: To edit the formula, select the cell where it was originally entered - B4. Other cells will show the formula in the formula bar, but it can only be changed in the top left cell.

To transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range, follow these steps:

- Select the 8 cells where you want to display the data vertically -- cells B4:C7 in this example.
**IMPORTANT**: You must**select the entire destination range**when entering the TRANSPOSE formula.- Type this formula:
**=TRANSPOSE(B1:E2)**- Press
**Ctrl+Shift+Enter**to array enter the formula - Curly brackets are automatically added at the start and end of the formula, to show that it is array entered

**NOTE**: To edit the formula, select **all the cells** where it was originally entered - B4:C7.

.

If you want to change the orientation of worksheet data, without keeping a link to the original data, you can use Paste Special, instead of the TRANSPOSE function:

- Select the original data and copy it
- Select the top left cell of the destination range
- On the Ribbon's Home tab, click the Paste drop down arrow
- Click Transpose
- (optional) Delete the original data.

The TRANSPOSE function can be used with other functions, as in this eye-popping formula.

It was posted by Harlan Grove, in the Excel newsgroups, in a discussion on calculating the total salary for best 5 consecutive years.

**=MAX(MMULT(A8:J8,–(ABS(TRANSPOSE(COLUMN(A8:J8))
-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))
-(Number-1)/2)<Number/2)))**

As you can see by the curly brackets in the formula bar, this formula is array entered. Cell A5 is named *Number*, and I've entered 4, for the number of years this example.

The formula tests the ranges to see if there are enough consecutive COLUMNS. The results of those test (1 or 0) are multiplied by the cell values, to get the total salaries.

To check the results, in the rows below the salaries, the total salaries for each starting cell are shown, with the maximum value highlighted in yellow. This is a long way to accomplish what the previous formula did in one cell!

To see the formulas used in today's examples, you can get the **TRANSPOSE function sample workbook**.

The file is zipped, and is in xlsx file format -- it does not contain any macros.

Last updated: July 15, 2021 11:45 AM