Home > Formulas > > Transpose

Excel TRANSPOSE Function Examples

These Microsoft 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 to 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

• Tip: See the note below the screen shot, for a time-saving tip

Follow these steps to copy data, and paste it in a different orientation on the worksheet:

1. Select the original data on the worksheet, and copy it
2. Select the top left cell of the destination range - where you want the transposed data to start
3. On the Excel Ribbon's Home tab, click the Paste drop down arrow
4. In the gallery of Paste options, click the Transpose command
• Or, to use the keyboard, type the letter T
5. (optional) After the transposed data is in its new location, you can delete the original data, if it is no longer needed.

Add Paste Transpose to Quick Access Toolbar

If you frequently use the Paste Transpose command, you can add it to your Quick Access Toolbar (QAT) to save time. To do this, see the steps below the screen shot.

Here is a screen shot of the Paste and Transpose command on my QAT. I use that command several times a day, so it's a real time saver to have that command available, with a single click.

Add Paste Transpose to QAT

To add the Paste and Transpose command to your QAT, follow these steps:

• First, right-click anywhere on the Quick Access Toolbar
• In the pop-up menu, click the command, Customize Quick Access Toolbar

Excel Options Window

The Excel Options window opens, with Quick Access Toolbar selected, in the list at the left

In the main part of the window, there are options for you to Customize the Quick Access Toolbar

Follow these steps, to add the command:

• Under the heading, Choose Commands from, click the drop down arrow
• In the list of options, click on All Commands
• Below the drop-down, the list will change, to show all of the commands

• In the list of All commands, scroll almost to the bottom
• Click on the command, Transpose (Paste and Transpose)
• In the list on the right, select an existing QAT command
  • The command that you add will be placed below the selected command
• Click the Add button, to put the Transpose command on the QAT
• Click the OK button, to save the QAT changes, and to return to the Excel worksheet.

Example 3: Total Salary

The TRANSPOSE function can be combined with other functions, as in the complex formula.

This long formula was posted by Harlan Grove, in the Microsoft 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)))

The formula is entered in cell B5, and the formula with the transpose function returns the total salary for the best 5 consecutive years.

NOTE

• As you can see by the curly brackets in the formula bar, this formula is array entered, by pressing Ctrl+Shift+Enter, instead of just pressing Enter
• This is not required in Excel 365, which has spill functions

How It Works

Here are a few details on how this formula works

• Cell A5 is named Number, and I've entered 4 in that cell, for the number of years in this example.
• There are 10 years of salary figures, in row 8
• In the formula, the cell reference A8:J8 is used several times, to calculate that Excel data.
• The formula tests the ranges to see if there are enough consecutive COLUMNS.
• The results of those tests (1 or 0) are multiplied by the cell values, to get the total salaries.

Verify Formula Results

When creating a complicated new formula, I like to verify that the results are working correctly, by using a different method to solve the problem.

To check the Total Salary result in cell B5, there are formulas in rows 10 and 11. Details on those formulas are below the screen shot.

• Note: These verification formulas are not required, and can be deleted.

Count Columns

First, the formulas in row 11 check if there enough columns to the right of the current column, based on the Years number, in cell A5 (Number).

This formula is in cell A11, and copied to the right:

• =--(COLUMNS(A8:$J$8)>=Number)

Here's how that formula works:

• First, the COLUMNS function counts the number of columns:
  • starting from row 8 in the current column
  • ending at cell J8
• Next, the Greater Than or Equal To operator (>=) compares that result, to the value in cell A5 (Number)
• At the start of the formula, the two minus signs (double unary) convert the result from TRUE or FALSE, to a number -- 0 (FALSE) or 1 (TRUE)

Sum the Salaries

Next, the formulas in row 10 sum the salary amounts, starting from the current column.

This formula is in cell A10, and copied to the right:

• =IF(A11,SUM(A8:INDEX(A8:$J$8,1,Number)),0)

Here's how that formula works:

• First, the IF function checks if there is a value greater than zero in cell A11
• If TRUE, the INDEX function returns a reference to:
  • the cell in row 1 and column 4
  • in the range:
    • starting from row 8 in the current column
    • ending at cell J8
• Next, the SUM function returns the total for the values
  • starting from row 8 in the current column
  • ending at the cell returned by the INDEX function
• If the IF result is FALSE, because there is a zero in row 11
  • The IF formula result is zero

Highlight Largest Total

To make it easy to spot the largest total salary amount in row 10, a simple conditional formatting Top 10 rule was applied to the range A8:J8

Then, in the Top 10 Items dialog box, I used the following settings:

• For the number of Top items, I typed the number 1
• For the formatting, I selected Custom Format, then chose bright yellow fill colour

Get the Sample File

To see the formulas used in the TRANSPOSE examples, you can download the TRANSPOSE function sample workbook.

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

More Functions Tutorials

VLOOKUP Function

Dynamic Array Example

LOOKUP Function

30 Functions in 30 Days