Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Excel 2003 - change columns to rows and rows to columns

I'll set up an easy example first (actually the second option may be easier -
read through them both and pick the one you prefer).

Begin by inserting a new worksheet into the workbook. In cell A1 enter this
formula:
=OFFSET('Other Sheet'!$A$1,COLUMN()-1,ROW()-1)
where Other Sheet is the name of the sheet with your table on it.
Now fill that formula to the right across row 1 until it starts returning
nothing but zeros.
Select all of the cells in row 1 and then fill down the sheet, again until
you start getting all zeros.

The data has now been 'transposed'.

Next figure out where on that new sheet that table actually is and select
all of those cells. Use Edit | Copy, then turn right around without
unselecting anything and choose Edit | Paste Special with the [Values] option
selected. This converts all of those formulas into hard values. You can now
cut/copy and paste that table anywhere else you care to in the workbook.

====
Slightly more complex set up to just pull the table out of the old sheet :
again insert a new worksheet. Pick a cell somewhere to start placing your
transposed copy of the table. Now, lets say the real table goes from
H10:R450.

You want to place that table into the new sheet beginning at A1. This time
the formula in A1 becomes
=OFFSET('Other Sheet'!$H$10,COLUMN()-1,ROW()-1)
again fill right and then down, and again do the Edit Copy/Paste
Special-Values trick.

I think we'll leave it at that. From this point you can copy or move the
table just about anywhere you need to without much effort at all.



"Trish" wrote:

tHi, I have a number of spreadsheets that have come from different divisions.
One of them is different, ie their column headings are where the others
have their row headings, and vice versa. There is data in the table. Is
there an easy way to turn this around wihout affecting the integrity of the
data? I don't know the first thing about code, so would prefer a solution
that does not involve that. Many thanks!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change color of cells, columns and rows in Excel? Johnny434 New Users to Excel 5 July 12th 06 02:54 AM
How do change rows to colums AND columns to rows Colleen A Excel Discussion (Misc queries) 7 December 30th 05 01:40 AM
filter rows rather than columns in Excel 2003? Lynne Excel Discussion (Misc queries) 4 December 2nd 05 07:47 PM
How do you change the rows to columns and columns to rows. cc4digital Excel Discussion (Misc queries) 1 August 24th 05 02:21 AM
In Excel, how do I change the rows into columns? Novice SS User Excel Discussion (Misc queries) 1 February 8th 05 12:13 AM


All times are GMT +1. The time now is 09:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"