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:
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:
The TRANSPOSE function has a couple of traps, unless you are using newer versions of Excel, with dynamic array functions:
For the 1st example shown below, there are 2 sets of instructions
To check if your version of Excel has Dynamic Array formulas:
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:
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:
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:
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