Excel HACK: Change HORIZONTAL Data to VERTICAL (NO Transpose Function needed)


Some time ago I loaded a video that showed three different ways to transpose data in excel and the third method was a hack so if you don’t remember it I’ve added the link to the video in the description, but then I got this message. I’ve got some issues with method three, right so method three was the hack, when the data is structured horizontally and you want to transpose it vertically there are very manual and
inefficient steps involved. (disappointed sound effect) How can the steps be made more efficient. Let’s take a look at another excel hack. (upbeat music) So in this example I have the data structured horizontally
and I want to transpose it so I want to make it vertical. Let’s try to do it here
and see what we get. So basically I would type in my initials then I put in the cell reference of what I want to be here first. So, this is the app, the app is sitting in cell B2 so it would be lgb2 and then this one would be lgb3, right? Because I want this
cell’s value right here. C2 that give me blend
and this would be c3. Basically the hack was that we’re going to replace the lg with an equal sign after we drag everything down, but what happens now
when we drag this down. Does this look right to you? So here I get b2 again and
then here I get c2 again it doesn’t become d2 and then e2, right? So excel doesn’t update the letters here. This is where our other
hack comes into play. As I was looking for ways to get this done I came across a method by Bob Umlas. Let’s go to file, options, go to formulas and instead of looking at it the usual way that we’re used to looking at our file we’re going to put a tick mark on R1C1 reference style and then click on okay. Did you notice what changed? All of these got numbers. If I type in now R2C2 then I press enter I get WenCal so instead of
seeing B2 I type in R2C2. Now if you go directly in edit mode in the cell and type in
equals and click on this you get a slightly different
type of referencing. This is the relative referencing so if you’re familiar with VBA you are familiar probably with this. So this means, minus 6 is
means go back 6 rows, right? So one, two, three, four, five, six and then go back two columns
is one two to end up here. So its like relative
from the point of view of this cell so that’s one way, but another way to do
it like fixed formatting basically what we know
as absolute referencing is to do this R1C1 style or in this case it would be R2C2. Now what I’m going to do is
to do the LG replacement trick using this RC style so I’ll do lgr2c2. What would this one be? This is sitting in the
third row, second column right, so that’s it. We just have to type
in this cell reference and then pull it down
and notice what happens. So we have R2C3, C4 so our
columns are actually changing. You can pull this done further until where you expect
to have maybe future data and then what you do is this. You replace it. Find and replace. First, what do we want to find? We want to find lg and we’re going to replace it with the equal sign. Click on replace all, all the replacements were done and that’s it and you have the right referencing. Let’s just switch back and
take care of the zeros. Let’s switch back to the style that we’re used to working. So let’s go back to
file, options, formulas and take away the check
mark here and click okay. Now we’re back, everything is the same. So if you want to get rid of the zeros or if you don’t want to see the zeros you can use the custom cell formatting so just highlight this, right mouse click, and format cells, and
inside the number formatting go to custom, use a custom format that hides the zeros. So since we have large numbers here lets use the thousand separator. I’m just going to copy this, so that’s how I want my positive
numbers to look like my negative numbers should
have a minus with the number and then for zero I’m just going to leave it as empty, click on okay and the zeros are hidden, but my references are still there so if
I add a new app to this and lets say we put 200 here it shows it to us in our vertical
view of the apps as well. So that’s another hack that you can use whenever you want to transpose data that’s horizontal into vertical format. I hope you like this video. If you did, give it a thumbs up and if you like these type of videos, you want to improve your excel knowledge, consider subscribing to this channel. (upbeat music)

97 Comments

  1. SISTER I HOPE YOU WILL BE FINE
    SISTER CAN YOU HELP ME IN THIS TRICK
    WHEN I MAKE HYPERLINK THEN I CLICK ON THAT CELL AND GO TO THE HYPER LINK SHEET
    SISTER I WANT THAT I DONT WANT USE MOUSE I USE KEYBOARD INSTEAD OF MOUSE CLICKING
    SISTER CAN WE MAKE THE SHORTCUT KEY OF THAT HYPERLINK CELL
    FOR EXAMPLE LIKE THIS
    I HYPERLINK A CELL TO THE OTHER EXCEL WORK BOOK AND IN THAT CELL I WRITE LEILA GHARANI
    AND I WANT THAT INSTEAD OF CLICKING LEILA GHARANI I USE KEY BOARD SHORTCUT KEY LIKE THIS
    (LEILA GHARANI) FOR THIS I WANT THAT SHORTCUT KEY USE (ALT+CTRL+E) OR (CTRL+E) OR OTHER
    SAME LIKE THIS
    FOR FLASH FILL WE PREES CTRL+E
    FOR DUPLICATE FILL WE PRESS CTRL+D

  2. Thanks Leila and Bob; wonderful video; R1C1 is an amazing trick and very easy to follow for anyone. Love you guys, Cheers !

  3. Great Video!!! I just wish when Microsoft introduced the R1C1 references back in their first version, that it had stuck and we used then today too (becasue they are easier to understand than the A1 references…)

  4. I didn't came across a situation where I can use this. But this is awesome . But I knew this can be done by this method when I saw the data in your video. 🙂

  5. @Leila Gharani
    Hey any idea teaching on POWER BI…please do help because now we have to deal with power Bi trust me i just open the youtube just to comment this to your latest vedio .

  6. Thank you so much Leila, I like so much your tricks in making excel so efficient and easy, always great to think out of the box

  7. I haven't seen that one yet. Very good. I'd always been frustrated that letters won't drag down. I suppose you could use =char(65) to get an A, and to drag it down you can do =char(row(a65)).

    My favoured method for transposing however would be =OFFSET($A$1,COLUMN(A1),ROW(A1))

    That way if you add a new row of data below your horizontal table you just drag your formula accross on your transposed output table or you can drag your formula down if you add new data to the right of your data table.

  8. ok, buen dato, el formato de numero para ocultar los ceros, #.##0;#.##0; Gracias desde Colombia.

  9. So wouldn't a Index formula do the same? Example for app in cell d8 I could put =INDEX($A$2:$M$3,1,ROW(D8)-ROW($D$8)+2,1) and in cell E8 I could put =INDEX($A$2:$M$3,2,ROW(D8)-ROW($D$8)+2,1). Then simply highlight both D8 and E8 and fill down the formula. Of course like you shown in another video, IFERROR could be added to crate NULL entries. but seems like allot less manipulation. In this approach, I don't need the RC reference as it is already applied within the formula?

  10. Why not select and copy all the data then key alt+E+S+E+V which performs a transpose in 5 keystrokes? (Paste Special Transpose Values)

  11. Hello there. I send you an email. I am facing in mail merge . Plz make a video on that. I will wait

  12. I thought you were going to end with Copy / Paste values only, then delete your original horizontal data. I mean if you're wanting a vertical table, you probably don't need the horizontal table. Anyway, great video.

  13. hey, Leila, I have a challenge for you, last year I needed a formula with "DSUM" that u can fill down and fill Right, with a lot of work I managed to achieve this
    Can u do this? Remember only DSUM works in the case, sumproduct or crappy sumif will duplicate some value and give wrong results.

  14. سلام سرکار خانم قرنی
    چرا این آموزشهای عالی خودتون رو ب زبان فارسی نمیزارین؟
    بهر حال ایرانی ها هم سهمی از این آموزشها باید داشته باشن

  15. Hi Leila. Thanks for the new video. In viewing it, I remembered my post to your original video with my method #4 as follows (using your worksheet visible in the video as the example):
    1. In cell A4, make a formula reference as: =A$2; in cell A5, make a formula reference as: = A$3
    2. Copy the contents of A4:A5 to the right through M4:M5 (or further if you want to pad for future additions to the source data)
    3. Highlight A4:M5 and press CTRL+C
    4. Move the pointer to cell D7 and press CTRL+ALT+V, click on the Transpose checkbox and press enter; use the format painter to copy the format of A2 or A3 to D7:E7
    5. The previously horizontal orientation will now be vertical and the cell formulas will reference back to your original horizontal data (i.e. will be linked to the original data)
    6. Delete the helper formulas in A4:M5
    I find the above a pretty quick hack to go from horizontal to vertical (lock the rows in the interim step) and from vertical to horizontal (lock the columns in the interim step) and with the bonus that the transposed cells are linked back to the source. As in your method, you can copy beyond the current range of data and format to hide the zeros, if you want to have more linked cells for future additions to the source.
    So, that is my tip for this challenge. I hope you and any others find if useful when needing to transpose source data to the opposite orientation while maintaining a formula link back to the source. Thumbs up!
    PS – Just read below the OFFSET methods from Bondi and Pawan.. those are GOLD.. even faster than my method #4 hack! Learn something new and valuable every day on your channel!

  16. I have a very huge data of lists of expenditures date wise, but some of them are duplicates. Is there any way to find same expenses done on same date?

  17. Hi GURU, you may have covered this but I can’t find the video

    For some reason when i export a report to excel, the date columns shows up as X’s . Do you know of a way to resolve?

    Thanks for any help

  18. I really like what you are doing ,you are great ,please tell me what is the best method to learn advance excel skills , and what are the best books , apps or programms to improve your excel knowlage ?
    Thanks .

  19. Hi there,
    Can you share a video where you can teach us how to take printout of bigger excel sheets?

  20. I had whole table to transpose, even these methods didnt help me to convert horizontally/vertically 🙁

  21. I want to say deeply thank you ☺…. I use your method today in office…. I was so excited that it really work perfectly 😊…. A very informative video for me… I am feeling to say thanks to you once again 😊

  22. Thanks, very helpful.

    I have a one scenario could you
    please help me with that:
    1) We have an excel sheet having dates as column names .eg 01 Jan, 15 Jan, 02 Feb, 30 Mar etc…
    2) We have like wise dates for 2018 and 2019 and all these columns are jumbled
    3) So we have to rearrange the columns in ascending order as per dates eg 01 Jan 2018 , 15 Jan 2018 etc…30Mar2019

    4) But this becomes really tiring process to make these changes every time since the column order is not fixed and any date can go anywhere. So we have to rearrange dates everytime.

    5) Is there any way we can automate this process or any simpler way to accomplish the task ie. rearranging columns based on date.

    Thanks in advance.

  23. Hi can you solve and tell me shortcut – I have data MI-DL-po-125 , I want series mi-dl-po -126/127,,, but without drag is there any formula .

    Like if we want 1 to 10 counting we mention 1 and after that we select cell till 10 and press alt+f+i+s,, enter after this all 1 to 10 shrimati come.

    Same like do you have any formula??

  24. Thanks but it's not useful much as we face issues where we have data vertically and horizontally connected and tht too with merged cell

  25. hi is there any trick that we need not type for each row manually just drag it and get result as here only 2 rows it's easy for more no. of rows what to do

  26. I love this video but how do you use that same formula for horizontal . i cannot get the column to change in my formulas

  27. Hi Leila, I love you videos, and this solves my long term pending problem. Thank you very much. A quick question though, is there a way following this, where if I insert a new column in my original horizontal data, that gets automatically updated in the vertical ?

Leave a Reply

Your email address will not be published. Required fields are marked *