Very nice ^^ For #8 I think it’s helpful to also mention that the Find, Format button, drop down allows you to select a specific cell to just pull the format from. It helps if you don’t know the exact color you want to duplicate.
Hi Chandoo, For #6: What I can do is just copy the 3 cell and go to Street cell and do Alt e+s+e(transpose) then enter. Stacked will be done in few seconds.
0:00 - Introduction 00:18 - Extracting first name with Flash fill feature of Excel 1:47 - Converting numbers to percentages 3:18 - Remove blank cells or rows using Goto special 4:43 - Find common values between two lists 5:32 - Clean-up ugly formats with one click 6:15 - Bonus trick #1 - Remove extra spaces 7:30 - Unstack data easily with simple formulas 9:44 - Removing duplicate combinations in your data 10:37 - Deleting yellow or any colour cells 12:09 - Extract numbers from text values easily 13:35 - Get file names from full path with Find Replace in Excel 15:36 - Bonus trick #2 - Dealing with numbers formatted as text
Love you so much Brother...For Your practice sheet and for the tutorial. It Saves lot of time. May Allah bless You all with success, health, happiness, patience and strength.
I do most of these practices but I didn't know that there's a flash fill function. I wasn't aware haha that's much faster and easier than doing delimit or formulas!!! Perfect! And very clear explanations. Yesterday was my first time watching your vid, but now I'm subscribing 👏💪🤟
Excellent video Chandoo and we particular like the section at 9:44 - (Removing duplicate combinations in your data) as this is something we have been working on for many years in our tools, and although Excel has a very quick method of finding and removing duplicates we find that by combining advanced data matching algorithms with configurable lookup libraries can provide the most accurate results.
you are a genius sir, your way of teaching, your example and your English is so simple. I don't have words to say thanks. your example no#6 is most useful for me.
@@chandoo_ People like you have been making many of our career. Thank you is not enough for your work. I am addicted with Excel you can say as I have been using it for last few years. Still love to watch videos online and learn new tricks. Keep it up. I am following you videos.
Awesome tricks! Thanks you Chandoo👌I truly want express how much I appreciate all your efforts in helping us learning through your awesome videos and the useful practice files you prepare for us.
Great thanks, Chandoo for this video! Especially thanks for special way #2 with data format. Usually i write formula "Ifmistakes", but your way more easy and fast, cool 👌
Hi Chandoo. Great Vidéo/ I'm new to your channel and subscribed. I speak french but i understand your vids. My contribution to the #6 unstacking data: Another way to Unstack Data First, write these formulas like this: E6=C6; F6=C7 and G6=C8 in the first row
Then select the first 3 rows
Then use the fill handle to copy down(fill without formats) Then copy paste as value to get rid of the underlying formula
From here, to get rid of blank cells, select the range (without the header) and sort from Z to A
Great video! Thank you! I just started using the last Bonus trick recently, after I had learned about it. It comes in very handy, because that is a big issue!
Hi Chandoo! Enjoyed your video as always. One thing though, when it comes to unstacking data (timestamp 7:30) there’s a simpler and easier way to do that (at least for those of us who have Office 365). In cell E6 you use this formula: =INDEX($C$6:$C$29,SEQUENCE(ROWS($C$6:$C$29)/3,3)) where the SEQUENCE(ROWS($C$6:$C$29)/3,3) actually feeds both the number of rows and the number of columns to the INDEX function.
Amazing job Chandoo. Loved all your videos. Plz keep doing gr8 job. A quick ques on tip-9, when i try to type numver un 2nd cell, flash does shows all bottom values in dollar but goes away quickly too. How do I get it in all cells?
Last bonus trick and stack data trick is awesome. Bcoz i know those all trick. I know that unstack data trick im different way. But u did it in easier way
Here is a dead simple way...Lol. I love this guy's videos. Great job Chandoo.
Thank you, Chandoo. I got the recommendation from one of my colleague in Manila. Very simple and effective presentation.
Glad it was helpful!
OMG - this is the very best Excel video ever. Need to watch like 5 times and takes note. Gold found here.
Wow, thanks!
Very nice ^^ For #8 I think it’s helpful to also mention that the Find, Format button, drop down allows you to select a specific cell to just pull the format from. It helps if you don’t know the exact color you want to duplicate.
Good tip!
Woahh! nr 6: stacked columns, what a great and easy way to fix this! well done Chandoo!
I know, right?
@@chandoo_ how to make it dynamic sir?
Learn to do this in Power Query or a Pivot table. Power Query in particular will blow your mind. In my opinion, #6 won't work in many cases.
Hi Chandoo,
For #6: What I can do is just copy the 3 cell and go to Street cell and do Alt e+s+e(transpose) then enter. Stacked will be done in few seconds.
@@vikashaldar982 agree, but it is going to take longer.
I just have one word for this video: wow! I guess I could add 'thank you' for all those great tips too!
You are so welcome!
Most of them i know as financial guy but few are unique. Thank you Chandoo. GOD BLESS YOU.
Thanks Nitin...
Some are so easy but will save you time. I needed to know number 3, which will save me about 60to 90 minutes of work tomorrow. Thank you so much!!!
Chandoo sir, you are my lifeline... I can't imagine using the excel without your videos. Marvelous job !!!
Love from India.
One of the best Excel tutorial!
Wow, thanks!
0:00 - Introduction
00:18 - Extracting first name with Flash fill feature of Excel
1:47 - Converting numbers to percentages
3:18 - Remove blank cells or rows using Goto special
4:43 - Find common values between two lists
5:32 - Clean-up ugly formats with one click
6:15 - Bonus trick #1 - Remove extra spaces
7:30 - Unstack data easily with simple formulas
9:44 - Removing duplicate combinations in your data
10:37 - Deleting yellow or any colour cells
12:09 - Extract numbers from text values easily
13:35 - Get file names from full path with Find Replace in Excel
15:36 - Bonus trick #2 - Dealing with numbers formatted as text
Thank you for these time stamps
Thank u sir
@@Jupiter_176 sambh
Love you so much Brother...For Your practice sheet and for the tutorial. It Saves lot of time. May Allah bless You all with success, health, happiness, patience and strength.
Thank You So much, I have always struggled with Numbers in Text Format, but after watching the video I have got the solution.
I do most of these practices but I didn't know that there's a flash fill function. I wasn't aware haha that's much faster and easier than doing delimit or formulas!!! Perfect! And very clear explanations. Yesterday was my first time watching your vid, but now I'm subscribing 👏💪🤟
That's awesome! Welcome and more power to you. 😀
Thank you for sharing these tricks. I've always been a big fan of your tips... glad to see that you now have a thriving TH-cam channel as well!!
Happy to hear that!
Happy to find you in youtube. you ease my burden of extracting, cleaning and analyzing data for my report.
Salamat po.
Great video, thanks!
Glad you liked it!
Great video and the "Monika" with the "k"... Priceless!! 😄
Learned so many new things that will increase my productivity and save me more time. Thanks a lot Chandoo. Your fan since a decade.
Fantastic 🎉
Some of the trick I had know before but I learnt many
🙏
This is excellent. Awesome job
Thank you very much!
Dude, do you have SQL cleaning tutorial? You know what you are doing and most importantly you can teach!! You’ve made many people’s life a lot easier.
Excellent video Chandoo and we particular like the section at 9:44 - (Removing duplicate combinations in your data) as this is something we have been working on for many years in our tools, and although Excel has a very quick method of finding and removing duplicates we find that by combining advanced data matching algorithms with configurable lookup libraries can provide the most accurate results.
you are a genius sir, your way of teaching, your example and your English is so simple. I don't have words to say thanks. your example no#6 is most useful for me.
You are most welcome
Flash fill, awesome! thank you!
Knew most of these but enjoyed the way you presented. Excellent presentation and voice. Thank you for sharing.
Thank you. I am glad you enjoyed this.
@@chandoo_ People like you have been making many of our career. Thank you is not enough for your work. I am addicted with Excel you can say as I have been using it for last few years. Still love to watch videos online and learn new tricks. Keep it up. I am following you videos.
Excellent Excel tutorial!
Keep it up 🦾🦾
Amazing. This guy is wizard.
Awesome video!
Bonus trick #2 is just legendary.
Some elegant built-in alternatives to using mid() and find(). The one to extract numbers is awesome
Glad you found those useful.
Midway through the video and this is already the best excel video I've seen
Chandoo to be true bcoz of you i am trying to learn much about excel day on day basis , Great Guru!!!
Wow. You made it so simple. Thank you
You’re welcome 😊
Thank you so much man
Very useful and well presented
super cool tricks. I wish I knew about them during my heydays! thank you for sharing!
Chandoo Man!!! you're changing people's lives. Great content!
Awesome tricks! Thanks you Chandoo👌I truly want express how much I appreciate all your efforts in helping us learning through your awesome videos and the useful practice files you prepare for us.
Thank you so much, you are distinguished and always presents new information
Awesome.. I know you since 8 years ago however following u now.... My bad, I could follow you at the beginning...
You are really amazing....
You are the Guru in excel.
Thank you chandoo sir for making this type of videos, it really help us in our work
Thank you Chandoo, please keep going with your videos!!!!
You are absolutely amazing. Thank you for your videos
Great thanks, Chandoo for this video!
Especially thanks for special way #2 with data format.
Usually i write formula "Ifmistakes",
but your way more easy and fast, cool 👌
Hi Chandoo. Great Vidéo/ I'm new to your channel and subscribed. I speak french but i understand your vids. My contribution to the #6 unstacking data:
Another way to Unstack Data
First, write these formulas like this: E6=C6; F6=C7 and G6=C8 in the first row
Then select the first 3 rows
Then use the fill handle to copy down(fill without formats)
Then copy paste as value to get rid of the underlying formula
From here, to get rid of blank cells, select the range (without the header) and sort from Z to A
And you are done
Welcome to my channel Thierno...
Thanks for this beautiful tip.
Knew these features,but the way you combined these simple features were amazing
Thanks High Priestess :)
Really cool tricks... Flash fill and finding color formatted cells.
Thanks Nirmal...
This was awesome. Especially that of unstacking data
Thank you so much for showing us these smart solutions in Excel.
You're always awesome!!!.. Thanks..
Your videos are really helpfull and understandable.Thanks for your great work Chandoo
You are always unique , no words great one .
This was awesome! Thank you!!!
/even though I am good at excel, your channel is very refreshing and I still learn a lot
Video was helpful as always , Thanks Chandoo !
Wow - this dude is amazing - this was so useful. Thanks for the video!
Thanks Anthony 😀
Thank you. Well explained in your video. Thanks once again.
You are welcome!
Nice tutorial. Thanks so much.
Helpful for Campus Placement | NIT Raipur
" Super Thala" by watching video i can feel the Worth of this video keep doing....!!!
Thank you, Chando!
So simple and so powerful! :)
Glad it was helpful!
This is gold!!! U got a new subscriber
Great video! Thank you! I just started using the last Bonus trick recently, after I had learned about it. It comes in very handy, because that is a big issue!
Excellent video. Thanks for making it.
Glad you liked it!
Wonderful, Chandoo! Thank you.
You are welcome Gill.. 😀
You are awesome my friend and your mission to make people awesome in Excel is commendable.
Thanks a lot for sharing your knowledge.
Thanks Rawat. I like learning and sharing.
Thanks Chandoo... your vids helps me.
Good Stuff. Thumbs up for dear Chandoo, I learnt a lot. God bless you.
Unique function also can be used to remove duplicate 😊
Awesome video by awesome man teaching awesome things. Thank you Chandoo
Just wow 🙏 super explanation sir ... Just watching 10 m .iam decided to subscribe this channel .. what a explanation 🙏🙏 great sir
Wow... welcome to our little corner of the world :)
Helo Chandoo. Just a thanks for the free lesson. So helpful to me.
Wow, looks like you have a lot of experience especially #10 thank you. So impressed will follow your videos. Earlier used to follow Leila
Welcome aboard! Leila is a great instructor.
Great tips!!!!!!!!
this is awesome, thank you for making videos!
You are welcome Sona...
Great video, nice and easy explanation! Thanks you
Hi Chandoo! Enjoyed your video as always. One thing though, when it comes to unstacking data (timestamp 7:30) there’s a simpler and easier way to do that (at least for those of us who have Office 365).
In cell E6 you use this formula:
=INDEX($C$6:$C$29,SEQUENCE(ROWS($C$6:$C$29)/3,3))
where the SEQUENCE(ROWS($C$6:$C$29)/3,3) actually feeds both the number of rows and the number of columns to the INDEX function.
Thanks, always nice to find more approaches I didn't know before. Wondering about the approach with Power Query.
It's always nice to see different solutions. My thought was (for MS 365 users) =WrapRows(vector, 3) which also solves the issue
God bless you man
you are our hero
Thanks a lot
Thank you. 🙏
Great tricks! Thank you for this video. Your channel already added to my subscription list:) cheers from Poland.
Best Channel Chandoo, tricks are awesome, keep up the good work. Excellently simple.
Thank you.
Thanks for sharing great technique
Goodluck Sir
Wow! These are super tricks. You never disappoint! 🙂👍
Extract no. Was awesome
Amazing job Chandoo. Loved all your videos. Plz keep doing gr8 job. A quick ques on tip-9, when i try to type numver un 2nd cell, flash does shows all bottom values in dollar but goes away quickly too. How do I get it in all cells?
hmm. You can trigger it again with Ctrl+E. Let me know if that doesn't work.
THNAK YOU VERY MUCH SIR 🙏❤️..
THIS VIDEO IS REALLY VALUE ADDING...KEEP IT UP 👍👍✌️.
THANKS AGAIN 🙏🙏
You're someone's reason to smile😍
Wonderful. Many thanks
You are welcome Erica... 😀
This was great. Super useful tips. Thank you!
You are welcome Bryan...
I would request you to consider Excel for Mac users as well in your demo's. My guess is lots of features are not activated/available on Excel for Mac
Thanks for Existing, you are awesome.
Thanks Rahul. :)
sir u are doing excellent work.. thnks a lot
This is a dead simple video ! Another great content
Thank you!
Last bonus trick and stack data trick is awesome. Bcoz i know those all trick. I know that unstack data trick im different way. But u did it in easier way
Thanks Vishal... :)
Awesome few tricks are very helpful chandoo
excellent video, chandoo thank you alot
Thanks from Brazil
Gracias Thiago...
Thank you so much. This is awesome and will be applied so often and will safe so much time!
You're so welcome!
Thanks for the upload
Useful tricks.👍