How to Change HORIZONTAL Data to VERTICAL in Excel (NO Transpose Function needed)
HTML-код
- Опубликовано: 14 окт 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
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....
🔍 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.co...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩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
![CHILE vs. BRASIL [1-2] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 9](http://i.ytimg.com/vi/5ObJt_71AYc/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/transpose-horizontal-file
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.
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.
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!
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.
Always the quickest and best solutions.
Thank you Leila. Your tricks are amazing. Glad to learn them.
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
Great to hear!
Great video...There is a super hack of transposing:
=Offset($A$2,column(A1)-1,row(A1)-1)
oh Thank you... just finished my job easily with this comment!
anyway thanks to this video tutorial also'
Thank you! spent 4 hours and this comment solved my requirement
How does this even work?
it was very helpful thank you very much
Good one, Leila! I must be so lucky to find this on time! Thanks
I'm glad you found it on time!
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 😊
You're very welcome. I'm glad it was helpful.
Thanks Leila and Bob; wonderful video; R1C1 is an amazing trick and very easy to follow for anyone. Love you guys, Cheers !
You're very welcome Sachin. Agree - it's a nice change to the usual formulas :)
Oh my god (or should I say “goddess “?).
That was simply mind blowing!!!
Thank you! and thanks to Bob for this trick :)
Exactly I was thinking like this.
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...)
Thanks Mike. R1C1 also sounds cooler than A1 :)
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
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?
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.
I love your lessons miss. Greetings from México
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. 🙂
Thanks a ton Leila, you and your tricks are amazing..you made my day!❤
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.🤗🤗
Wonderful followup, thanks Leila.
You're very welcome.
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 ?
You're the Excel Queen!
Superb hack Leila , amazing as usual, keep it up
I would wish to give a love for the hacks and tricks
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 .
You can find my favorite books and my own courses on my website.
@@LeilaGharani ok thank you soo much, I will check your website
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.
Oh my gosh, this is such a lifesaver! Thank you 💛💛💛 ^Chelsea
Glad it's helpful, Chelsea!
Nice trick very usefull in excel world
incredibly simple. Thank you.
Excelent tutorial! Very good and time saving!
Excellent video.
Great Video. thanks for Guidance
Amazing trick. Thanks Leila.
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 😊
My favorite: =OFFSET(Sheet1!$B$2,COLUMN(A1)-1,ROW(A1)-1)
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!.
Thanks again, Lisa!
As i said earlier, You are Magician
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!
Gr8
One more Golden Brick to build my EXCELent Palace
Soon we'll have a golden palace :)
Thanks, Leila. It's useful as always.
I'm glad you like it.
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...
Awesome! THANK YOU! :)
Great video Leila!
Slick trick. Let's see that in VBA!
Thanks for this awesome solution.
You're very welcome.
thanks Ms. Leila you are the best
That's very kind Ismail.
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
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 RUclips as well.
@@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!
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.
If you don't need the original anymore, you could just copy the new transposed data and paste as values.
@@LeilaGharani Thank you for the help and all of your sharing Excel knowledge with us.
thank for the hack explanation!
You're very welcome!
Wonderful great work I have no word for u thank you very much
Mind Blowing....you are unbelievable :)
You are the Best Leila !!! Thanks
Thanks, Leila!
Cool hack!
I’m wondering when you are showing us power query videos :)
It's still on my list. Don't give up :)
You are amazing!!! thank you very much!!
You're very welcome!
You are amazing!!
Hi there,
Can you share a video where you can teach us how to take printout of bigger excel sheets?
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.
Masya Allah.......So smart .....thank you "LG"
Very good! Thanks.
You’re very welcome Luis.
Cool hack! Thanks Leila : )
You're welcome Yulin :)
Thanks for the hacks. Very useful :-)
Glad you like it!
So smart like usually
awesome video!!
Can we use this hack, if my horizontal dataset comes from another work sheet? Could you show us how to do it if its doable?
Hi! I just figured this out.
So like for her example, instead or simply replacing "lg" with an = sign, replace "lg" with ='Name of your Sheet'!
and it works! 👍
BTW thank you Leila for being a great teacher!
Awesome 👏🏻👏🏻
4:18 loved it
Thanks. Custom formatting is a great feature. I will have a more detailed video coming on this soon....
I love this video but how do you use that same formula for horizontal . i cannot get the column to change in my formulas
Glad you like it Nina. Do you mean something like this: ruclips.net/video/yYVokk0NdiI/видео.html
Well done Leila
Superb, Amazing, excellent
Thank you Mam
Wow. You’re Gangster. Love it
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?
Yes sure. You can also use the Transpose function which I show in the previous video. Whichever method fits best for you and the data. This was just to show another way of getting it done :)
I had the same idea as Jeff which I show in this example that can transpose any size horizontal data block located at any source location to any target location. See it at wjhladik.github.io/Transpose%20Example.xlsx . The generic formula uses cell names to denote the starting cell of the "source" and the starting cell of the "target" destination. It's basically =index($r:$r,1,c) where r is the row in the source and c is the column in the source. It becomes a little scary incrementing them appropriately but it's a simple paste. Had to use indirect() to get the row of the source right.
=INDEX(INDIRECT("$"&ROW(source)+COLUMN(B7)-COLUMN(target)&":$"&ROW(source)+COLUMN(B7)-COLUMN(target)),1,COLUMN(source)+ROW(B7)-ROW(target))
It'll make more sense if you download the example.
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?
Like always you are the best! Regards!!!
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.
If you can send us a sample data set with the result you expect to our email address, I would be up for the challenge and can add this to our video list. If you do send an email, please title it "RUclips DSUM challenge".
@leila i tried this, but i cant get it to work properly when i am pulling from another sheet..... can you advise?
I can just tell you are awesome 👌
conteúdo relevante e muito didático.
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?
Genius as usual ;)
Thank You !!
Wow...you are amazing
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??
Hey Leila, When Im trying to replace the "lg" letter with "=". Its not recognising "lg" Could you tell me how to fix that?
Very Nice video
Glad you like it!
Magnifique ! So usefull
Awesome!
awesome
great video.. how about from vertical to horizontal?
I got you covered Frankie :) ruclips.net/video/yYVokk0NdiI/видео.html
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
Not sure why it would do this. Have you tried text to columns? ruclips.net/video/O-PMmN5CkNo/видео.html
Thank you
Nice trick
Glad you like it.
Superb
One thumb up there☝️
and another one 👍 here
Thank you and thank you :)
If i have more than 100 row data then which method follow please suggest
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)
Yes sure. Those are the two other methods I cover in the previous video. The only issue with paste special though is that it’s not dynamic.
Thanks ....I realised that later. Brilliant videos....keep up the good work! 👍🏻
I always liked R2D2 :-)
My favorite character!
this video saved my day :-) mine 2K th like by the way :-)