I've got a table such as this:

enter image description here And I have another worksheet with the data transposed, such as this:

Transposed Table

I want the transposed sheet to update whenever I update the original sheet.

I've tried to add another line beneath the transposed table, at line 4, and pulling all the appropriate names:

Cell A (Transposed) <- Cell B (Original) (the <- means pull the contents of Cell B into Cell A).

A4 <- Sheet1!D1

B4 <- Sheet1!D2

C4 <- Sheet1!D3

D4 <- Sheet1!D4

The problem is, when I try to autocomplete the next line using the little plus (+) mark at the bottom of my selection, lo and behold what happens:

enter image description here

It makes cell A5, which should pull from cell Sheet1!E1, pull from cell Sheet1!D2 instead!

How do I auto-complete this correctly, without writing every single cell myself?


If you want to get a formula simply transposing content of another sheet you can use this:

=OFFSET('Another Sheet'!A1,COLUMN()-1,ROW()-1)
(copy this in cell A1)

or this one:

=INDIRECT(ADDRESS(COLUMN(),ROW(),1,1,"Another sheet"))

After entering these formula you can just drag them to any direction and will work.

enter image description here

Of course, you can also use TRANSPOSE as suggested already, but there is more difficult to modify it.

  • This is actually better than TRANSPOSE(). Thank you!
    – Tom Granot
    May 10 '17 at 14:00

Step 1: Select blank cells

First select target blank cells. But make sure to select the target number of cells you want to add items transposed later. For example, there are 3 columns in your sheet1, but as the item number grows, the target cells number should increase. Because this is an array formula, the target cells should be pre-selected.

In your example, after adding original list, in second sheet select a range like Sheet2!A1:D10.

Step 2: Type =TRANSPOSE(

With those blank cells still selected, type: =TRANSPOSE(

Notice that the A1:D10 cells are still selected even though we have started typing a formula.

Step 3: Type the range of the original cells.

Now type the range of the cells you want to transpose. In your example, as I want to list 10 items, range should be A1:J10. So the formula for this example would be: =TRANSPOSE(Sheet2!A1:J10) -- but don't press ENTER, instead use CTRL+SHIFT+ENTER, otherwise it won't work.

You can access a definition of transpose formula in office support webpage, just search transpose formula.

Hope it helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.