Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-26-2020, 12:21 PM
skeezix skeezix is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 97-2003
Advanced Beginner
Transposing Columns and Rows in
 
Join Date: Jan 2019
Posts: 61
skeezix is on a distinguished road
Default 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; 04-26-2020 at 12:27 PM. Reason: Clarity
Reply With Quote
  #2  
Old 04-26-2020, 10:46 PM
ArviLaanemets ArviLaanemets is offline Transposing Columns and Rows in Windows 8 Transposing Columns and Rows in Office 2016
Expert
 
Join Date: May 2017
Posts: 565
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

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), right-click 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.
Reply With Quote
  #3  
Old 04-27-2020, 06:03 AM
xor xor is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,041
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Maybe like shown in the attached (two different formulas)

Transpose_CR.xlsx
Reply With Quote
  #4  
Old 04-27-2020, 08:44 AM
skeezix skeezix is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 97-2003
Advanced Beginner
Transposing Columns and Rows in
 
Join Date: Jan 2019
Posts: 61
skeezix is on a distinguished road
Default

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????
Reply With Quote
  #5  
Old 04-27-2020, 07:54 PM
xor xor is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,041
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Would you please care to show (in a workbook) how you get that to work if you have formulas in original range?
Reply With Quote
  #6  
Old 04-28-2020, 07:25 AM
skeezix skeezix is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 97-2003
Advanced Beginner
Transposing Columns and Rows in
 
Join Date: Jan 2019
Posts: 61
skeezix is on a distinguished road
Default

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...
Reply With Quote
  #7  
Old 04-28-2020, 08:19 AM
xor xor is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,041
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #8  
Old 04-30-2020, 11:23 AM
skeezix skeezix is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 97-2003
Advanced Beginner
Transposing Columns and Rows in
 
Join Date: Jan 2019
Posts: 61
skeezix is on a distinguished road
Default

Thanks for reminding me - I had overlooked your link, but now I just downloaded it and will give it a try.
Reply With Quote
  #9  
Old 04-30-2020, 08:23 PM
xor xor is offline Transposing Columns and Rows in Windows 10 Transposing Columns and Rows in Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,041
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

@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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transposing Columns and Rows in Transposing a worksheet H28Sailor Excel 11 04-23-2017 09:25 PM
Transposing Columns and Rows in Rows to Columns with Every Other Row Formula dlucius Excel 1 12-21-2015 07:10 PM
Moving from Rows to Columns jimmydd2121 Excel 1 06-02-2014 09:05 AM
Transposing Columns and Rows in Stack columns into rows stellag09 Excel 1 04-23-2014 03:26 AM
Transposing Columns and Rows in Rows to Columns kyakobi Excel 5 12-07-2010 03:12 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft