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, and the full video transcript. Video TranscriptIf 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 VerticalHere 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 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: August 30, 2023 3:44 PM