Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Lookup & reference Under review

TRANSPOSE

TRANSPONEREN in Dutch Excel

Turns the rows of a range into columns and the columns into rows, and stays linked to the source.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: D1 holds the formula
Row	A	B	D	E	F	G
1	Week	Hours	11=TRANSPOSE(A2:B5)=TRANSPOSE(A2:B5)	22	33	44
2	11	3838	3838	4242	4040	4545
3	22	4242
4	33	4040
5	44	4545

Note: D1:G2 must be empty: the result spills there.

=TRANSPOSE(A2:B5)

=TRANSPOSE(A2:B5)

What TRANSPOSE does and when to use it

What it does: it swaps the rows and columns of a range, for example turning a list of weekly hours that runs down the sheet into a row across the top, and it stays linked to the source.

When to use it: when data is laid out the wrong way for the task: a list that must become column headers, or a timeline that must run down instead of across. For a one-off flip that never changes, Paste Special with Transpose is quicker, but it pastes a fixed copy.

Two main gains:

Stays linked: the result follows the source when it changes, while Paste Special pastes a fixed copy.
Works inside a formula: you can wrap SORT, UNIQUE or IF around it, which Paste Special cannot do.

How Excel evaluates it

Excel reads the source and notes its height and width. It builds a new block with those two swapped: the value at row r and column c of the source goes to row c and column r of the result.

Excel 2021 and Microsoft 365: you type the formula in the top-left cell of the target and press Enter; the result spills into the cells it needs, and all of them must be empty or you get #SPILL!.

Excel 2019 and older: you select the whole target block first (same size, rows and columns swapped), type the formula and confirm with Ctrl+Shift+Enter.

Watch out: A blank cell in the source comes back as 0, so a missing figure looks like a real zero, in every Excel version.

Mental model

Hold a sheet of paper and flip it over its diagonal: the top row becomes the left column, and the cell in the top-left corner stays where it is.

Terms used on this page: spill, range, cell

Syntax

TRANSPOSE(array)

TRANSPOSE(array)

Arguments
Argument	Type	Meaning
`array`	Required	The range or array whose rows and columns are swapped.

Return value:A block in which the rows and columns of array are swapped

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Turn a list of weekly hours into a timeline row

The time-writing export of an engagement lists the weeks down the sheet, with the hours booked in the next column. The planning sheet needs the weeks across the top, with the hours underneath. You want it to update when the export changes.

Sheet excerpt: D1 holds the formula
Row	A	B	D	E	F	G
1	Week	Hours	11=TRANSPOSE(A2:B5)=TRANSPOSE(A2:B5)	22	33	44
2	11	3838	3838	4242	4040	4545
3	22	4242
4	33	4040
5	44	4545
6

Formula cell (D1)Spilled resultNote: D1:G2 must be empty: the result spills there.

Formula in D1

=TRANSPOSE(A2:B5)

=TRANSPOSE(A2:B5)

Result: 1 | 2 | 3 | 4 / 38 | 42 | 40 | 451 | 2 | 3 | 4 / 38 | 42 | 40 | 45

Step by step

Excel reads A2:B5: 4 rows and 2 columns. The result must have 2 rows and 4 columns.
The first column of the source, A2:A5 (1, 2, 3, 4), becomes the first row of the result: D1:G1.
The second column, B2:B5 (38, 42, 40, 45), becomes the second row: D2:G2.
In Excel 2021 and Microsoft 365 you type the formula in D1 and press Enter, and the eight cells D1:G2 fill by themselves. In Excel 2019 and older you select D1:G2 first, type the formula and press Ctrl+Shift+Enter.
Change B3 from 42 to 44 and E2 changes to 44 as well, because the result is a formula and not a copy.

Watch out: If any of D1:G2 already holds a value, the cell shows #SPILL! and nothing is transposed. Clear the cells and the result appears.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The value at row r and column c of the source lands at row c and column r of the result. That swaps the shape from 4 by 2 to 2 by 4, and it keeps every hour under its own week. Only D1 holds the formula; the other seven cells show parts of its result.

More to watch out for

You cannot edit or delete a single cell of the result, such as E2. Change the source, or delete the formula in D1 to remove the whole block.

Variations

=TRANSPOSE(B2:B5)

=TRANSPOSE(B2:B5)

Only the hours, as one row. Use it when the week numbers are already typed above.

Intermediate

Flip a headcount table and keep blank cells blank

Group reporting collects the headcount of four entities per quarter, with the entities down the side. The dashboard needs the entities across the top. Two entities did not report one quarter, so those cells are empty, and they must stay empty: a headcount of 0 would suggest the entity had closed down.

Sheet excerpt: F1 holds the formula
Row	A	B	C	D	F	G	H	I	J
1	Entity	Q1	Q2	Q3	Entity=TRANSPOSE(IF(A1:D5 = "", "", A1:D5))=TRANSPOSE(IF(A1:D5 = ""; ""; A1:D5))	NL01	NL02	BE01	DE01
2	NL01	112112	118118	121121	Q1	112112	6464	3838	2727
3	NL02	6464		7070	Q2	118118		4141	2929
4	BE01	3838	4141	4343	Q3	121121	7070	4343
5	DE01	2727	2929
6

Formula cell (F1)Spilled resultNote: C3 and D5 are empty on purpose. F1:J4 must be empty: the result spills there.

Formula in F1

=TRANSPOSE(IF(A1:D5 = "", "", A1:D5))

=TRANSPOSE(IF(A1:D5 = ""; ""; A1:D5))

Result: Entity | NL01 | NL02 | BE01 | DE01 / Q1 | 112 | 64 | 38 | 27 / Q2 | 118 | | 41 | 29 / Q3 | 121 | 70 | 43 | Entity | NL01 | NL02 | BE01 | DE01 / Q1 | 112 | 64 | 38 | 27 / Q2 | 118 | | 41 | 29 / Q3 | 121 | 70 | 43 |

Step by step

Excel works out IF first, over the whole block A1:D5 including the headers. For every cell it asks: is it empty? C3 and D5 are, so they become an empty text ""; every other cell keeps its own value. The result is a block of the same size, 5 rows by 4 columns.
TRANSPOSE turns that block into 4 rows by 5 columns. The first row of the block (Entity, Q1, Q2, Q3) becomes the first column, F1:F4.
The second row (NL01, 112, 118, 121) becomes the second column, G1:G4; the third row (NL02, 64, empty, 70) becomes H1:H4, and so on.
The two empty texts end up where their sources point: C3 (row 3, column 3) goes to H3, and D5 (row 5, column 4) goes to J4. Both look empty.
Without the IF, TRANSPOSE would put a 0 in H3 and J4, and the dashboard would report that NL02 had no staff in Q2.

Watch out: An empty text is not a blank cell. ISBLANK says FALSE for it, and arithmetic such as H3 + 1 gives #VALUE!. SUM and AVERAGE skip it, which is usually what you want.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

TRANSPOSE copies whatever it receives, and a truly blank cell is received as 0. IF replaces the blank by an empty text before TRANSPOSE sees it, and an empty text is passed on as an empty text. The IF works on the whole block at once, so the fix holds for any size of table.

More to watch out for

In Excel 2019 and older the IF inside TRANSPOSE needs the selected block and Ctrl+Shift+Enter as well. Without them you get a single value.

Variations

=TRANSPOSE(A1:D5)

=TRANSPOSE(A1:D5)

The plain flip. Blank cells show as 0, which is fine when 0 is the truth.

AdvancedExcel 2021 or later (This example needs Excel 2021 or later)

Build a sorted period header from the entries themselves

From the journal entry extract you want a summary with one column per posting period. The periods in the extract are repeated and unsorted. Rather than typing the headers, you want one row with each period once, in order, that grows by itself when a new period shows up in the data.

Sheet excerpt: E1 holds the formula
Row	A	B	C	E	F	G	H
1	Entry no.	Period	Amount	99=TRANSPOSE(SORT(UNIQUE(B2:B9)))=TRANSPOSE(SORT(UNIQUE(B2:B9)))	1010	1111	1212
2	JE-1041	1111	€2,450.00€ 2.450,00
3	JE-1042	99	€18,200.00€ 18.200,00
4	JE-1043	1010	€760.50€ 760,50
5	JE-1044	99	€3,300.00€ 3.300,00
6	JE-1045	1212	€41,250.00€ 41.250,00
7	JE-1046	1111	€1,980.00€ 1.980,00
8	JE-1047	1010	€5,100.00€ 5.100,00
9	JE-1048	99	€12,750.00€ 12.750,00
10

Formula cell (E1)Spilled resultNote: E1:H1 must be empty: the result spills there.

Formula in E1

=TRANSPOSE(SORT(UNIQUE(B2:B9)))

=TRANSPOSE(SORT(UNIQUE(B2:B9)))

Result: 9 | 10 | 11 | 129 | 10 | 11 | 12

Step by step

Excel works from the inside out. UNIQUE reads B2:B9 (11, 9, 10, 9, 12, 11, 10, 9) and keeps each value once, in order of first appearance: 11, 9, 10, 12. That is a column of 4 rows.
SORT puts that column in ascending order: 9, 10, 11, 12. It is still a column of 4 rows.
TRANSPOSE turns the column of 4 rows into a row of 4 columns: 9, 10, 11, 12 in E1:H1.
Because every step is a formula, the row follows the data. Add an entry for period 13 in row 10 of the extract and the row grows to I1, as long as I1 is empty. If it is not, the cell shows #SPILL!.

Watch out: If the periods are stored as text, such as P9, P10, P11 and P12, SORT puts them in text order: P10, P11, P12, P9. No error appears, and the header row is wrong. Store periods as numbers, or write them with a leading zero (P09).

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

UNIQUE and SORT both return a column, because the source is a column. TRANSPOSE is the last step because it only changes the shape, never the order or the content. Put it first and UNIQUE would receive a single row, compare whole rows by default, and return that row unchanged, duplicates included.

More to watch out for

A blank cell in B2:B9 comes back from UNIQUE as 0, so the header row gets a period 0. Filter the blanks out before UNIQUE if the extract can contain them.

Variations

=TOROW(SORT(UNIQUE(B2:B9)))

=TOROW(SORT(UNIQUE(B2:B9)))

TOROW gives the same row and is shorter, but it needs Excel 2024 or Microsoft 365. TRANSPOSE works in Excel 2021 as well.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Reconcile intercompany balances against their mirror image

Three group entities each report what they have receivable (positive) or payable (negative) from every other entity. When both sides book the same transaction, the balance NL01 reports for BE01 is exactly the opposite of the one BE01 reports for NL01. You want one cell with the total unreconciled amount, so that the elimination can go ahead once it reads 0.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F
1	Entity	NL01	BE01	DE01	Unreconciled
2	NL01	€0.00€ 0,00	€12,500.00€ 12.500,00	-€4,300.00€ -4.300,00	€150.00€ 150,00=LET( balances, B2:D4, mirror, TRANSPOSE(balances), gap, balances + mirror, SUM(ABS(gap)) / 2 )=LET( balances; B2:D4; mirror; TRANSPOSE(balances); gap; balances + mirror; SUM(ABS(gap)) / 2 )
3	BE01	-€12,500.00€ -12.500,00	€0.00€ 0,00	€8,000.00€ 8.000,00
4	DE01	€4,300.00€ 4.300,00	-€7,850.00€ -7.850,00	€0.00€ 0,00
5

Formula cell (F2)InputNote: Rows are the reporting entity, columns the counterparty. Positive is a receivable, negative a payable.

Formula in F2

=LET(
  balances, B2:D4,
  mirror, TRANSPOSE(balances),
  gap, balances + mirror,
  SUM(ABS(gap)) / 2
)

=LET(
  balances; B2:D4;
  mirror; TRANSPOSE(balances);
  gap; balances + mirror;
  SUM(ABS(gap)) / 2
)

Result: €150.00€ 150,00

Step by step

LET names the block B2:D4 balances: three rows (NL01, BE01, DE01) of three figures each.
mirror is TRANSPOSE(balances). It swaps rows and columns, so the figure that BE01 reports for NL01 (-12,50012.500) now sits where NL01's figure for BE01 (12,50012.500) sits.
gap adds the two blocks cell by cell:
- row 1: 0 + 0, 12,50012.500 + (-12,50012.500), -4,3004.300 + 4,3004.300 gives 0, 0, 0
- row 2: -12,50012.500 + 12,50012.500, 0 + 0, 8,0008.000 + (-7,8507.850) gives 0, 0, 150
- row 3: 4,3004.300 + (-4,3004.300), -7,8507.850 + 8,0008.000, 0 + 0 gives 0, 150, 0
ABS makes every difference positive and SUM adds them: 150 + 150 = 300.
Every mismatch shows up twice, once on each side of the diagonal, so the formula divides by 2: 300 / 2 = 150. The difference sits between BE01 and DE01.
Change DE01's figure for BE01 from -7,8507.850 to -8,0008.000 and the result becomes 0.

Watch out: The sign mistake: subtracting instead of adding. With receivables positive and payables negative, balances - mirror gives 25,00025.000 for the pair that agrees, and looks like everything is wrong. Subtract only when both sides report positive amounts.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

The mirror image of a matrix puts each pair of reports on top of each other, so adding them tests the pair in one step. This only works for a square block with the same entities in the same order in the rows and in the columns. For a long list of intercompany lines rather than a matrix, check each pair with SUMIFS instead: it also tells you which pair differs, and it does not need the symmetry.

More to watch out for

If the entities are in a different order in the rows than in the columns, the block is still square, so no error appears, but the formula compares the wrong pairs. Check that the headers read the same as the first column.
A block that is not square, such as 3 rows by 4 columns, cannot be added to its transpose (4 by 3): the positions that do not overlap give #N/A, and the formula returns #N/A.

Variations

=B2:D4 + TRANSPOSE(B2:D4)

=B2:D4 + TRANSPOSE(B2:D4)

The difference block itself, 3 by 3, spilled from the cell. Use it to see which pair differs before you add up.

=SUM(ABS(B2:D4 + TRANSPOSE(B2:D4))) / 2

=SUM(ABS(B2:D4 + TRANSPOSE(B2:D4))) / 2

Without LET. In Excel 2019 and older, select the single result cell and confirm with Ctrl+Shift+Enter, because the calculation works on whole blocks.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#SPILL! instead of the flipped block	The cell shows #SPILL! because something sits in the cells where the result must go, or the result would overlap its own source.	Clear the target cells, or move the formula to an empty area with room for the swapped block.
One value only, or #N/A in extra cells	In Excel 2019 or older the formula is entered in one cell with Enter, and only one value appears. Or the selected block is too large, and the extra cells show #N/A.	Select a block of exactly the swapped size first, type the formula and confirm with Ctrl+Shift+Enter.
Zeros where the source has gaps	The transposed block shows 0 where the source has gaps.	Test for blanks inside the function: TRANSPOSE(IF(range = "", "", range)).
The pasted copy no longer matches the source	Paste Special with Transpose is used on data that will change. The pasted copy is fixed and no longer matches the source.	Use TRANSPOSE as a formula when the result has to follow the source.