Video: TRANSPOSE FunctionThe 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 TRANSPOSEThe 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. TRANSPOSE SyntaxThe TRANSPOSE function has the following syntax:
TRANSPOSE TrapsThe TRANSPOSE function has a couple of traps, unless you are using newer versions of Excel, with dynamic array functions:
|
Dynamic Array FormulasFor the 1st example shown below, there are 2 sets of instructions
To check if your version of Excel has Dynamic Array formulas:
|
Example 3: Total SalaryThe 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)) 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
How It WorksHere are a few details on how this formula works
|
Verify Formula ResultsWhen 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.
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:
Here's how that formula works:
|
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:
Here's how that formula works:
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:
Get the Sample FileTo 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 |
Last updated: October 1, 2022 1:25 PM