How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa.
A vertical range is a range with values in one column, a horizontal range has values in one row.
You can also transpose a range with more than one column and one row.
Array formula in cell D2:H2:
Range B2:B6 has 1 column and 5 rows and it is a vertical range. Use the TRANSPOSE function to convert it to a horizontal range. Shown in the example above.
Excel Function Syntax
TRANSPOSE(array)
Arguments
array | The values you want to transpose. |
=TRANSPOSE(B2:B6) is entered in cell range D2:H2, as an array formula.
How to enter an array formula
- Select cell range D2:H2.
- Click in formula bar
- Type: =TRANSPOSE(B2:B6)
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release CTRL + SHIFT
If you did this right the formula now begins and ends with a curly bracket. Like this: {=TRANSPOSE(B2:B6)} Don't enter these yourself.
Why do I have to enter this function as an array formula? It returns more than one value, if you want to see all values you need to enter it as an array formula.
What happens if you transpose a range with the same number of columns and rows?
The picture shows what happens if you transpose a cell range with the same number of rows and columns, in this case a cell range with 3 columns and 3 rows. The range size doesn't change but the numbers in the range change positions.
The values in the first column (B2:B4) is converted into the first row (F2:H2), the same with the second column (C2:C4) into the second row (F3:H3) and so on.
Converting a vertical cell range to constants
This animated picture shows how to convert the values in a cell range to constants.
What the animated picture doesn't show you is that I am pressing F9 after selecting the cell range B2:B6. These are all the steps:
- Select cell D2
- Type =
- Select cell range B2:B6
- Press F9
The formula bar returns {1;2;3;4;5}. The curly brackets tell you that it is an array. The constants in the array are separated by semicolons. The semicolon tells you that the next value is in the row below. In the example above all values are separated vertically, this is a one dimensional array.
Transpose an array with constants separated vertically
What happens if we transpose this array? {1;2;3;4;5} Select a cell and type in formula bar: =TRANSPOSE({1;2;3;4;5}) Press F9.
The formula bar shows {1,2,3,4,5}. The comma tells you that the values in the array are separated horizontally.
You can verify this by selecting D2:H2, type =TRANSPOSE({1;2;3;4;5}) in the formula bar and enter it as an array formula.
In other words, the colon tells you that the next value in the array is in the next column. The semicolon tells you that the next value is in the row below.
Two dimensional arrays
Look at this array {1,2,3;4,5,6;7,8,9}. You can now distinguish that this array has 3 columns and 3 rows by looking at the commas and semicolons. The array has values in more than one column and one row. This is a two dimensional array.
I made this array {1,2,3;4,5,6;7,8,9} from cell range B2:D4, it has 3 columns and 3 rows.
Transpose a cell range manually without using a formula
- Select a cell range you want to transpose
- Copy cells (CTRL + c)
- Right click on a destination cell
- Click "Paste Special..."
- Click "Transpose"
- Click OK button
Note! You may have other delimiting characters, it depends on your regional settings.
Articles with the 'TRANSPOSE' Function
The following 3 articles have formulas that contain the TRANSPOSE function.
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
Functions in 'Lookup and reference'
The TRANSPOSE function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
How to use the COLUMN function
The COLUMN function returns the column number of the top-left cell of a cell reference. If the argument is not […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
How to use the FILTER function
The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
The SORT function lets you sort values from a cell range or array. It returns an array with a size […]
How to use the SORTBY function
The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
How to use the UNIQUE function
The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form