Flipping data around

[Ed]

Hi

I have a table of data which i need to flip around so all
the column headings will be row headings and vica versa,
is there an easy way of doing this on excel.

I have tried playing around with the offset function but
to no avail.

Please help!!

Thanks

 

[Andy B]

Ed

Use Copy / Paste Special / Transpose.
If I was doing it, I would do the paste on a new sheet - or at least well
away from the existing data!

Andy.

 

[Max]

Another way is to use TRANSPOSE

Assume the source range is say in Sheet1, in A1:A26 (a 26R x 1C grid)

You can transpose this to another sheet, say, Sheet2

In Sheet2:

Select say, A1:Z1 as the target range
(target range must be a 1R x 26C grid, the "converse" grid to the source)

Put in the formula bar: =TRANSPOSE(Sheet1!A1:A26)

Array-enter the formula, viz.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly Excel will wrap curly braces around the formula:

{=TRANSPOSE(Sheet1!A1:A26)}

(don't type-in the curly braces!)

Whatever's showing in Sheet1 A1:A26 will be transposed into Sheet2 A1:Z1

If Sheet1 A1:A26 contains for eg: formulae returning values which may
change,
then the changes will be correspondingly reflected in Sheet2 A1:Z1