#1




Transposing Columns and Rows in
I'm working with Excel 2000 on a Win10 computer.
I have a table that is 25 columns wide and 12 rows high. The table is too wide to be practical. I would like to transpose the columns and rows so that the table is 25 columns high and 12 rows wide. I've tried the "Transpose" forumla but I don't understand it and it doesn't transpose the rows with the columns. Most of the values in the table are the result of forumlas. How do I go about transposing the table without messing with the formulas? (And how do I edit a thread's title? The title of this thread should read "Transposing Columns and Rows in Excel 2000".) Last edited by skeezix; 04262020 at 12:27 PM. Reason: Clarity 
#2




Transpose doesn't work with formulas. It moves the formula to new location, but all references to cells/ranges in remain unchanged, and as follows they will be completely wrong. You have to create all formulas from start.
You can select datarange of table (without header row), rightclick on leftmost top cell of new datarange OUTSIDE of old datarange (leave an empty row at top of new datarange for new headers), and PasteSpecial.Transpose copied data. After that clear all rows with formulas and enter new column headers. And after that you have to compose new formulas. But there will be a caveat  you get formulas in some cells of table columns, and different ones for every row, and values in other cells in same column. Not a best design IMHO. 
#3





#4




I found what I was looking for. Copy, then Edit > Paste Special with Transpose checked.
Thank you for your help! And how can I edit my Thread title???? 
#5




Would you please care to show (in a workbook) how you get that to work if you have formulas in original range?

#6




It appears (to me, anyway) that the transpose function only transposes values, not formulas.
>>Would you please care to show (in a workbook) how you get that to work if you have formulas in original range?<< I'm gettin' pretty old and forgetful (= "senile") and don't want to post anything I do that I'm not absolutely sure about. And I'm not absolutely sure about anything anymore... 
#7




You wrote
It appears (to me, anyway) that the transpose function only transposes values, not formulas. You are completely right. That's why I uploaded the file Transpose_CR in #3 above which also works when you have formulas and you mentioned yourself that most of the values in the table are the result of forumlas. 
#8




Thanks for reminding me  I had overlooked your link, but now I just downloaded it and will give it a try.

#9




@skeezix
I would recommend you to use the INDEX formula used in Sheet1!A17:M31 instead of that in Sheet2 (TRANSPOSE). If you absolutely should want to use the formula in Sheet2 be aware of the following. You must count the size of the original range  here 13*26 (13 rows and 26 columns). Then you must select an empty range which is 26*13 (26 rows and 13 columns) With such a range selected you must enter the array formula: =TRANSPOSE(A2:Z14) but instead of just pressing Enter you must hold down Ctrl and Shíft before pressing Enter. If you do it correctly Excel will automatically put braces {} around the formula. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Transposing a worksheet  H28Sailor  Excel  11  04232017 09:25 PM 
Rows to Columns with Every Other Row Formula  dlucius  Excel  1  12212015 07:10 PM 
Moving from Rows to Columns  jimmydd2121  Excel  1  06022014 09:05 AM 
Stack columns into rows  stellag09  Excel  1  04232014 03:26 AM 
Rows to Columns  kyakobi  Excel  5  12072010 03:12 PM 