flip columns to rows

  • Thread starter Thread starter captain fantastic
  • Start date Start date
C

captain fantastic

Is there any way to 'flip' a worksheet with one command, so that columns become
rows (or rows become columns)?
 
captain said:
Is there any way to 'flip' a worksheet with one command, so that columns become
rows (or rows become columns)?
Click to expand...

Not one command, but you could write a macro.

Select all
Copy
Paste Special tick "Transpose"

Chris
 
captain fantastic said:
Is there any way to 'flip' a worksheet with one command, so that columns
become
rows (or rows become columns)?
Click to expand...

Copy
Paste Special ..... transpose

AndyW
 
captain fantastic said:
Is there any way to 'flip' a worksheet with one command, so that columns become
rows (or rows become columns)?
Click to expand...

Another way, via a formula
Assume source data in Sheet1
In Sheet2,
Put in A1: =OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,ROWS($1:1)-1)
Copy A1 across & fill down as far as required to return a dynamic transpose
of Sheet1

---
 
Much easier formula (I'm using in that case)
on the sheet2
in A1: = OFFSET(Sheet1!$!$1,COLUMN()-1;ROW()-1)

This is one common "fault" - people forget the most simple 2 functions -
ROW() and COLUMN()
With offset they may do the miracles :)

With bests
 
Albeit longer, the earlier version was meant to be a generic which could be
placed in any starting cell in Sheet2 and then propagated across/down, in
case this is preferred.

---
 
Back
Top