Lookup & reference › TRANSPOSE85 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeTRANSPOSE

TRANSPOSE: turn a vertical table sideways

Rizal has to put the revenue of his five meatball (bakso) stall branches on a wide presentation slide. The data runs down the sheet, but sideways reads better on a slide. He wants a horizontal version that updates whenever the original numbers change.

Task

In D1, write a single TRANSPOSE formula that flips the table A1:B6 (headers included) so the result spills into D1:I2: row 1 holds the branch names and row 2 the revenue.

Cells to fillD1:I2

Required functionsTRANSPOSE

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
TRANSPOSE(range) turns rows into columns and columns into rows. A 6-row × 2-column table becomes 2 rows × 6 columns.
Hint 2
Write the formula in D1 only. In Excel 365 the result spills into the neighboring cells automatically, as long as they are empty.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

D1

=TRANSPOSE(A1:B6)

Explanation

=TRANSPOSE(A1:B6) returns a 2 × 6 array that spills from D1 to I2 and stays linked to the original data. That differs from Paste Special > Transpose, which copies the values only once. If any cell in the spill area is not empty, the result is #SPILL!. In older Excel versions (before 365), you had to select the output area first and enter the formula with Ctrl+Shift+Enter.

Starting worksheet: Sheet1
Row	A	B
1	Branch	Revenue
2	Kudus	18,500,000
3	Jepara	14,200,000
4	Pati	16,750,000
5	Demak	9,800,000
6	Rembang	11,300,000
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result