TRANSPOSE: switch the orientation without retyping
TRANSPOSE turns rows into columns and columns into rows. That is useful for presentation and imports, as long as you understand whether you need a live formula or a one-time paste.
Formula syntax
=TRANSPOSE(array)
What goes into it
array
Range or array whose rows and columns should be swapped.
A quick example first
=TRANSPOSE(A1:D3)
Returns the rows of A1:D3 as columns and vice versa.
=TRANSPOSE({1,2,3})
Returns a vertical array in dynamic-array Excel.
Turn a small two-column table into a horizontal summary
Enter this block in A1:B3. In a dynamic-array version of Excel, put the formula in D1 and leave D1:F2 clear.
| A | B |
|---|---|
| Item | Units |
| Notebook | 12 |
| Pen | 30 |
| Formula | Expected result / interpretation |
|---|---|
| =TRANSPOSE(A1:B3) | D1:F1 = Item, Notebook, Pen; D2:F2 = Units, 12, 30 |
Three source rows become three output columns, and two source columns become two output rows. The formula links to the source, so a change to B2 updates the corresponding output value. It does not move or remove the original data.
Before you copy this into a real sheet
For a one-time layout change, Paste Special → Transpose can be simpler; its values do not form the same live formula connection. Older Excel versions require a correctly sized array-formula entry, whereas dynamic-array versions spill from one cell. Do not type into the cells controlled by a spill. When working with dates or percentages, review output number formats because data orientation and display formatting are separate concerns. Clear space before transposing a larger block.
Useful habits
- In modern Excel, TRANSPOSE spills automatically.
- Leave enough blank cells for the output array.
- For a one-time static change, Paste Special → Transpose may be more appropriate than a live formula.
Where people get caught out
- Blocking the spill output with existing values.
- Using a live TRANSPOSE formula when you actually need independent editable values.
Other functions worth a look
A detail worth remembering
Check where the result will spill and whether other cells are in the way. A dynamic array changes size with its source.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.