This might be the most brilliant video ever posted on UA-cam. If the Nobel Prize people had a prize for software use advice, you should get that Nobel prize.
Wow. I have been using Excel since 1997, and am pretty proficient. But I have never seen this *most excellent* hack! Kudos to you! I have subscribed to your vids because your explanations are spot on, simple and your voice is measured and quite pleasant. Yay Leila!
Brilliant! Another, similar trick I learned was to disguise formulae if you want to copy a large block of cells with formulae that don't have absolute references, but where you *don't* want the references to change when you copy. Select the range, do +H to Find "=" and Replace with "%%"; copy the range to where you want it, then reverse the Find and Replace to put them back to formulae preceded by "=". I need to do this so often, that I've put the two stages into macros, triggered by buttons on my customised ribbon.
I needed to put a single column of 300 values into a 10x30 grid. Googled and googled and tried to figure out a simple way to do it. So glad I found this, what a great (and simple) trick! Thanks!
Hi,Im working in a call center where I use excel a lot.I just want to let you know that your tutorials are very helpful and easy to understand.Thank you so much for your videos.
This is immense, and do simple. It was taking me hours to manually move cells around, your tips had me finished inside of 10 minutes. Thank you thank you thank you!
Thank You. I have my tricks to unstack, but what you show here will work for so many other applications. I totally overlooked the custom formatting to hide 0's. That tip alone is worth a whole lot for so many things I do. I owe you a drink if you're ever in the San Diego area.
What an awesome way to unstack data! I got your notification on this when I was about 10% through unstacking over 1000 rows of imported text. I saw the other ways but I was spending too much time configuring and not getting work done. Your way ISN'T the lazy way but the most productive way that I've seen. 1574 rows done in under a minute. Thank you, thank you and thank you.
Excellent. Every video you make us to learn, learn & learn. God only knows your brilliance. Thank God. We are very lucky to get a teacher like you. Saluatations to my beloved Teacher.
This EXTREMELY valuable for me! I have data pulls in the tens of thousands that this fixes an issue we have. What would be further value that I can figure out is; how to do the same thing but with the twist that the stacked data can have variable amounts of entry, not just the set of 3 like you have in the example. So Record A might be 3, Record B might be 6, Record C might be 2, etc, etc.
This is brilliant. I had exactly this problem, my usual spreadsheet jockey was running an errand out of the office, and I needed this done asap. Found you with Google and said to myself: "Really? That simple?" I tried it and BAM! A total of 3,976 rows (copied and pasted from a table on a website) transformed into a 497x8 array just like that. G.E.N.I.U.S.
Ha....ha... I remember when I faced this problem, when working as accountant. And how happy am I to found unpivot features in power query. And now you show the very simpla-easy way without Power Query. Nice.....
Cool trick! I have to do this quite often but have always used =index(array, column(A1)+(row(A1)-1)*3). Column and row can be switched whether you're destacking vertically or horizontally (and 3 would change depending on the group size). At first I had to always think it out but now it is so ingrained in my head it comes out somewhat naturally.
When you used your initials and cell reference I knew where this was going! That was sooooo crafty!! I literally was putting together dynamic chart for a dashboard last night and could have used this tip lol. You're the best Mrs Gharani !!!!
Oh.My.Gosh! You are my hero. I have used a few of your videos when i needed to learn something quickly. I discovered this one by accident and I can't wait to watch them all. Thank you for all of your hard work.
So glad I found your channel, Leila. As an IT professional constantly working in excel, your videos are extremely useful. Question: what if your stack of data has no clear pattern? In your example, you had a new title every 3 rows from the previous title. What if you're working in a sheet, where the title appears randomly? Thanks in advance!
Leila, This is perfect timing! I was just discussing FILL/SERIES and FIND/REPLACE with an employee this morning and how powerful they both are...now to combine them, even more so. Thank you!
Very nice! Wow, I'm still learning simple tricks after 20 years of Excel. Tried and it works with other patterns as well (restacking, rearranging data). Thank You!
I've only recently come across your videos in the last couple of weeks and abolsutely love them. Your succinct and easy to following teaching style is great. Thank you.
I did use a few easy tricks, but this one is away wave more easy and efficient than anything I have ever used, I watch your videos everytime to get amazed and definitely improve my teaching skills !!
I stumbled across your UA-cam channel by accident and I have to say that I've learned more in the last week then from all of the Excel courses I've attended. Great tips and tricks!! Well done!
Hi Leila - as this is my first ever comment, I first just want to say how impressed I have been with the quality of these videos. Not only do you explain things clearly and concisely, you always choose practical and innovative ways to solve real-life problems. I wanted to get your thoughts on an alternate approach that I thought you might try for this example to see if you saw any downside to it. Using your data, I propose starting with the following formula in cell C3: =OFFSET($A$4,3*(ROW(D4)-ROW($D$4))+COLUMN(D4)-COLUMN($D$4),0) You can then drag this formula down as far as you need. As with your example, users may want to format zeroes not to display within the relevant area. You could make this even more dynamic and generic by parameterizing the 3 after the first comma, which represents the number of columns of output data you want to produce. What do you think?
Hi Leila.. thanks for the great lg-replacement trick for unstacking data. Forgive me if I call it the we-replacement trick.. haha!! Your tips are always so valuable and useful.. top notch! Thumbs up!!
I always like all your videos, you make things so simple .. wish i had found your channel earlier, i could have learned a lot by now.. I am getting addicted to this channel now.
THANK YOU!!!!! Leila, I can't tell you how many times I have tried to figure this out!!! WOW!! I am already thinking of other ways to use this trick. You are the Best. P.S You and Mike Gavin need to have an Excel battle like he does with the Excel Guy. You two would make a great team!!
Grab the file I used in the video from here 👉 pages.xelplus.com/unstack-file
This might be the most brilliant video ever posted on UA-cam. If the Nobel Prize people had a prize for software use advice, you should get that Nobel prize.
One of the simplest and smartest things I’ve seen in excel. Wow
This is not Excel, this is Magic! TY!
unbelievable shortcut you created Ms Leila..... Hats off !!!
Wow. I have been using Excel since 1997, and am pretty proficient. But I have never seen this *most excellent* hack! Kudos to you! I have subscribed to your vids because your explanations are spot on, simple and your voice is measured and quite pleasant. Yay Leila!
Oh. and I will use YOUR initials too :)
Brilliant! Another, similar trick I learned was to disguise formulae if you want to copy a large block of cells with formulae that don't have absolute references, but where you *don't* want the references to change when you copy. Select the range, do +H to Find "=" and Replace with "%%"; copy the range to where you want it, then reverse the Find and Replace to put them back to formulae preceded by "=". I need to do this so often, that I've put the two stages into macros, triggered by buttons on my customised ribbon.
Best excel tutor I have ever seen in my life...!! Thank you!!
O.M.G. The simplicity of this is pure genius!
Leila is one the most wonderful and gifted teacher of all times! It's so awesome to watch her videos!!!
Every time! If I'm stuck, you come through. You're awesome. Thank you!!
Wow! Simplicity is the mother of beauty! This is an awesome trick
Yep !
Clear, clever solution to a frequently difficult and time consuming problem. Awesome.
Glad you liked it!
Thank you dear Leila, it's so simple
Leila your are really smart. Your simple solutions to very hard issues are really amazing.
I needed to put a single column of 300 values into a 10x30 grid. Googled and googled and tried to figure out a simple way to do it. So glad I found this, what a great (and simple) trick! Thanks!
Hi,Im working in a call center where I use excel a lot.I just want to let you know that your tutorials are very helpful and easy to understand.Thank you so much for your videos.
I'm very happy to hear that Richard!
Kudos for making scammer’s jobs easier.
One of the simplest and smartest things I’ve seen in excel. Excellent
Glad it was helpful!
Saved my hours! Thanks a ton. Wish you good health and prosperity
This is immense, and do simple. It was taking me hours to manually move cells around, your tips had me finished inside of 10 minutes.
Thank you thank you thank you!
Fantastic! Glad it helped.
Wow.. Very simple, smart, and non-obvious workaround
Glad you like it Yousif.
Thank You. I have my tricks to unstack, but what you show here will work for so many other applications. I totally overlooked the custom formatting to hide 0's. That tip alone is worth a whole lot for so many things I do. I owe you a drink if you're ever in the San Diego area.
That's great! I'm glad to hear that. I've been to San Diego ....beautiful place...but that was 20 years ago :)
What an awesome way to unstack data! I got your notification on this when I was about 10% through unstacking over 1000 rows of imported text. I saw the other ways but I was spending too much time configuring and not getting work done. Your way ISN'T the lazy way but the most productive way that I've seen. 1574 rows done in under a minute. Thank you, thank you and thank you.
That's GREAT! I'm really happy to hear that! I'm glad you saw this at 10% and not right after 100% :)
So true. That wouldn't have been a good thing. Thanks again.
Thanks, Leila. You're a time saver.
No matter which topic you post on excell learning.it is always best and well received.
I'm glad to hear that.
Excellent. Every video you make us to learn, learn & learn. God only knows your brilliance. Thank God. We are very lucky to get a teacher like you. Saluatations to my beloved Teacher.
Thank you Gopala for always commenting and liking the videos. I am very blessed to have a wonderful audience. Thank you!
Such a clever way to unstack. So simple but never came into my mind. Thanks for the trick Leila.. you are great.
You're very welcome Tamrakar :)
Smart and easy method to follow. Thank you very much!
I am speechless. I know almost same as you know. I also have good logic. But you are far ahead in terms of great ideas. Hats off.
I'm glad you find the tutorial useful.
This EXTREMELY valuable for me! I have data pulls in the tens of thousands that this fixes an issue we have.
What would be further value that I can figure out is; how to do the same thing but with the twist that the stacked data can have variable amounts of entry, not just the set of 3 like you have in the example. So Record A might be 3, Record B might be 6, Record C might be 2, etc, etc.
Thanks a lot..you saved my tons of hours and efforts..I will be indebted to you.
Wow, very simple and very clever. Thanks, Leila.
You're very welcome :)
This is brilliant. I had exactly this problem, my usual spreadsheet jockey was running an errand out of the office, and I needed this done asap. Found you with Google and said to myself: "Really? That simple?" I tried it and BAM! A total of 3,976 rows (copied and pasted from a table on a website) transformed into a 497x8 array just like that. G.E.N.I.U.S.
Excellent solution. I have not thought of the replacement technique as a workable solution. It is an eye opener. Many many thanks
You’re very welcome :)
You are my favorite teacher for excel......
Thank you! That’s an honor!
Wow!! I'm just getting addicted to your tutorial. May Allah give you the better return and solve your problems like you do for us!
I'm happy to hear the tutorials are helpful!
Ha....ha... I remember when I faced this problem, when working as accountant. And how happy am I to found unpivot features in power query. And now you show the very simpla-easy way without Power Query. Nice.....
:) Sorry this came a bit late....but unpivoting with Power Query is great too.
One of the finest things I've leaned today.
To me Leila is excellent teacher, Thank you for many things i learnt from you
Thank you! 😃
Cool trick! I have to do this quite often but have always used =index(array, column(A1)+(row(A1)-1)*3). Column and row can be switched whether you're destacking vertically or horizontally (and 3 would change depending on the group size). At first I had to always think it out but now it is so ingrained in my head it comes out somewhat naturally.
A great and very smart way of doing it. Thanks, you are a treasure.
When you used your initials and cell reference I knew where this was going! That was sooooo crafty!! I literally was putting together dynamic chart for a dashboard last night and could have used this tip lol. You're the best Mrs Gharani !!!!
I'm so glad!
Oh.My.Gosh! You are my hero. I have used a few of your videos when i needed to learn something quickly. I discovered this one by accident and I can't wait to watch them all. Thank you for all of your hard work.
I always come back to watch your video to learn new tricks. Happy new year to you, from Malaysia.
Thank you for a great tip. I bump into the need to unstack periodically - sometimes with much larger data sets. This way is the best!
Thank you! Glad you can make use of this trick.
Really mind blowing trick for stuck data converted to proper format
Thanks for sharing this video
What a smart way to unstack,thanks Leila
So glad I found your channel, Leila. As an IT professional constantly working in excel, your videos are extremely useful. Question: what if your stack of data has no clear pattern? In your example, you had a new title every 3 rows from the previous title. What if you're working in a sheet, where the title appears randomly? Thanks in advance!
Collapse the rows until it shows in a pattern.
Brilliant. Saved me a ton of time.
The LG idea replecment , just saved me a ton of time ... thank you veeery much :)
Thanks leila, i found this in 2020, aftert these years. U make my life easier
Glad I could help!
Leila,
This is perfect timing! I was just discussing FILL/SERIES and FIND/REPLACE with an employee this morning and how powerful they both are...now to combine them, even more so. Thank you!
Genius! Thanks for all your excel help
Just what i need... Very simple... Thanks LG
Very nice! Wow, I'm still learning simple tricks after 20 years of Excel.
Tried and it works with other patterns as well (restacking, rearranging data).
Thank You!
Welcome to the club. I’m also learning new stuff every day :) let me count how may years I’ve had ---18 years!
Your channel is a life saver!
Thank you! I was working through it knowing there had to be a better way. I’ll take a quick hack over M code any day!
Our pleasure!
I use excel a lot specially dashboard creation and I really find your videos very helpful
Thank you
This was great! Thanks so much.
thank you ! very helpful method
Great trick Leila, I run into this all the time and this is a really fast method to properly format the data. Thanks for another great video!
Thank you Robert. I'm glad you can make of use of this :)
You are a Clarity Queen, this is really handy and useful. Nice trick.. Thumbs up!
OMG it's BRILLIANT!!! Absolutely brilliant!!! Thanks for sharing, Leila.
Thank you Malina :)
Really brilliant, like said before. Astonished.
Simply brilliant. Thank you.
This blew my mind. What a great tip!
I'm so glad that I saw this video, it saved me hours of work. Many thanks Leila
My pleasure Karim. I'm glad the tutorial is helpful.
Very nice trick!!! it's only about being creative and using the right tools.
Exactly!
Thank you I have been looking for solving this problem for a long time. Your videos are amazing
A BRILLIANT resolution to my exact problem. I am now a new subscriber and I will be back soon to probe your brain some more. Thanks so much.!!
Your tricks are helping me a lot...
Thanks for this..
My pleasure. I'm glad the video is helpful.
I've only recently come across your videos in the last couple of weeks and abolsutely love them. Your succinct and easy to following teaching style is great. Thank you.
Awesome, thank you Fred!
I did use a few easy tricks, but this one is away wave more easy and efficient than anything I have ever used, I watch your videos everytime to get amazed and definitely improve my teaching skills !!
I'm glad to hear you find the tutorial helpful.
OMGoodness! I would've used offsets and rows and columns to do achieve this, but this trick is badass!!! I'm a fan. Subscribed!
Glad you like it. It's great to have you here :)
I don’t know how to thank you Leila, this is amazing trick made my life easy, 🌹🌹🌹
Excellent! Thank you again. You are brilliant and totally enchanting!
One of the best shortcuts I have seen!
Glad you like it, Steven.
Getting back into Excel from older version and I love this method. I usually write VBA macro.
Great to hear!
Simply BRILLIANT!
I stumbled across your UA-cam channel by accident and I have to say that I've learned more in the last week then from all of the Excel courses I've attended. Great tips and tricks!! Well done!
Thanks for the kind feedback James. I'm happy the tutorials are useful for you.
Girl, you rock. Thanks for all you do.
Hi Leila - as this is my first ever comment, I first just want to say how impressed I have been with the quality of these videos. Not only do you explain things clearly and concisely, you always choose practical and innovative ways to solve real-life problems. I wanted to get your thoughts on an alternate approach that I thought you might try for this example to see if you saw any downside to it.
Using your data, I propose starting with the following formula in cell C3:
=OFFSET($A$4,3*(ROW(D4)-ROW($D$4))+COLUMN(D4)-COLUMN($D$4),0)
You can then drag this formula down as far as you need. As with your example, users may want to format zeroes not to display within the relevant area. You could make this even more dynamic and generic by parameterizing the 3 after the first comma, which represents the number of columns of output data you want to produce.
What do you think?
Thank you David for your first wonderful comment. Your OFFSET method looks great! Love it! Thanks for sharing :)
thank you leila like this simple method .
Thank you! You are my new favourite channel to come to for excel challenges. Great video - clear, concise, and excellent screen presentation!
Thanks for the kind feedback Catherine. Glad you like it.
Wow this is mind-blowing ! Thank you so much
this is really great technique
I was struggling with this and was so frustrated, thinking "how can this NOT be so easy to do?". Well, now it is. Thank you so much! :)
Glad I could help Edmundo!
You just saved me, probably, hours of work. I absolutely LOVE Excel for situations like this!
Great to hear!
Absolutely brilliant! This is exactly what I was looking for.
Glad it was helpful!
Excellent idea! Thank You :)
You are top! your teachings way is such easy to follow.
Sorcery!!! You continue to amaze Leila!
Glad you like it Daniel.
This is ridiculously easy and useful. Best video I have seen in UA-cam
Hi Leila.. thanks for the great lg-replacement trick for unstacking data. Forgive me if I call it the we-replacement trick.. haha!! Your tips are always so valuable and useful.. top notch! Thumbs up!!
Thank you Wayne. The we-replacement actually sounds cooler :)
Thx :))
This is Gold! You've saved me a whole load of time 👏👏👏
Fantastic!
This is really helpful for me. Thanks a lot for this shortcut method
My pleasure. Glad it's helpful.
I always like all your videos, you make things so simple .. wish i had found your channel earlier, i could have learned a lot by now.. I am getting addicted to this channel now.
That's great to hear! Good to have you here.
THANK YOU!!!!! Leila, I can't tell you how many times I have tried to figure this out!!! WOW!! I am already thinking of other ways to use this trick. You are the Best.
P.S You and Mike Gavin need to have an Excel battle like he does with the Excel Guy. You two would make a great team!!
really smart function!!! thanks for sharing:) I enrolled your lesson already!
Obviously... Very useful shortcut method. Thank you very much
You're very welcome. Glad it's useful.