Formula Question

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I am trying to move data that is currently in columns to rows

Value 1 Value 2
Value 1 Value 2 Current way
Value 1 Value 2

I want it to appear like this

Value 1 Value 1 Value1
Value 2 Value 2 Value 2

Is there any easy way to do this in a formula. I have a spreadsheet that
has a lot of data on it that I need to easily move into a display as shown
above.

Thanks in advance.
 
Hi
if you only want to copy the data try the following:
- copy the source range
- select your target range
- goto 'Edit - Paste Special' and choose 'transpose' as action

If you need to link the cells (e.g. the source data can be changed and
you want to reflect this on a second sheet/range) try the following
(assumption: your target range starts on a separate sheet in cell A1).
Enter in A1:
=OFFSET('source_sheet'!$A$1,COLUMN()-1,ROW()-1)
and copy this to right/down
 
In its simplest form:

Highlight Your Range A1:B3
Then Copy it (Ctr-C)
Click on D1, then at the menu -- Edit, Paste-Special, Check the Transpose
Box, OK
Done

Consider however that :
1) Back up your data before you "try" anything.
2) If your range is over 250+ rows your going to have problems as Excel
STOPS at Column 256.
3) Any Range your select as a destination (D1 in this case) must free an
open (free - no existing data) area or it will be over-written.
HTH

at hotmail dot com> wrote in message
news:[email protected]...
 
Another Example:
Assign a RangeName to A1:B8, say Mydata
If you want the New Output to be 8 columns by 2 rows
click on and highlight an empty area of your sheet say D5:E12 << should
be highlighted with D5 the active cell.
while still highlighted in D5 enter =transpose(Mydata) then hold down
Control+Shift+Enter.
Copy D5:E12 and Paste_Special Values to De-formularize..
HTH
 
Back
Top