Quick Excel Trick to Unstack Data from one Column to Multiple Columns

[@LeilaGharani]

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

[@allisonbell8721]

This might be the most brilliant video ever posted on KZbin. If the Nobel Prize people had a prize for software use advice, you should get that Nobel prize.

[@dbookmarker9524]

This is not Excel, this is Magic! TY!

[@niyanaknowledgecave3277]

Best excel tutor I have ever seen in my life...!! Thank you!!

[@mauriciomolinahn]

Leila is one the most wonderful and gifted teacher of all times! It's so awesome to watch her videos!!!

[@debbiegaydos]

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!

[@iankr]

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.

[@thedr00]

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!

[@LeilaGharani]

Fantastic! Glad it helped.

[@anshuiway]

Really mind blowing trick for stuck data converted to proper format Thanks for sharing this video

[@rajanghadi1982]

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.

[@LeilaGharani]

I'm glad you find the tutorial useful.

[@raghuv7114]

No matter which topic you post on excell learning.it is always best and well received.

[@LeilaGharani]

I'm glad to hear that.

[@richardveloz923]

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.

[@LeilaGharani]

I'm very happy to hear that Richard!

[@01chippe]

Kudos for making scammer’s jobs easier.

[@abhisekmaheswari3707]

Saved my hours! Thanks a ton. Wish you good health and prosperity

[@lijikumarbalan6038]

unbelievable shortcut you created Ms Leila..... Hats off !!!

[@sjn7220]

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.

[@yousifmohammed4841]

Wow.. Very simple, smart, and non-obvious workaround

[@LeilaGharani]

Glad you like it Yousif.

[@jamieeru3519]

Every time! If I'm stuck, you come through. You're awesome. Thank you!!

[@mattias5063]

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! :)

[@zedzpan]

This blew my mind. What a great tip!

[@amysutton8238]

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.

[@juliejoseph2486]

Girl, you rock. Thanks for all you do.

[@eks007]

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!

[@spiralingsphere3785]

Collapse the rows until it shows in a pattern.

[@ramamohanpathapati7694]

To me Leila is excellent teacher, Thank you for many things i learnt from you

[@LeilaGharani]

Thank you! 😃

[@babaktftf8925]

Thank you dear Leila, it's so simple

[@DerickPitcher]

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.

[@LeilaGharani]

That's GREAT! I'm really happy to hear that! I'm glad you saw this at 10% and not right after 100% :)

[@DerickPitcher]

So true. That wouldn't have been a good thing. Thanks again.

[@Fitimth]

The LG idea replecment , just saved me a ton of time ... thank you veeery much :)

[@tombic6373]

Clear, clever solution to a frequently difficult and time consuming problem. Awesome.

[@LeilaGharani]

Glad you liked it!

[@glennvd7622]

O.M.G. The simplicity of this is pure genius!

[@nairobi203]

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

[@1gopalakrishnarao]

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.

[@LeilaGharani]

Thank you Gopala for always commenting and liking the videos. I am very blessed to have a wonderful audience. Thank you!

[@nanookdan]

Sorcery!!! You continue to amaze Leila!

[@LeilaGharani]

Glad you like it Daniel.

[@sarfarajkureshi6569]

You are my favorite teacher for excel......

[@LeilaGharani]

Thank you! That’s an honor!

[@niuean22kid]

Amazing ! I would normally just use power query but this is even quicker

[@rymabenhimi1589]

Your channel is a life saver!

[@tawhidulislam3440]

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!

[@LeilaGharani]

I'm happy to hear the tutorials are helpful!

[@qblakesnappit]

Just what i need... Very simple... Thanks LG

[@mikoajkomorowski1290]

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

[@AbuTalha-eo7pr]

Super; thanks

[@BondiMacF]

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

[@shadrackawunyo7786]

Excellent solution. I have not thought of the replacement technique as a workable solution. It is an eye opener. Many many thanks

[@LeilaGharani]

You’re very welcome :)

[@Boudico]

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!

[@timotiusriady6298]

Thanks leila, i found this in 2020, aftert these years. U make my life easier

[@LeilaGharani]

Glad I could help!

[@mozgus79ify]

So simple. Thank you. I love Excel, but after watching your videos i love it even more.

[@spamdump4459]

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.

[@LeilaGharani]

That's great! I'm glad to hear that. I've been to San Diego ....beautiful place...but that was 20 years ago :)

[@rasoulkhoshravan5912]

Leila your are really smart. Your simple solutions to very hard issues are really amazing.

[@TamraShorts]

Such a clever way to unstack. So simple but never came into my mind. Thanks for the trick Leila.. you are great.

[@LeilaGharani]

You're very welcome Tamrakar :)

[@walieast7896]

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

[@LeilaGharani]

I'm so glad!

[@basilyang3862]

Genius! Thanks for all your excel help

[@joep9617]

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!

[@LeilaGharani]

Welcome to the club. I’m also learning new stuff every day :) let me count how may years I’ve had ---18 years!

[@MelMario]

Genius. Yet so simple.

[@iestynjones5796]

Simply brilliant. Thank you.

[@jamesdemaio5234]

I stumbled across your KZbin 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!

[@LeilaGharani]

Thanks for the kind feedback James. I'm happy the tutorials are useful for you.

[@mlflanker]

Wooow! How simple and how Fan-tas-tic! at the same time! Amazing job!

[@LeilaGharani]

Thank you! 😊

[@marwandeen]

I use excel a lot specially dashboard creation and I really find your videos very helpful Thank you

[@LobanRahman]

Love your hacks. Love your instructional style!

[@LeilaGharani]

I'm glad you like it!

[@aperkinsvt]

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!

[@LeilaGharani]

Thank you! Glad you can make use of this trick.

[@stevenarias717]

One of the best shortcuts I have seen!

[@LeilaGharani]

Glad you like it, Steven.

[@leroyguillot481]

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

[@musicsang4882]

Thanks a lot..you saved my tons of hours and efforts..I will be indebted to you.

[@fenderdaw]

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!

[@LeilaGharani]

Our pleasure!

[@joshuamanampiu6489]

Extremely useful. I actually had a database like this and I had no clue on how to proceed.

[@ZeratulX]

Very nice trick!!! it's only about being creative and using the right tools.

[@LeilaGharani]

Exactly!

[@superhiway]

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

[@vetman98]

Thank you I have been looking for solving this problem for a long time. Your videos are amazing

[@diegovinicius1987]

This is ridiculously easy and useful. Best video I have seen in KZbin

[@mahmoudbasheti3551]

You are a Clarity Queen, this is really handy and useful. Nice trick.. Thumbs up!

[@DavidPerez-bf4cp]

i've been trying to figure this out for the last 2 days to solve a problem i had and boom! thank you

[@LeilaGharani]

Glad I could help, David!

[@justsaiyen596]

Answering the questions i have when others are answering the opposite from the same search query. Thank you.

[@GhulamAli-ge8gi]

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

[@LeilaGharani]

You're very welcome. I'm glad to hear that!

[@russellhare2881]

Absolutely brilliant! This is exactly what I was looking for.

[@LeilaGharani]

Glad it was helpful!

[@cheriesmewengkang7004]

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

[@LeilaGharani]

:) Sorry this came a bit late....but unpivoting with Power Query is great too.

[@karimarafa]

I'm so glad that I saw this video, it saved me hours of work. Many thanks Leila

[@LeilaGharani]

My pleasure Karim. I'm glad the tutorial is helpful.

[@MalinaC]

OMG it's BRILLIANT!!! Absolutely brilliant!!! Thanks for sharing, Leila.

[@LeilaGharani]

Thank you Malina :)

[@graphguy]

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.

[@stanly462u]

One of the finest things I've leaned today.

[@anikther]

You just saved me, probably, hours of work. I absolutely LOVE Excel for situations like this!

[@LeilaGharani]

Great to hear!

[@gaultztaguinod9289]

OMGoodness! I would've used offsets and rows and columns to do achieve this, but this trick is badass!!! I'm a fan. Subscribed!

[@LeilaGharani]

Glad you like it. It's great to have you here :)

[@angus68655]

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.

[@LeilaGharani]

Awesome, thank you Fred!

[@bennettdejoya2]

Thanks, Leila. You're a time saver.

[@mohammadashiq9996]

thank you leila like this simple method .

[@heather7461]

Wow this is mind-blowing ! Thank you so much

[@lpanades]

Really brilliant, like said before. Astonished.

[@ToddBeck]

Brilliant. Saved me a ton of time.

[@mireillecantrell4638]

Getting back into Excel from older version and I love this method. I usually write VBA macro.

[@LeilaGharani]

Great to hear!

[@fuzz-head4800]

Offset formula does trick as well using plus 3 for rows

[@9410201852]

Its really simple.. Kudos!

[@joydeeppurkayastha8015]

What a smart way to unstack,thanks Leila

[@sanjayroy649]

Really Nice way to unstack data...without using Formula.👍👍👍

[@LeilaGharani]

Glad you like it Sanjay.

[@rameshm1145]

So Easy to follow all your course

[@willlewis5011]

This is the best video ever, thank you so much

[@ramya3928]

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

[@fayi85]

I don’t know how to thank you Leila, this is amazing trick made my life easy, 🌹🌹🌹

[@MrAbhishek1109]

Your tricks are helping me a lot... Thanks for this..

[@LeilaGharani]

My pleasure. I'm glad the video is helpful.

[@ABG_7]

This is brilliant. Helped me just now, thanks a bunch :)

[@bahirvm4437]

This is really helpful for me. Thanks a lot for this shortcut method

[@LeilaGharani]

My pleasure. Glad it's helpful.

[@robertflynn7416]

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!

[@LeilaGharani]

Thank you Robert. I'm glad you can make of use of this :)

[@shubhsjbp]

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

[@LeilaGharani]

I'm glad to hear you find the tutorial helpful.

[@TRX2015]

Now why didn’t I think of that! Thank you Leila! Loving this channel.

[@LeilaGharani]

I'm glad to hear that!

[@ajexcel]

You are Just awesome Leila Ma'm...this is really easiest way....loved it...👍😘

[@SandipBanerjee]

Excellent idea! Thank You :)

[@RaviPrakash-ez4mq]

Leila You inspire me period

[@animatorlomeli]

This is exactly what I was looking for. Problem solving at its finest. Leila you're the best.

[@LeilaGharani]

Glad to help Phillip.