How to Change HORIZONTAL Data to VERTICAL in Excel (NO Transpose Function needed)

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Struggling with transposing data in Excel? Discover a clever hack to transform horizontal data into a vertical format efficiently!
⬇️ Grab the workbook here: pages.xelplus.com/transpose-h...
🔍 What's Inside:
▪️ Revamped Hack: Explore an advanced method for transposing data, overcoming the limitations of traditional techniques.
▪️ Step-by-Step Guide: Follow a clear, detailed walkthrough to transpose your data with ease.
▪️ Bob Umlas' Method: Learn a unique approach using the R1C1 reference style for seamless data manipulation.
In this Excel tutorial, we explore an efficient hack for transposing data from a horizontal row to a vertical column. If you previously encountered challenges with manual and time-consuming methods, this technique will simplify the process.
In my previous video ( • 3 Ways to Transpose Ex... ) I talk about 2 other methods you can use. One method is to copy the data and paste special and select transpose. This way however the data is static.
Another option is to use the Excel TRANSPOSE function. One thing I don't like about the Transpose formula is that it's an array function and you need to remember to press Control shift enter (at least until you get dynamic array functionality). You also need to highlight the answer area first before inputting the Transpose formula.
LINKS to related videos: 3 ways to Transpose (including Transpose function): • 3 Ways to Transpose Ex...
Unstack Excel data: • Quick Excel Trick to U...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/transpose-horizontal-file

[@1lynton128]

Leila, not only are you an outstanding teacher but you made a point of thanking Bob for his contribution. It would have been easier for you to do this video without mentioning him. That you did mention him, elevates you to the highest level of great teachers.Thank you.

[@user-im4fj4th5c]

Leila you are amazing!!! I spent entire night to manually write it down each sell reference with an equal sign. This makes my life easy. I appreciate it a lot.

[@wayneedmondson1065]

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!

[@BondiMacF]

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.

[@katerina6495]

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

[@ExcelFormulasHacks]

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. 🙂

[@excelisfun]

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...)

[@LeilaGharani]

Thanks Mike. R1C1 also sounds cooler than A1 :)

[@amirnetherlands]

Always the quickest and best solutions.

[@francisconava9874]

I love your lessons miss. Greetings from México

[@JuditeCorreia]

Excelent tutorial! Very good and time saving!

[@anv.4614]

Thank you Leila. Your tricks are amazing. Glad to learn them.

[@dashrathpanchal8393]

Superb hack Leila , amazing as usual, keep it up

[@nonsoamamchukwu9598]

Good one, Leila! I must be so lucky to find this on time! Thanks

[@LeilaGharani]

I'm glad you found it on time!

[@noveenarani3169]

Thanks a ton Leila, you and your tricks are amazing..you made my day!❤

[@Luciano_mp]

Amazing trick. Thanks Leila.

[@sethmaundu3421]

incredibly simple. Thank you.

[@Bugkiller666]

You are the Best Leila !!! Thanks

[@sachinrv1]

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

[@LeilaGharani]

You're very welcome Sachin. Agree - it's a nice change to the usual formulas :)

[@sumitgoyal34]

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 😊

[@LeilaGharani]

You're very welcome. I'm glad it was helpful.

[@RahulJauhari]

Wonderful great work I have no word for u thank you very much

[@FR-pk5mc]

You're the Excel Queen!

[@SholaDsdg]

Great video Leila!

[@krn14242]

Wonderful followup, thanks Leila.

[@LeilaGharani]

You're very welcome.

[@gautamsarkar7517]

Thank you Leila, have been an ardent fan of your channel and it has helped me solved many problems helping me improve my Excel skills

[@LeilaGharani]

Great to hear!

[@sanjaypatwa8045]

Great Video. thanks for Guidance

[@pwnyadav007]

Great video...There is a super hack of transposing: =Offset($A$2,column(A1)-1,row(A1)-1)

[@empaguia]

oh Thank you... just finished my job easily with this comment! anyway thanks to this video tutorial also'

[@MrZeeshany]

Thank you! spent 4 hours and this comment solved my requirement

[@nerciedenele5173]

How does this even work?

[@TarunDhimanOfficial]

Mind Blowing....you are unbelievable :)

[@georgetosounidis5545]

Oh my god (or should I say “goddess “?). That was simply mind blowing!!!

[@LeilaGharani]

Thank you! and thanks to Bob for this trick :)

[@ashoksahu9546]

Exactly I was thinking like this.

[@Dexter101x]

Excellent video.

[@ramandeepsweety]

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 ?

[@bektendosmambetov938]

Thanks, Leila. It's useful as always.

[@LeilaGharani]

I'm glad you like it.

[@sarfarajkureshi6569]

Nice trick very usefull in excel world

[@harati6744]

Awesome! THANK YOU! :)

[@Dipmondal]

Thanks for this awesome solution.

[@LeilaGharani]

You're very welcome.

[@aboelmagd100]

So smart like usually

[@cortinas54]

Like always you are the best! Regards!!!

[@DougHExcel]

thank for the hack explanation!

[@LeilaGharani]

You're very welcome!

[@tarunj1361]

As i said earlier, You are Magician

[@WilliamOliveiraExcel]

conteúdo relevante e muito didático.

[@JessicaTanGan]

You are amazing!!

[@luisneto2165]

Very good! Thanks.

[@LeilaGharani]

You’re very welcome Luis.

[@TroyJScott]

Wow. You’re Gangster. Love it

[@noumonmunir5047]

awesome video!!

[@antoineviekoamekodjo6616]

Magnifique ! So usefull

[@capitancristian8841]

You are amazing!!! thank you very much!!

[@LeilaGharani]

You're very welcome!

[@amiladevapriya1721]

Thanks, Leila!

[@SenthilvelMurugesan]

I would wish to give a love for the hacks and tricks

[@sudhirhiwale9784]

Superb, Amazing, excellent

[@sudhirhiwale9784]

Thank you Mam

[@ismailismaili0071]

thanks Ms. Leila you are the best

[@LeilaGharani]

That's very kind Ismail.

[@trinamenta]

I love your videos - thank you! One item I am struggling with is the starting point. My data set has 63 rows that I want to make columns and 19 columns that I want to make rows. How do I get to the starting point from 19 columns to 19 rows, like how you go from App and Sales as rows to App and Sales as Columns. It would take me forever to type this...

[@poligapoliga3700]

Awesome 👏🏻👏🏻

[@yulinliu850]

Cool hack! Thanks Leila : )

[@LeilaGharani]

You're welcome Yulin :)

[@stephenbrincat7124]

Well done Leila

[@primaveranz]

Genius as usual ;)

[@chelseawarren6048]

Oh my gosh, this is such a lifesaver! Thank you 💛💛💛 ^Chelsea

[@LeilaGharani]

Glad it's helpful, Chelsea!

[@BRoslansky]

Slick trick. Let's see that in VBA!

[@hattan6775]

I can just tell you are awesome 👌

[@ninakadu3531]

Great video.. I have a different data type. Suppose in your example if app field is repetitive with different numerical values. Ex. Blend is repeated twice with values 5000 and 2000 in 2 separate columns ? please give some solution. Thanks 😊

[@bionicmarsh]

Wow...you are amazing

[@shabbirkanchwala-abwaab6263]

Gr8 One more Golden Brick to build my EXCELent Palace

[@LeilaGharani]

Soon we'll have a golden palace :)

[@safiparman415]

Thanks for the hacks. Very useful :-)

[@LeilaGharani]

Glad you like it!

[@machbauer132]

Thank you so much for that very useful trick. I'm so angry that Excel is still not capable to properly perform an inverse transpose operation, it's needed quite often!

[@walterbertelli]

Thank You !!

[@carrydy843]

Nice Videos I'm learning a lot. I have a question though, I really hope you can help me.. Most of these ways that are being shown works if and only cells & merge cells are equal, but in our form of reports this is impossible, is there a way to sort items of different cell & merge cell sizes. I really hope you can help me. Thanks in advance.

[@lisamccray6481]

Hi. It's me again. (Control T). Wow. But then again I guess that goes for every video. So I will just say that here in this one. I also really like the column chart video where the top of the bars are rounded. Insane. More more more!.

[@LeilaGharani]

Thanks again, Lisa!

[@mrpropilot007]

I love these video's!! Always helpful! This is not working for transposing data from one spreadsheet to another within the same workbook. I did the steps but I am not able to drag down the formula to fill the rest of the vertical cells from the horizontal cells. Any answer for this?

[@LeilaGharani]

with Office 365 or Excel 2021 you should be able to drag down. If you have an older version of Excel, this probably doesn't work because you need to use the control + shift method which fixes the area needed in advance.

[@agussetyowicaksono3927]

Masya Allah.......So smart .....thank you "LG"

[@prahladkumar7388]

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

[@thanadets.8689]

Thank you

[@pedramsamieyan7836]

Awesome!

[@luisquismondo]

My favorite: =OFFSET(Sheet1!$B$2,COLUMN(A1)-1,ROW(A1)-1)

[@ashoksahu9546]

Very Nice video

[@LeilaGharani]

Glad you like it!

[@rameshyadav1723]

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.

[@anil11996]

Nice trick

[@LeilaGharani]

Glad you like it.

[@ansar311]

I have data of 2 columns 1st columns are heading and 2nd column is value. Suppose I have 50 records how do I have it in a table showing only one heading and values downwards. Example 1st column contains name, bank ref, amount, payment details. Like this I have vertical 50 records in excel. I need a report with unique headings on the rows and values below the headings. Hope I have asked correctly. Finally your videos and explanation is fantastic.🤗🤗

[@venkatcool5873]

Superb

[@AjayAnandXLnCAD]

awesome

[@1877Pegasus]

Owesone!!!

[@apodim8874]

Cool hack! I’m wondering when you are showing us power query videos :)

[@LeilaGharani]

It's still on my list. Don't give up :)

[@anil11996]

Thanks

[@ademoluodujoko4842]

Hello there, Can I sort with the third method? From largest to smallest? I can’t sort a transpose sheet, how do I sort transpose data?

[@hosseinhosseinpoor4845]

thanks...

[@rahulbhujbal3663]

U r 1 in million

[@yashchawla8360]

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?

[@borhanvic]

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 .

[@LeilaGharani]

You can find my favorite books and my own courses on my website.

[@borhanvic]

@@LeilaGharani ok thank you soo much, I will check your website

[@ganesh5041]

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

[@Suhaspro]

4:18 loved it

[@LeilaGharani]

Thanks. Custom formatting is a great feature. I will have a more detailed video coming on this soon....

[@artyafridi]

THANKS

[@chamindabasnayake4844]

Great!!!

[@daveatkinson7549]

Hi Leila, love your videos. Here's an interesting one. I have a Y by X table of values that I want to convert to 2 columns. So instead of being 8 rows and 12 columns, it becomes 72 rows and 3 columns (the first 2 columns repeat for each row). Not sure if Excel can do this, or if I'm going to have to throw in the towel and commit to learning VBA. 124 Dave Joe Leila Nancy 134 Peter Angela Deirdre Donald Becomes: 124 Dave Joe 124 Dave Leila 124 Dave Nancy 134 Peter Angela 134 Peter Deirdre 134 Peter Donald

[@LeilaGharani]

The easiest way to do this is with Power Query. You can pivot the data. I cover this in my Power Query course but I've made a note to make a video for KZbin as well.

[@daveatkinson7549]

@@LeilaGharani Thank you. I've only watched your Excel videos. I'll take a look at the Power Query list. Is there a specific video that explains this? Thank you for replying. I regularly watch these and ruminate on them while falling asleep - where I do my best clear thinking!

[@timothymusole3114]

Thank u

[@doublekkinc]

@leila i tried this, but i cant get it to work properly when i am pulling from another sheet..... can you advise?

[@ninamactas6878]

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

[@LeilaGharani]

Glad you like it Nina. Do you mean something like this: kzbin.info/www/bejne/r4q5oJ6hZbOXn6s

[@rakhijain9516]

Hey Leila, When Im trying to replace the "lg" letter with "=". Its not recognising "lg" Could you tell me how to fix that?

[@anonperson9781]

Hi Leila enjoyed this video. Could you help me? After transposing the info, can the original orientation be deleted without losing the new transpose info? I can' seem to do so.

[@LeilaGharani]

If you don't need the original anymore, you could just copy the new transposed data and paste as values.

[@anonperson9781]

@@LeilaGharani Thank you for the help and all of your sharing Excel knowledge with us.

[@kavitadaswani5547]

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

[@shakiraasfoor7599]

شكرا

[@andy28652]

If i have more than 100 row data then which method follow please suggest

[@VicentePerdomoGarzon]

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