Yesterday, in the 30XL30D challenge, we counted columns with the COLUMNS function, and now it’s time for something a bit more demanding.
For day 13 in the challenge, we’ll examine the TRANSPOSE function. With this function, you can flip things around, changing vertical ranges to horizontal ones, or vice versa. Is that something you need to do? Could you use Paste Special instead? Could other functions do the same thing?
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the TRANSPOSE information and examples, and if you have other tips or examples, please share them in the comments.
Function 13: TRANSPOSE
The TRANSPOSE function returns a horizontal range of cells as a vertical range, or vice versa.
How Could You Use TRANSPOSE?
The TRANSPOSE function can change the orientation of data, or be used with other functions:
- change horizontal data to vertical
- show total salary over best consecutive years
To change data orientation, without links,
- use Paste Special > Transpose.
The TRANSPOSE function has the following syntax:
- array is an array or a range of cells to be transposed
- 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.
Example 1: Change Horizontal Data to Vertical
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:
- Select the 8 cells where you want to display the data vertically — cells B4:C7 in this example.
- Type this formula, then enter it as an array formula, by pressing Ctrl+Shift+Enter.
Curly brackets will be automatically added at the start and end of the formula, to show that it is array entered.
Instead of using TRANSPOSE, you could use another formula to display the data, such as this INDEX formula. It does not require array entry, and you don’t have to select all the destination cells when creating the formula.
Example 2: Change Orientation Without Links
If you just want to change the orientation of your data, without keeping a link to the original data, you can use Paste Special:
- Select the original data and copy it
- Select the top left cell of the destination range
- On the Ribbon’s Home tab, click the Paste drop down arrow
- Click Transpose
- (optional) Delete the original data.
Example 3: Total Salary For Best Consecutive Years
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.
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!
Download the TRANSPOSE Function File
To see the formulas used in today’s examples, you can download the TRANSPOSE function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the TRANSPOSE Video
To see a demonstration of the examples in the TRANSPOSE function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Change Horizontal Data to Vertical – Excel TRANSPOSE Function