How do I transpose rows (with formulas) to columns??

[Ari]

I have 6 rows of data going across 40 columns with the rows being th
header. The data is a result of formulas based on data in anothe
sheet. I want to show this as 6 columns and 40 rows with the column
being the header instead of the rows.

The only transpose feature I know is copy and paste special but I nee
to cut and past special. when I copy and past special the formula
change and don't work.
I can do this manually but it will take a long time.

Any help

 

[Frank Kabel]

Hi
one way: if your data is on sheet1 (starting in cell A1) try the
following on a separate sheet:
- select a range of 6 columns and 40 rows (e.g. A1:F40)
- click into the formula bar and enter the array formula
=TRANSPOSE('sheet1'!A1:AN6)
- finish this formula with CTRL+SHIFT+ENTER

 

[Gord Dibben]

Ari

Alternate method to Frank's.

Select cells. Edit>Replace What: = With: %%%

Transpose the cells then reverse the Edit>Replace.

Gord Dibben Excel MVP