Contextures

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.

transpose function change orientation of data

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, and the full video transcript.

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Video Transcript: Change Horizontal Data to Vertical

Here is the full transcript for the Change Horizontal Data to Vertical video.

With the TRANSPOSE function in Excel, you can take data that's arranged horizontally and display it vertically or vice versa, and you keep the links to the original data.

So if something changes here, it would also change in the display data with the TRANSPOSE function.

To look at an example of how we can use it, we have four years of data and the units sold in each year.

To transpose that, I would like to have the years down a column and the unit sales beside each year.

I've got 4 columns by 2 rows. To transpose that, I'll select the opposite. I want 4 rows and 2 columns.

With those cells selected, I type =TRANSPOSE, open bracket, and then the cells from the original data.

Close the bracket and the TRANSPOSE function is array entered. So on the keyboard, press the Ctrl and shift keys, and then tap the Enter key.

And if you look in the Formula Bar, you can see curly brackets have been added at the beginning and end of that formula to show that it's array entered. So there's the data that's gone from horizontal to vertical.

You can do that with other functions though. The TRANSPOSE function is a bit limiting because you have to know, ahead of time, exactly the size of the original data and the size, select that size in the location where you're pasting it.

If you use a function like INDEX instead. So here I've used INDEX, and I could just make that more flexible.

I'm not sure how many years of data I'm going to have. Each formula's individually entered in a cell. It's not array entered, so it gives me a bit more flexibility.

So that's an alternative to using TRANSPOSE is to, to use another function like INDEX.

If you don't need to keep the links, you can also use Paste Special to change data from horizontal to vertical.

If I selected these three cells, and on the Home tab, I'll copy them. Then I can select a cell where I'd like to start pasting those.

I don't have to pick the range that's the exact size. I just select the starting cell and then from the Paste drop down, click Transpose and there's the transposed data, and the original is still there.

They're not linked at all, but you have the data in a different layout.

You can use TRANSPOSE in other formulas. This is a very long, complicated formula, but you can see TRANSPOSE is used in here.

You can see curly brackets at the end and the beginning.

So this is another array-entered formula, and it's looking at all these years of salaries and finding the best four consecutive years.

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

transpose function

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.

transpose function change orientation of data

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.

transpose function change orientation of data

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.

.transpose function change orientation of data

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.

transpose function alternative paste special

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.

transpose paste special on quick access toolbar

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

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

customize quick access toolbar

  • 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.

customize quick access toolbar

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

transpose function calculating total salary for best 5 consecutive years

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.

verification formulas for total salary

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

top 10 items conditional formatting command

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

top 1 item conditional formatting settings

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

 

About Debra

 

Last updated: August 30, 2023 3:44 PM