3 Ways to Transpose Excel Data (Rotate data from Vertical to Horizontal or Vice Versa)

[@LeilaGharani]

Method 3 Excel hack works for vertical to horizontal data but not the other way round. Check out this video: kzbin.info/www/bejne/mneTlImIrqhrmZY to learn a new Excel hack that works on horizontal to vertical data.

[@jordanmalis7250]

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

[@kaantuncyildiz2937]

you inspired me with vertical to horizontal, i managed to make horizontal to vertical, thank you very much

[@kfcassoe]

You can also combine index with column function to transpose rows to columns and vice versa

[@aryanverma2005]

Please Help Values becoming zero in method 1 . I do copy special, and then it asks for some "Update generation" file. Even in the same sheet.

[@AaronAnthony-p7v]

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.

[@rajaaslam2118]

Hi Leila, Your 3rd method is so outside the box thinking. I love it. Thanks so much.

[@theshivelyfamily]

Method 3 is so genius and yet so simple. Perfect.

[@marthalacomba3306]

yup that's by favorite too

[@krystaladeoye2911]

Agreed!! It is brilliant!

[@triveshd5937]

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.

[@robertlohman8947]

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.

[@HindSena-005]

Method 1 🎉

[@kapcons8796]

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 !

[@abuzaransari9032]

pucha kisine?

[@movetomarco9625]

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.

[@excelisfun]

Yes indeed, Method Three : ) Ctrl + H Rules! Thanks for the vid, Teammate!

[@LeilaGharani]

:) Thanks Mike. Yes and with dynamic arrays transposing becomes super easy too.

[@bengi8406]

Hi guys I was wondering does anyone knows how to do the third method but with the filters ON, That is, that it follows the filtered rows pattern

[@mloebab]

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

[@sondrarubin7605]

YOU ARE A GENIUS!!!!!!! I HAVE BEEN SEARCHING THE WEB FOR THIS FORMULA!!!! THANK YOU!!!!

[@АртурКривошеєв-у9п]

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!

[@nuvoclassic]

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!

[@GWShark1]

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

[@stevenintexas6947]

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.

[@salmansabir2644]

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

[@bashiralmadani780]

Hello Leila, i have to admit that whenever i face a challenge in Excel i find my answer in your videos. You are the best. Thank you

[@OzduSoleilDATA]

NICE! Very nice. I like that lg-replacement move.

[@LeilaGharani]

Thanks Oz :)

[@farazshaikh74]

That really cool move.. !!! :)

[@krn14242]

Ditto. Smooth move

[@bahaaomar9927]

Nice I like last option very nice

[@BinhLeSG]

that's magic

[@kagankocaoglu6215]

Hi Leila, also try this =OFFSET($A$2,COLUMNS($D3:D3),ROWS(D$3:D3)-1) in D3 cell. And than drag it to other cells

[@LeilaGharani]

I like this one too! Many thanks for sharing.

[@wackolas]

I do something slightly similar with Index, where row in the index = column() - 1st column of transposed table

[@kagankocaoglu6215]

@@wackolas index is applicable but you must select an array and that limits you. Offset formula lets you drag the formula as long as you want

[@ANDYT8]

This is amazingly neat and just what I was looking for to solve my transpose problem. Thanks.

[@AbhishekTripathidce]

Hi LG, third formula is really innovative and faster. I will use this in future. Thanks A lot for this video.

[@mickthunder1107]

I stumbled upon your channel and you have no idea how grateful I am to you. Bless you.

[@LeilaGharani]

Great to have you here!

[@jessicagroenhout6758]

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.

[@KirkNeddy]

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!

[@atecathe718]

wow!!! you are very helpful to people like me who needs this very badly . keep it up and thank you so much very big help

[@sanishtv]

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 !

[@turboconch2]

Method 3 is what I needed after I spent 3 hours trying to figure this out!!! Amazing work!!

[@mohitjagad]

Hi Leila, 3rd one is totally a WOWW trick.. awesome it is..👍👍 Thanks for sharing.☺

[@SermedAlWasiti]

Thank you very much for method 3...it saved me...I have more than 3000 data points I needed to transpose, and it worked like a charm...

[@SermedAlWasiti]

Will explore your videos further...and I may subscribe

[@LeilaGharani]

Glad it helped!

[@jonathannali7682]

Thank you. I was going to copy and paste one by one but this has made life much easier

[@dan-vau]

This is brilliant help and just saved me my life of Excel...Thanks so much

[@RED-CITYMobile]

I like it when you execute the formula lg to replace with =... YOU ARE AN EXCELLENT TEACHER Leila!

[@joebell30]

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.

[@LeilaGharani]

Glad you liked it!

[@sunildusa1966]

LEILA, Thanks a bunch. You saved me a lot of time. Method#3 is very helpful for me. Thank you once again.

[@LeilaGharani]

You're very welcome. Glad you find the tutorial helpful.

[@abejacob5410]

The transpose formula rocks! Watching this has been very valuable to me!

[@LeilaGharani]

I'm glad to hear that Abe!

[@Aj-gk5uu]

Exactly what I was looking for! The lg + Ctrl H is a time saver!

[@1gopalakrishnarao]

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.

[@LeilaGharani]

Thank you very much Gopala for your kind words. I'm very happy to have wonderful people like you who find value in the videos and support my work.

[@leroidelacave]

I used the third method...very effective! Thank you!

[@natashadoyle1126]

Really easy to understand. I'm now using all three methods depending on how quick or detailed I need the report to be. Thanks

[@LeilaGharani]

That's great! It's always good to have options :)

[@fthorup]

THANK YOU! After 15 frustrating minutes with excel "help" this was exactly (method 3) what I needed

[@reuben.odinaka]

Thank you so much Leila. Step 1 is exactly what I needed.

[@haldoglife]

Thank you! Only needed static and this saved me hours of copy and paste.

[@Pablo_6974]

Working since last 4 years in excel never knoew the 3rd method exist. Amazing !!!❤

[@shironmania]

You do explain and present any post very understandable. Very rare skills of communication as well expertise in excel

[@LeilaGharani]

Many thanks for the kind words. I'm glad you like it.

[@buhariidris2402]

The third one look more fantastic. Learning alot from you all the time 😍😍😍

[@opknox1212]

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!

[@LeilaGharani]

That's great to hear, Claudia!

[@debojyotiojha2350]

You make my day with the third version. I am enthralled both with your beauty and excel art 😎😎

[@anishpillai]

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.

[@iawwad]

Method 3 was easy and genius at the same time, never thought of it :) Thanks Leila.

[@LeilaGharani]

It is one of my favorite hacks. Glad you like it too Ibrahim!

[@fraserassembly6875]

Every time I have a question you answer it quickly and effectively. Many thanks.

[@sanjitpaul2953]

Love the 3rd trick. Thank you ma'am. 👍👍👍

[@gregorymumboh6254]

You always solve my excel headache, and not forgetting your sweet voice and twee facade,😘

[@FR-pk5mc]

Great video! Love the Excel hacks you use!

[@Moshahed-Bangladesh]

First time I watched this one of your videos that made me subsribe to your channel. Fantastic!

[@rezataba6204]

Nice materials. Thanks! Saved my life!!!

[@marcbombenon3431]

Method 1 worked best. Just wanted to change the formating from Rows to Columns and erase original. Thanks for the tip

[@DarthJ2164]

Method 3 is a true time saver and easy to apply! THANK YOU!!!!

[@beencouraged1284]

Excellent, thank you - Method 1 works perfect.

[@ParadoxGD]

you are my favorite tutor. clear and functional

[@LeilaGharani]

Thank you! I'm glad you find the tutorials easy to follow.

[@vivek-placardcommunication9705]

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!!!

[@ianodonoghue6011]

Method 3 is genius. If I could like this twice I would.

[@mostafa4321]

you elevated excel to a fine art with every Video you post. awesome keep it up.

[@LeilaGharani]

I'm glad you like the videos! Thank you for the support.

[@sonalshukla9447]

The 3rd method just worked. Thank you so much ☺️

[@LeilaGharani]

You're welcome!

[@learnspreadsheets]

Wow! Method 3 is something I’d never considered, clever! Having said that... dynamic arrays CHANGES EVERYTHING!!

[@LeilaGharani]

Agree - Dynamic arrays are a game changer!

[@nagalakshmivenkatesan9997]

Thanks for the second one. It's very useful

[@d.i.leephdcde9674]

This is fantastic and straight forward!!! I prefer methods one and three

[@prashantsethi7837]

Method 3 is Wow! Great tip. Thanks for the videos.

[@LeilaGharani]

Glad you like it :)

[@Taskade]

This is super helpful, thanks for sharing!

[@israeliseoluwa6704]

Thanks a lot for methods 2 & 3! ❤

[@Aesthetic45]

Love your teaching style! Simply put yet so effective. Appreciate the tutorials (:

[@abuzaransari9032]

pucha kisine?

[@sanishtv]

@@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?"

[@NaseerMusic]

Thanks a lot Leila, I used your method to translate thousands of interval data from column to rows that would have taken me ages doing manually.

[@f94s1]

Very informative also tricky. love the third one

[@Tanushree00099]

wow ....i was thinking of joining excel classes in my area. But then I am lucky to have found your online classes.

[@LeilaGharani]

Glad to have you here! I'm glad you enjoy the tutorials.

[@mohammadiqbal5954]

Thanks Lella, you solved my problem in a very simple method. Its difficult for an average excel operator to apply formulas.'s

[@rajshreejoshi9765]

Method 3 is perfect one. I just confused while using transpose function thanks for video.

[@nuzyzavahir5844]

You saved my neck for good Leila. Thanks a bunch!

[@krono32]

This woman is a lifesaver! Thank you so much!

[@fazhafathima8233]

How are your videos even free? Thank you, you are a life saver

[@LeilaGharani]

Glad you like them!

[@chipturner495]

Just used this with a large dataset - saved me a ton of time! Thank you Leila!!

[@LeilaGharani]

I'm glad to hear that. You're very welcome Chip :)

[@dougmphilly]

#3 is so clever. this a one my go to channels to solve my excel problems.

[@LeilaGharani]

Glad you find the tutorial helpful Doug.

[@steniorodrigo5590]

The last one is the one I was looking for. thank you.

[@LeilaGharani]

👍

[@akanjirosetta7573]

OMG I wish I could like this twice. Sooo helpful

[@stevemorris4938]

Method 4, make the data into a table (Ctrl+T) then create a Pivot from it putting App in columns and Sales in Values. Simple, quick AND dynamic :)

[@LeilaGharani]

That's a good one too! Thanks for sharing.

[@gelligonzales3959]

Steve Morris Q

[@davidhansen527]

Steve, you took my words right out of my fingertips :)

[@rohityadav-gj7ow]

your video very simple and easy to understand thnks

[@ramakrishna-tq2wj]

Thank you so much for sharing such a useful information....😊

[@ckokse]

Excellent! Did not know the dynamic methods. Keep up the good work!

[@LeilaGharani]

Glad you found something new here :)

[@peteryeh2008]

method 3 was perfect ! Thank you very much.

[@devinaputri8908]

Method 3 is just WOW! Thank you.

[@noturmomma]

❤1 thank you I had forgot how to do this you reminded me quick fast and easy

[@brianlack7763]

Method 3 is so clever! Thank you.

[@LeilaGharani]

You are so welcome!

[@ziabashir]

you are great teacher and trainer,

[@husseinjaburable]

I use Index Function and combine it with Columns AND Row functions. It works perfect for me

[@mrprashant1]

The 3rd method is absolutely perfect

[@jimohololade5385]

Thanks very much, method 3 is superb

[@ignaciovila]

Thank u so much! Saludos desde Argentina

[@ckokse]

Pure genius! I wish I had known this earlier. Could have saved me a lot of time typing in formulas manually. I am gonna use the lg-move from now on.

[@LeilaGharani]

lg-move :)

[@KevinDMarshall69]

These are great tips. I’ve always used the Offset Function to transpose data dynamically. 😬

[@AZBYAAA]

Great Video as usual, method 3 is so simple and works, keep the videos coming.

[@michalroesler]

3rd method is dope. and it's creative as well.

[@primaveranz]

The sign of a true genius is to find the simplest solution and this is a perfect example ;)

[@tarunj1361]

Honestly speaking...you are Magician

[@LeilaGharani]

Excel witchcraft 😁

[@harry902008]

The method3 is very cool. Thank you Leila 🙂!

[@LeilaGharani]

You're welcome 😊

[@jhindson88]

Great work! You are my "go to" site for any Excel function

[@LeilaGharani]

Wow, thanks!