Excel TRANSPOSE Function Examples

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.

Video: TRANSPOSE Function

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.

Uses for TRANSPOSE

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.

TRANSPOSE Syntax

The TRANSPOSE function has the following syntax:

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

TRANSPOSE Traps

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

Dynamic Array Formulas

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

1. For Excel versions with Dynamic Array formulas
2. 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.

Example 1: Change Orientation

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.

1) With Dynamic Array Formulas

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

1. Select the top left cell in the range where you want to display the data vertically -- cell B4 in this example.
2. Type this formula:
• =TRANSPOSE(B1:E2)
3. Press Enter, to complete the formula
4. 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.

2) Without Dynamic Array Formulas

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

1. 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.
2. Type this formula:
• =TRANSPOSE(B1:E2)
3. Press Ctrl+Shift+Enter to array enter the formula
4. 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.

.

Example 2: TRANSPOSE Alternative

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:

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

Example 3: Total Salary

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)))

How It Works

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!

Get the Sample Files

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.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

More Functions Tutorials

VLOOKUP Function

Dynamic Array Example

LOOKUP Function

30 Functions in 30 Days