+ Reply to Thread
Results 1 to 4 of 4

Copy horizontal data to vertical data

  1. #1
    Registered User
    Join Date
    09-06-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    2

    Copy horizontal data to vertical data

    Hi, I am using excel 2016 version currently. I want to copy my horizontal data in sheet 1 to vertical data in sheet 2. When I use formula sheet1!A$27 it does not fixed my column and vary my row. Instead, when I drag, it will become sheet1!A$27 again instead of sheet1!B$27. How should I fix that?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,333

    Re: Copy horizontal data to vertical data

    May be:

    =INDEX(Sheet1!$27:$27,ROW(1:1))

    Drag down

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,835

    Re: Copy horizontal data to vertical data

    Is the formula TRANSPOSE available in 2016? I think it is, but not the dynamic array version. However may cells you want to transform you'll have to select that many cells in Sheet2. In this example, the cells you want to transform are from A27:D27 so you'd have to have 4 cells vertically selected. With those cells selected, type this in as an array formula:

    =TRANSPOSE(Sheet1!A27:D27)

    To enter as an array formula instead of pressing ENTER, you press CTRL + Shift + Enter at the same time. You should see curly braces {} if it's entered correctly.

  4. #4
    Registered User
    Join Date
    09-06-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    2

    Re: Copy horizontal data to vertical data

    Thank you so much Gregb11! It works!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transposing Vertical data to Horizontal data without pivot table
    By Ian.Lance in forum Excel General
    Replies: 5
    Last Post: 05-15-2020, 09:11 AM
  2. Replies: 10
    Last Post: 06-26-2015, 09:05 AM
  3. Replies: 5
    Last Post: 04-26-2015, 11:04 AM
  4. Extract data from a vertical and horizontal Data Table as an Average.
    By Koddy in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 02-15-2015, 04:35 PM
  5. I want to learn how to use macros to change vertical data to horizontal data
    By SeanEboy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-23-2014, 06:32 PM
  6. Transposing repeating vertical data to horizontal data but retaining unique values only
    By mgcarino14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 12:57 AM
  7. Replies: 5
    Last Post: 04-23-2010, 08:01 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1