Is there a formula to transpose a range?

[excelos]

Hello,

Is there a way to specify a vertical range and fill in a row with that?

Thanks!

 

[Joe4]

There is a a Copy -> Paste Special -> Transpose option, if that is what you are asking.
If that is not, please describe your problem in more detail and maybe post an example of exactly what you are trying to do.

 

[excelos]

There is a a Copy -> Paste Special -> Transpose option, if that is what you are asking.
If that is not, please describe your problem in more detail and maybe post an example of exactly what you are trying to do.

Hello, yes, I know the special paste button, but I want to do that with formulas.

Let's say you have a horizontal rage (eg random numbers in A1:G1).

How can you write a formula in A2 and drag it across A3, A4 etc so that A2 copies A1, A3 copies B1 etc

 

[Joe4]

Here is one way.

Enter this formula in A2 and copy down:

=OFFSET($A$1,0,ROW()-2)

To see how the OFFSET function works, see this: https://exceljet.net/excel-functions/excel-offset-function

 

[RoryA]

Is there a way to specify a vertical range and fill in a row with that?

Yep, =TRANSPOSE()

 

[Eric W]

To expand a bit on Rory's idea, select A2:A8, enter the formula =TRANSPOSE(A1:G1) and press Control+Shift+Enter.

 

[Joe4]

Well, I guess that shows you how much I have to transpose data, I never used that TRANSPOSE function before!
Here is a good write-up on it: https://support.office.com/en-us/article/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027

 

[Rick Rothstein]

Here is one way.

Enter this formula in A2 and copy down:

=OFFSET($A$1,0,ROW()-2)

To see how the OFFSET function works, see this: https://exceljet.net/excel-functions/excel-offset-function

You could also use this formula which uses the non-Volatile INDEX function instead of the Volatile OFFSET function...

=INDEX(A$1:G$1,1,ROWS(A$2:A2))

Although if you want to allow the formula to be dragged down past where there is available data to fill the cells, then this version would be more appropriate...

=IFERROR(INDEX(A$1:G$1,1,ROWS(A$2:A2)),"")

 

[excelos]

You could also use this formula which uses the non-Volatile INDEX function instead of the Volatile OFFSET function...
=IFERROR(INDEX(A$1:G$1,1,ROWS(A$2:A2)),"")

Thanks, that's seems ideal.

Btw, what do you mean by volatile/non-volatile?

Also, I assume the above is to convert a row to a column?
What would be the opposite, it to convert a column to a row?

Thanks!

 

[Rick Rothstein]

TBtw, what do you mean by volatile/non-volatile?

See this link...

https://www.lifewire.com/description-of-volatile-functions-3124106