This might be the most brilliant video ever posted on CZcams. If the Nobel Prize people had a prize for software use advice, you should get that Nobel prize.
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!
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.
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.
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.
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.
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.
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.
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.
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
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!
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.
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! :)
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!
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 !!!!
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.....
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 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 !!
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!!
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.
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?
I stumbled across your CZcams 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!
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
Ah of course! Thanks! I actually needed to do the opposite, go from multiple to one column. Can just do this trick in one column, and seems to work pretty well :)
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 CZcams. 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 !
This is not Excel, this is Magic! TY!
Leila is one the most wonderful and gifted teacher of all times! It's so awesome to watch her videos!!!
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.
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.
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.
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 :)
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.
Really mind blowing trick for stuck data converted to proper format
Thanks for sharing this video
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.
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.
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.
Saved my hours! Thanks a ton. Wish you good health and prosperity
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.
This blew my mind. What a great tip!
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
No matter which topic you post on excell learning.it is always best and well received.
I'm glad to hear that.
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.
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.
unbelievable shortcut you created Ms Leila..... Hats off !!!
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! :)
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 :)
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!
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.
A great and very smart way of doing it. Thanks, you are a treasure.
The LG idea replecment , just saved me a ton of time ... thank you veeery much :)
Every time! If I'm stuck, you come through. You're awesome. Thank you!!
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!
Welcome to the club. I’m also learning new stuff every day :) let me count how may years I’ve had ---18 years!
Amazing ! I would normally just use power query but this is even quicker
So simple. Thank you.
I love Excel, but after watching your videos i love it even more.
O.M.G. The simplicity of this is pure genius!
Thanks a lot..you saved my tons of hours and efforts..I will be indebted to you.
Girl, you rock. Thanks for all you do.
Clear, clever solution to a frequently difficult and time consuming problem. Awesome.
Glad you liked it!
Smart and easy method to follow. Thank you very much!
Thank you dear Leila, it's so simple
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!
Your channel is a life saver!
I use excel a lot specially dashboard creation and I really find your videos very helpful
Thank you
To me Leila is excellent teacher, Thank you for many things i learnt from you
Thank you! 😃
One of the simplest and smartest things I’ve seen in excel. Excellent
Glad it was helpful!
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.
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!
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.!!
You are my favorite teacher for excel......
Thank you! That’s an honor!
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 :)
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!!
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 :)
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!
Genius! Thanks for all your excel help
Brilliant. Saved me a ton of time.
Love your hacks. Love your instructional style!
I'm glad you like it!
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 :)
Just what i need... Very simple... Thanks LG
This is Gold! You've saved me a whole load of time 👏👏👏
Fantastic!
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 :)
Really brilliant, like said before. Astonished.
I stumbled across your CZcams 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.
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!
Simply BRILLIANT!
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 :)
Simply brilliant. Thank you.
Leila your are really smart. Your simple solutions to very hard issues are really amazing.
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.
This is ridiculously easy and useful. Best video I have seen in CZcams
Sorcery!!! You continue to amaze Leila!
Glad you like it Daniel.
Genius. Yet so simple.
That's extremely useful... I thank you
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!
This was great! Thanks so much.
Wow this is mind-blowing ! Thank you so much
Very nice trick!!! it's only about being creative and using the right tools.
Exactly!
This is brilliant. Helped me just now, thanks a bunch :)
Wooow! How simple and how Fan-tas-tic! at the same time! Amazing job!
Thank you! 😊
Absolutely brilliant! This is exactly what I was looking for.
Glad it was helpful!
One of the best shortcuts I have seen!
Glad you like it, Steven.
You are top! your teachings way is such easy to follow.
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..
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!
Getting back into Excel from older version and I love this method. I usually write VBA macro.
Great to hear!
You are a Clarity Queen, this is really handy and useful. Nice trick.. Thumbs up!
Extremely useful. I actually had a database like this and I had no clue on how to proceed.
This is the best video ever, thank you so much
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.
OMG it's BRILLIANT!!! Absolutely brilliant!!! Thanks for sharing, Leila.
Thank you Malina :)
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 :)
Now why didn’t I think of that! Thank you Leila! Loving this channel.
I'm glad to hear that!
So Easy to follow all your course
Ah of course! Thanks!
I actually needed to do the opposite, go from multiple to one column. Can just do this trick in one column, and seems to work pretty well :)