Method 3 Excel hack works for vertical to horizontal data but not the other way round. Check out this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-dF0cVRyC8c4.html to learn a new Excel hack that works on horizontal to vertical data.
What about a Method applied. From Horizontal to vertical and dataset comes from another worksheet. And lastly, tranposed data are not moved if you insert a column or row? appreciate it. thanks
You are really great. Thank God the newer version of Excel fixed this issue so that we can just hit enter now. I subscribed. You explain very well. I also think method 3 is more reliable since it avoids the function transpose() which can behave unexpectedly.
Leila thank you very much, i have my job because of you, the one thing i forgot about excel. You are really a good person, i hope everything goes good in your life and your children (if u have one) will grow as good persons as you are!
I came up with this formula: =INDIRECT("B"&COLUMN()) and copy across. Bob Umlas showed your example #3 in one of his books (This isn't Excel, it's magic). Method #3 is definitely the easiest.
How do you do with the origninal data in horizontal format and to be copied into a vertifical format? The opposite sitatuation of what is explained in the video? Thanks in advance.
I can't believe how quickly you helped me including finding you in the search. I've been using Excel for probably longer than you've been alive (1987). I've stuck with a very old version and finally was forced to upgrade. I used to teach Excel but now need to have you teach me.
Whoever figured out method 3 deserves a monument :) BTW in case you have not noticed, do the replacement of the prefix by = AFTER you dragged and filled ALL cells you'll need. Once you replace it you won't be able to drag and fill anymore and get the right values
Leila is one of the Trainers whose position is in TOP 99th Percentile!! It’s always pleasure to watch her teaching with different style and perspective. Hatsoff !
This is the first time I like tutorial videos...thank you Leila....a good combination of intelligence, knowledge and public speaking will keep you moving forward
Transport Method helped me a lot. I was preparing reports for data analysis for my company. And your method saved many hours and indeed thank you so much for saving my life. Amazing Person you're. Thanks
Method 1 is the reason I came here. For me it's the fastest because when I'm needing this I'm usually just looking for a quick static representation. I use method 2 when needing dynamic, although 3 is fun! Also love the info about dynamic arrays!
You're a lifesaver - #3. I did method #1 for the first time today to rotate cell range A1-AM1 through a1 - am30 - after much trial and error. I was on the wrong track with 'pivot table'...THen I found TRANSPOSE to rotate using copy/Paste. Thank you for #3!
Method 3 was a smart way to do it. I use method 1 mostly because I never had to link with the original table. I have also used offset function but I don't remember the formula. It basically would offset rows as we copy to the right. Thanks for the video.
Great video Leila, thank you. I like the =TRANSPOSE formula. It is dynamic and because we have a number of users picking up the worksheets to transpose one tab to another, this will prevent anyone from accidentally deleting the formula in one specific cell and throwing the the entire project information off. The inability of changing parts of an array prevents that accidental deletion of any part of the array. Very secure! Plus, we use both vertical to horizontal and horizontal to vertical from one tab to another.
LG replacement is a cleaver move, really like it as it opens a whole new dimension for me, thanks. The only problem left is dragging the cell horizontally for more flexibility. So here's another solution using Index formal: =INDEX($A$2:$ZZ$2,1,ROW($A1))
Method 3 saved my neck! Thank you for the tutorial, Leila. I had 30,000 values to put into 22 columns in a specific order. After doing the first two rows, I was able to drag it down to the last needed row. BIG time saver!
I love your channel. I understand why you have over 2.3 million subscribers. And I love your Persian accent. When I was in Elementary school through High School my best friends parents were from Iran and his mom and Dad spoke with the same accent. Brings back great memories.
These methods are really amazing, Thank a lot. I was searching from a long time, but i got now. the process how to transform value from vertical to horizontal cell, and i am getting changes in horizontal cell while changing in vertical cell. Thanks Alot
Hello Leila, thank you so much for this video. I am trying to learn Excel in this lockdown and I wanted to use transpose function, and it was not happening. for 3-4 hours I was searching for a right solution but your video proved to be the best one. Also the next video hack for transposing Horizontal to Vertical by changing to R1C1 was great as well. Thank you so much. Cheers!!!
Maam, You are Awesome ! and have a very good quality among others to teach every complicated things in simple way and very intelligently. Always coming to you with tension and got calm and relaxed when u start explaining it very simple style and in soft voice thats seems to be so easy.. Thanks alot !
Hi Leila. I love method 3. I have a spreadsheet for an annual event that I revisit every year. The spreadsheet gets bigger, however I forget how to do transpose each time, and waste a lot of time re-learning it. I now have a spreadsheet that is a template, so is bigger that I need. I have also added notes on how to do method 3 so I can remember when I need to extend it in future.
We could use this method also, 1. Transpose using Paste Special. Once done area is highlighted. 2. Then write transpose Formula and Press Ctr + Shift + Return. It is also convenient and faster especially in older Versions of Excel.
I used to do method 1, then learned about 2 and 3! And now I can tell that method 3 is my fav! So simple yet so smart! thank u so much for these awesome tips!
I had been looking for the transpose option and it was right there in front of me lol! Thanks for the tips! The transpose(x,y) is my fave so far, but the lg one is something I'd never have known
Excellent. Thanks to my beautiful, brilliant and most intelligent Excel Miracle Maker/Teacher. We are very lucky to learn many tricks from you. Waiting some more in the days to come. Once again sincere and special thanks.
@@abuzaransari9032 Appreciate karna bhi insan ki eq quality hote hy..Sirf insan ki bol raha hu...or youtube me comment section kis chiz keliye rakha hy sabko pata hy..Aur ye lady ka pura videos dekhhke aao.. jisko kutch samajh me aya, fayada hua, wo appreciate karne me tumko kya mushkil hy...agar tumko ye video dekhke kutch samajh me ny aye , fayada ny hua to samajh lo tum galat jagah pe aye ho....Aur my bhi poochta hu , KOI BHULAYA TUMKO IDHAR??? Video pasand aye to comment karne keliye ye ladki khud boli hy video ki andh me..Samjhne wala samajh jayenge....U dont deserve this! and asking u again"Who invited u here?"
Great tutorial again. Thanks a lot! I'm a big Fan of your channel :) Learing stuff every day. You have such patience in explaning things and a nice voice.
4th method, using vertical and horizontal indexes (for example I put my horizontal indexes from e2,f2,g2... 1,2,3,... and my vertical ones d3,d4,d4... i fill up with 1,2,3 simple formula =INDEX($A$1:$C$20,E$2,$D3) in E3 , copied vertically then horizontally will will transpose A1:C20 into E3:X5
خیلی ممنون! درسهایتان خیلی دوست دارم! I prefer #2 because I usually transpose from one worksheet to another, and that would mean a lot of typing if I were to use #3. I can see #3 is better for working within one worksheet, though.
