Results 1 to 9 of 9
  1. #1
    Senior Member
    Join Date
    Dec 2004
    Posts
    376

    Default transforming excel row to column

    I have a row of data in excel and I want to transpose it into a column. How do I do this?

  2. #2
    Short Member pcuser42's Avatar
    Join Date
    Apr 2006
    Location
    Pukekohe Shuttle
    Posts
    10,097

    Default Re: transforming excel row to column

    Manually.
    "He who resorts to personal insults hath lost the argument."

    Twitter

  3. #3
    Mike Mike's Avatar
    Join Date
    Dec 2004
    Location
    Rosarito, Mexico
    Posts
    1,383

    Default Re: transforming excel row to column

    Quote Originally Posted by bpt2 View Post
    I have a row of data in excel and I want to transpose it into a column. How do I do this?
    Select the row, Ctrl+C to copy, select the column you want it in (you may need to move it over to fit, or put into another worksheet if there are no free columns), go Edit > Paste Special and select "Transpose" and press OK. It will paste your row as a column (or a column as a row).

    HTH

    Mike.
    what are you doing looking in here?

  4. #4
    Senior Member
    Join Date
    Dec 2004
    Posts
    376

    Default Re: transforming excel row to column

    How about if the row includes formulae that reference cells on other worksheets. Is there a way of preserving the same cell references?

  5. #5
    Mike Mike's Avatar
    Join Date
    Dec 2004
    Location
    Rosarito, Mexico
    Posts
    1,383

    Default Re: transforming excel row to column

    Quote Originally Posted by bpt2 View Post
    How about if the row includes formulae that reference cells on other worksheets. Is there a way of preserving the same cell references?
    Excel is usually quite clever at keeping cell references correct.

    EDIT: I just tried it, and it keeps all the cell references correctly.

    Mike.
    Last edited by Mike; 25-10-2006 at 11:52 AM. Reason: update
    what are you doing looking in here?

  6. #6
    Senior Member
    Join Date
    Dec 2004
    Posts
    376

    Default Re: transforming excel row to column

    Can you look at that again. My cell references change relative to the new location of each formula. I need them to be absolute and refer to the same cells as before

  7. #7
    Mike Mike's Avatar
    Join Date
    Dec 2004
    Location
    Rosarito, Mexico
    Posts
    1,383

    Default Re: transforming excel row to column

    Quote Originally Posted by bpt2 View Post
    Can you look at that again. My cell references change relative to the new location of each formula. I need them to be absolute and refer to the same cells as before
    Then they need to be made into absolute references before you do the copy - in your formulas you highlight the cell reference you want to be absolute, and press F4. If you only want row or column absolute, continue to hit F4 until the $ appears in front of the part you want absolute.

    Mike.
    what are you doing looking in here?

  8. #8
    Lab Rat
    Join Date
    Mar 2005
    Location
    Hamilton
    Posts
    141

    Default Re: transforming excel row to column

    Hi,

    If you don't want to change your formula references as below, I wrote a short macro to help do what you want. It worked in the simple test I gave it. Just copy the code below into a module in the spreadsheet you're working on (let me know if you don't know how to do that).

    Then run the macro back in Excel. The macro requests the source cells so just select the range you want to copy. Then it asks for the top left cell to paste the results to, don't select more than one cell. Also you probably won't be able to do multiple selection areas (I don't know what would happen!).

    HTH

    Code:
    Option Explicit
    
    Public Sub PasteTransFormula()
        'Sets the source range
        Dim rngSource As Range
        On Error Resume Next
        Set rngSource = Application.InputBox("Select the source cells to copy.", "Select Source", _
        Selection.Address, , , , , 8)
        
        If Err.Number <> 0 Then
            MsgBox "Action cancelled", vbOKOnly, "Action cancelled"
            Exit Sub
        End If
        
        'sets the destination range
        Dim rngDest As Range
        Set rngDest = Application.InputBox("Select the top left cell where you want to paste " & _
        "the results.", "Select Destination", Selection.Address, , , , , 8)
        
        If Err.Number <> 0 Then
            MsgBox "Action cancelled", vbOKOnly, "Action cancelled"
            Exit Sub
        End If
        On Error GoTo 0
        
        'Checks that only one cell is selected in the destination range
        If rngDest.Cells.Count > 1 Then
            MsgBox "Please select only one cell in the destination.", vbOKOnly, _
            "Select one cell only"
            Exit Sub
        End If
        
        'Loops through every row and then for each row loops through all the columns
        'and pastes the formulas them in the destination location swapping columns
        'for rows.
        Dim intRow As Integer
        Dim intCol As Integer
        For intRow = 1 To rngSource.Rows.Count
            For intCol = 1 To rngSource.Columns.Count
                rngDest.Offset(intCol - 1, intRow - 1).Formula = _
                rngSource.Cells(intRow, intCol).Formula
            Next intCol
        Next intRow
    End Sub

  9. #9
    Senior Member
    Join Date
    Dec 2004
    Posts
    376

    Default Re: transforming excel row to column

    Macro worked nicely thanks.

Similar Threads

  1. Excel column division question
    By Billy T in forum PressF1
    Replies: 11
    Last Post: 15-03-2006, 05:01 AM
  2. excel column puzzle
    By santae in forum PressF1
    Replies: 21
    Last Post: 20-11-2002, 03:49 PM
  3. Replies: 1
    Last Post: 18-11-2002, 09:23 AM
  4. Printing Column Headings in Excel
    By in forum PressF1
    Replies: 0
    Last Post: 15-06-2000, 11:19 AM
  5. Line - Column charts in Excel
    By in forum PressF1
    Replies: 0
    Last Post: 19-11-1999, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •