This might be the most brilliant video ever posted on RUclips. If the Nobel Prize people had a prize for software use advice, you should get that Nobel prize.
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!
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.
You could also use the OFFSET formula: =OFFSET($A$4,(ROWS($A$1:A1)-1)*3+(COLUMNS($A$1:A1)-1),0) this would make it more automatic and easier to extend in case you have to get more data input
Great thanks. I was just going to try to work on something similar. Instead of ROWS($A$1:A1), how about simply ROW(A1)? Now you have =OFFSET($A$4,(ROW(A1)-1)*3+(COLUMN(A1)-1),0) which works out as the same thing but less typing
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.
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.
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!
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.
I never thought about this way, but this is those sort of things that surprise people for its simplicity and learnability... It is not about using a tool.. it is about using your brain....
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.
As always with your examples and tutorials; straight to the point in an easy to understand way and quite simply (no doubt) the best solution. As I´ve said before, never a dull moment watching your tutorials. You rock! :)
I stumbled across your RUclips 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!
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 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.
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!
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!
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.....
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 !!!!
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!
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.
I am taking your courses on udemy but beside course, i dont know what you eat in your breakfast, I learn something new everytime i visit course and youtube video as well. Thanks teacher
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!!
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 kinda like the indirect formula for something like this. There are three variables so it would be something like =indirect("A"&3×c4+1) where c4 would be the iteration number, 1 would be the variables after the original and 3 would be the number of new columns being created.
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.
Very good trick. I actually have to unstack a large amount of data that looks exactly like this on a regular basis. I created a simple piece of vba code in order to do it, however on large data sets it takes a about 10 seconds to complete. Using this nice and easy formula method would take no time at all!! That's for the video
This can also be done without the need to find and replace. Just build the formulas for the first 2 rows then select them both and drag to fill just as you did after adding the "lg". Same results and eliminates a step.
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?
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 RUclips. 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
Best excel tutor I have ever seen in my life...!! Thank you!!
Wow! Simplicity is the mother of beauty! This is an awesome trick
Yep !
Leila is one the most wonderful and gifted teacher of all times! It's so awesome to watch her videos!!!
This is not Excel, this is Magic! TY!
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!
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.
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 :)
You could also use the OFFSET formula:
=OFFSET($A$4,(ROWS($A$1:A1)-1)*3+(COLUMNS($A$1:A1)-1),0)
this would make it more automatic and easier to extend in case you have to get more data input
Super; thanks
Great thanks. I was just going to try to work on something similar.
Instead of ROWS($A$1:A1), how about simply ROW(A1)?
Now you have =OFFSET($A$4,(ROW(A1)-1)*3+(COLUMN(A1)-1),0) which works out as the same thing but less typing
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.
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.
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.
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.
Really mind blowing trick for stuck data converted to proper format
Thanks for sharing this video
No matter which topic you post on excell learning.it is always best and well received.
I'm glad to hear that.
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.
Saved my hours! Thanks a ton. Wish you good health and prosperity
I never thought about this way, but this is those sort of things that surprise people for its simplicity and learnability... It is not about using a tool.. it is about using your brain....
This blew my mind. What a great tip!
Every time! If I'm stuck, you come through. You're awesome. Thank you!!
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.
As always with your examples and tutorials; straight to the point in an easy to understand way and quite simply (no doubt) the best solution. As I´ve said before, never a dull moment watching your tutorials. You rock! :)
One of the simplest and smartest things I’ve seen in excel. Excellent
Glad it was helpful!
I stumbled across your RUclips 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.
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!
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.
Clear, clever solution to a frequently difficult and time consuming problem. Awesome.
Glad you liked it!
The LG idea replecment , just saved me a ton of time ... thank you veeery much :)
Wow.. Very simple, smart, and non-obvious workaround
Glad you like it Yousif.
Thanks leila, i found this in 2020, aftert these years. U make my life easier
Glad I could help!
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 :)
Girl, you rock. Thanks for all you do.
O.M.G. The simplicity of this is pure genius!
i've been trying to figure this out for the last 2 days to solve a problem i had and boom! thank you
Glad I could help, David!
A great and very smart way of doing it. Thanks, you are a treasure.
unbelievable shortcut you created Ms Leila..... Hats off !!!
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!
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.
To me Leila is excellent teacher, Thank you for many things i learnt from you
Thank you! 😃
Thank you dear Leila, it's so simple
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!
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 :)
Your channel is a life saver!
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!
Smart and easy method to follow. Thank you very much!
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!
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!
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 :)
One of the best shortcuts I have seen!
Glad you like it, Steven.
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.
Genius. Yet so simple.
Sorcery!!! You continue to amaze Leila!
Glad you like it Daniel.
Genius! Thanks for all your excel help
You are my favorite teacher for excel......
Thank you! That’s an honor!
I am taking your courses on udemy but beside course, i dont know what you eat in your breakfast, I learn something new everytime i visit course and youtube video as well. Thanks teacher
You're very welcome. I'm glad to hear that!
Wooow! How simple and how Fan-tas-tic! at the same time! Amazing job!
Thank you! 😊
That's extremely useful... I thank you
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.
You just saved me, probably, hours of work. I absolutely LOVE Excel for situations like this!
Great to hear!
Just what i need... Very simple... Thanks LG
So simple. Thank you.
I love Excel, but after watching your videos i love it even more.
This is Gold! You've saved me a whole load of time 👏👏👏
Fantastic!
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.!!
Absolutely brilliant! This is exactly what I was looking for.
Glad it was helpful!
Leila your are really smart. Your simple solutions to very hard issues are really amazing.
Thanks a lot..you saved my tons of hours and efforts..I will be indebted to you.
Amazing ! I would normally just use power query but this is even quicker
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!
I use excel a lot specially dashboard creation and I really find your videos very helpful
Thank you
Simply BRILLIANT!
Wow! This is the first video I have seen recently where there are no dislikes. Kudos.
Thank you! The dislikes have arrived. Not everyone likes the lazy approach :)
Simply brilliant. Thank you.
Love your hacks. Love your instructional style!
I'm glad you like it!
One of the finest things I've leaned today.
Answering the questions i have when others are answering the opposite from the same search query. Thank you.
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!!
Brilliant. Saved me a ton of time.
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.
I kinda like the indirect formula for something like this. There are three variables so it would be something like =indirect("A"&3×c4+1) where c4 would be the iteration number, 1 would be the variables after the original and 3 would be the number of new columns being created.
So Easy to follow all your course
This is ridiculously easy and useful. Best video I have seen in RUclips
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'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!
Thank you I have been looking for solving this problem for a long time. Your videos are amazing
Very nice trick!!! it's only about being creative and using the right tools.
Exactly!
thank you leila like this simple method .
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 good trick.
I actually have to unstack a large amount of data that looks exactly like this on a regular basis.
I created a simple piece of vba code in order to do it, however on large data sets it takes a about 10 seconds to complete.
Using this nice and easy formula method would take no time at all!! That's for the video
That's great! I'm glad you can make use of this :)
This can also be done without the need to find and replace. Just build the formulas for the first 2 rows then select them both and drag to fill just as you did after adding the "lg". Same results and eliminates a step.
Extremely useful. I actually had a database like this and I had no clue on how to proceed.
Thanks for the tips. Pls keep them coming.
Will definitely do Eva :)
Getting back into Excel from older version and I love this method. I usually write VBA macro.
Great to hear!
This is really helpful for me. Thanks a lot for this shortcut method
My pleasure. Glad it's helpful.
This was great! Thanks so much.
Perfect! Thanks daily I come across this kind of separation and since I am unaware I was doing all these days manually or via other formula..
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 :)
Now why didn’t I think of that! Thank you Leila! Loving this channel.
I'm glad to hear that!