I love these tips! Thanks for compiling them, Chandoo! At 1:43, I thought Sumit would sort Left to Right by Row 1. (Data, Sort, More Options, Sort Left to Right).
@@chandoo_ sir that trick not about sorting data. Some times we need to move amount data before date or something else. That time we use this trick. We can't do that thing with the help of sorting. Sorry bye my bad english. 😃😃
Teaching excel is one thing and admiring the work of your competitors / fellow teachers is another. Your gesture is highly appreciated. This makes you even more respectable. Thank you. 👍🏼
Hi Sumit Bansal, there's a simpler solution to reorder the columns: 1. select all data (except for column A) 2.Data-> Sort->Options>Sort Left to Right 3. sort by: Row1 and press: OK 😊
Thanks guys!!! Awesome video Chandoo! My favourite has to be the last one by Bill, with the multicolour data bar, which you can repeat for as many values as you'd like...!!!! What??? 👏👏👏
Hi, There were 2 out of were new and added to my knowledge. Bill's conditional formatting was awesome and another which was solution for double refresh query and pivot (I was always doing refresh double till date). Thanks Chandoo.. You should have also contributed to this by one of your tricks.
You are welcome Rajan. Those two are new for me too. My channel is full of my tips already. So in this video, I wanted to sit back, take notes & learn from the gurus 😀
Thanks Chandoo for bringing the best leaders of Excel in 1 place. Lovely to see the legends - Bill, Ken, etc... Hope you can also bring in Mike Girvin next time too. thx Chandoo!
@@chandoo_ Very strange that Excel would have the keys used totally swapped for the same functionality within tables vs ranges. SHIFT+Drag in ranges is the same as ALT+Drag in Tables and ALT+Drag in ranges is the same as SHIFT+Drag in talbes. You would think it makes sense to have them work the same but oh well, I've stop scratching my head on some Microsoft decisions.
Thanks Chandoo for this super useful video! Would like to see more like these in future! There are many experts out there, who we may not know, but can expect to reach them through you! 😊👍
Hi. I have a data starting from column B1 till B9 (in column B). Data is 1,2,3,4,5,6,7,8,9 (B1=1, B2=2, B3=3 etc.) I want to put this data with a suitable formula in Column A starting from cell A1. Should look as below: A1 = 1 A3 = 2 A6 = 3 A9 = 4 so going on.
The tip where the %% was put into the formula itself for me is a "not done". Always refer to a separate %%-cell for this. This is how big firms screwed up when the VAT-%%had to be changed, but not picked up in the workfiles
Thalaiva.....Neenga engiyo poiteenga.... Great to see you extending your reach by onboarding many folks to talk....And also great video editing skills.... Pls make us 1 video to show how you perform video editing.... Thanks a ton!!!
Chandoo your videos are amazing and i get ideas from your videos and it is very helpful for me to create my UA-cam Videos. Lots of love and respect. Cheers!
how can i write a formula that only compares 2 cells in a given row that will have 4 values? For example, cell B1 has a value of 6 and C1 has a value of 2. the "Difference cell" will total C1-B1 = -4. then when i add a value of 3 into cell D1 of 3, the "difference cell" will total D1-C1 = 1. then when i add a value of 3 into cell E1, the "difference cell" will total E1-D1 = 0. Hope this makes sense lol
Hi Chandoo here'Renato from Venice, Italy. Many thanks for yr videos, tips and tricks. Is it possible fo u to share the complete recipe of Bill Jelens's two colour databars since I have been trying for all the morning with very poor results. Many thanks in advance and my best season's greetings to u an yr family.
14:00 One day I will be good enough at excel so that I even have a clue what to use this for..... then I might be able to learn how to do this, or even understand what Bill is talking about :) Don't get me wrong, I am very impressed, but my skills are so basic at the moment so I don't know how to use this, or when... :)
Would someone interested in Data Science but only has 12th grade education, but has experience of more than 15 years in different fields, get a job in Data Science if they comlete a course?
Have you got a tip to paste a link into an excel table? I want to link cell B2 on sheet 1 into an excel table on sheet 2. It won't let me paste inside the table but will let me paste outside the table on sheet 2. Nothing is protected
![Дон ДОН, Алаудинов и СБЕЖАВШИЕ из под Курска ахматовцы 😁 [Пародия]](http://i.ytimg.com/vi/dEfCf5IK26Q/mqdefault.jpg)
I love these tips! Thanks for compiling them, Chandoo! At 1:43, I thought Sumit would sort Left to Right by Row 1. (Data, Sort, More Options, Sort Left to Right).
the secret 6 coloured ICONS I love these tips ,
Please I want to know the method or a ready-made file because I could not implement it
That trick(Sumit Sir's Trick) is not new for me sir. But u r legend of excel 🙏🙏
Thank you Bill for doing this. There are many gems in the video and more in the comments.
Yes. RtL sort is another amazing option.
@@chandoo_ sir that trick not about sorting data. Some times we need to move amount data before date or something else. That time we use this trick. We can't do that thing with the help of sorting. Sorry bye my bad english. 😃😃
@@vishalbhati912 try insert new row and right 1 to 5 according to you and then sort , u can sort columns
Memes in this education video is like cheery on cake 😂😂
I had loads of fun editing the video. I am glad you enjoyed it 😀
Agree, the Memes were fun to have in the videos
Wow what a comment❤
Teaching excel is one thing and admiring the work of your competitors / fellow teachers is another. Your gesture is highly appreciated. This makes you even more respectable. Thank you. 👍🏼
Thank you Chandoo for putting this together - it looks like you've had LOADS of fun with the editing process. So excited to be a part of it!
It was a pleasure having you in this video Danielle. Your trick on using UNIQUE to make a pivot is 🤯
Hi Sumit Bansal,
there's a simpler solution to reorder the columns:
1. select all data (except for column A)
2.Data-> Sort->Options>Sort Left to Right
3. sort by: Row1 and press: OK
😊
We definitely need a second part !
A Great collection of tips. Even for those recognized, it is a good reminder and refresher to use them. Thanks and thumbs up!!
Ooh the power query default is something i can use right now!
Such a useful video, thank you for putting something like this together.
Thanks guys!!! Awesome video Chandoo! My favourite has to be the last one by Bill, with the multicolour data bar, which you can repeat for as many values as you'd like...!!!! What??? 👏👏👏
Thanks Chandoo for bringing them all together!! that was FUN!!
Nice....Bill and Boriana trick I liked...
Wow... Thanks.
Handy tricks 👍
Hi,
There were 2 out of were new and added to my knowledge. Bill's conditional formatting was awesome and another which was solution for double refresh query and pivot (I was always doing refresh double till date).
Thanks Chandoo.. You should have also contributed to this by one of your tricks.
You are welcome Rajan. Those two are new for me too.
My channel is full of my tips already. So in this video, I wanted to sit back, take notes & learn from the gurus 😀
Thanks for bringing together the experts! They are awesome. I regularly refer their videos.
Excellent ideas bringing experts into one clip. Sharing is caring. Thanks.
Thank you. Going by the response of our community, this is a HIT... I might do more of these next year.
Thanks Chandoo for bringing the best leaders of Excel in 1 place. Lovely to see the legends - Bill, Ken, etc... Hope you can also bring in Mike Girvin next time too. thx Chandoo!
You are welcome Cherian. I did try to get Mike, but our schedules didn't work out. Hopefully next year... 🤞
Thank you, amazing source.
Keep in mind Sumit's tip on moving ranges of data does not work with Tables. If you have a table you must Alt+drag instead of Shift+drag.
Yes. ALT when using tables and SHIFT for regular ranges.
@@chandoo_ Very strange that Excel would have the keys used totally swapped for the same functionality within tables vs ranges. SHIFT+Drag in ranges is the same as ALT+Drag in Tables and ALT+Drag in ranges is the same as SHIFT+Drag in talbes. You would think it makes sense to have them work the same but oh well, I've stop scratching my head on some Microsoft decisions.
Hi, in tables you just have to drag without pressing the ALT key and it works. But nevertheless, it´s different within ranges.
Thanks, Chandoo! Now I found lots of rock stars in Excel apart from you! Will check them out, too!
Best video I have watched today. Thank you!
Wow, thanks!
Nice👏👏👏👏😊.... Thank for supporting chandoo... For making this vedio
Thanks Chandoo for this super useful video! Would like to see more like these in future! There are many experts out there, who we may not know, but can expect to reach them through you! 😊👍
Thank you Vijay. I will invite few Excel / Power BI people to the channel next year too so we all can learn from them.
thanks to all the wonderful trick masters and yes chandoo too👍
Makes me feel like a master as I knew all of these tips.
Thank you! So nice to see you all in one video :)
Love these tips, thank u sir.
Hey, the great Excel gurus. Wonderful.
Thanks to the experts for the helpful tips.
well done and some funny edits...you've gained a subscriber and contributors too!
Yess.... really epic. Thanks Chandoo
You are welcome Eri... 😀
No Mike Girvin and Leila Gharani? :(
I did try to get both of them for the episode. Unfortunately our schedules couldn't work out. May be next year. 😀
Chandoo bhai is legend. Thank you bhai for sharing your expertise, not many people who know are willing to share. 😊👌
So nice of you. I learn a lot from all these people. So naturally I want to share their goods with you all 😀
Awesome Chandoo😊 Thanks
Unique video👍🏼👍🏼👈
Thank you very much....
Hey Chandoo !! :)
This was awesome Like avengers landed at a time to save the time giving useful techniques
Superb ! Keep going like this !!
May be next time, I will get an eyepatch...
Thanks 🙏
Thanks for the video Chandoo! I really enjoyed these tips, they are all so very useful. Looking forward to more of these!
Hi. I have a data starting from column B1 till B9 (in column B). Data is 1,2,3,4,5,6,7,8,9 (B1=1, B2=2, B3=3 etc.)
I want to put this data with a suitable formula in Column A starting from cell A1. Should look as below:
A1 = 1
A3 = 2
A6 = 3
A9 = 4
so going on.
Excellent intiative Chandoo 👌
Really nice to hear from experts that I didn't know, apart from Bill Jelen
Thanks Chandoo, 👍👍
Boriana mam's trick is really awesome. And second trick is really awesome. Everyone thought y we didn't thought that 😀😀
Glad you liked it
Really amazing tips and tricks,.... Thank you all respected...
Thanks Chandoo for this super useful gem tips! 🥰
Great video! Thank you Chandoo!
Glad you liked it!
Loved this, thanks!
It is really enjoyable. Thanks for sharing.
I’m with Ken Puls on this one: in PQ default to Connection Only is a must for efficiency, IMO.
…Add no automatic data types to that, as well. :-)
OTOH, for sub- and grand totals, I just use the SUBTOTAL function, no need to correct the double count.
Great suggestion on "automatic data types".
This cocktail is amazing 👏
Thanks Nirmal. Cheers 🥂🥂🥂
Sir you are the expert for me
No Mike Girvin - ExcelIsFun??? Still, much appreciated, great for a beginner like myself! Thanks!
I would so love to feature Mike on the channel. But he has a busy holiday season, so may be next year 😀
Impressive
Thank you SO much - awesome tips!
You are so welcome!
Excellent!
😀😀😀
Excellent tips thanks Chandoo for sharing
Great tips , really appreciated .
Thankies
Glad it was helpful!
Lol. I love this
The tip where the %% was put into the formula itself for me is a "not done". Always refer to a separate %%-cell for this.
This is how big firms screwed up when the VAT-%%had to be changed, but not picked up in the workfiles
Of course, that is not a best practice. But she is showing the concept of how Excel can natively process the arrays now.
Thank you @Chandoo 🙂
Hi Chandoo teacher, Thanks a lot to make us awesome in excel you are really awesome
You are welcome Tanveer...
Thanks to all Excel Genious people 🙏🙏🙏🙏🙏
Thanks Chandoo... this was fantastic
You are one of them 🤫🙂
All xls troubleshooters one video and amazing tips revel, please continue every month once sir
Thank you Kumar. I can't promise a monthly thing, but I will certainly try and do this at the end of the year again.
This was awesome 😎
YES!!!
Leila Gharani is one of the best excel tipster too..
Yes... I did try to get on her on the episode, unfortunately our calendars didn't align. May be next year?
Thalaiva.....Neenga engiyo poiteenga.... Great to see you extending your reach by onboarding many folks to talk....And also great video editing skills.... Pls make us 1 video to show how you perform video editing.... Thanks a ton!!!
Nanri Sarnath 🙏
You are going to love my next video. Stay tuned 🤫🤫🤫
Great👍
Lago raho Chandoo bhai
Chandoo your videos are amazing and i get ideas from your videos and it is very helpful for me to create my UA-cam Videos. Lots of love and respect. Cheers!
thank you chandoo for this excellent idea its was great
Most welcome 😊
hi Chandoo, Nice tricks from the experts... I love the video editing as well
AGGREGATE(9,0,range) to exclude subtotals from sum in O365. If you were to have daily and weekly subtotals, range/2 is going to be wildly inaccurate
amazing tips, thanks so much for organizing and sharing it:)0
Re Ken Jelen's numbering 1-100000 tip: you could also write =SEQUENCE(100000).
how can i write a formula that only compares 2 cells in a given row that will have 4 values? For example, cell B1 has a value of 6 and C1 has a value of 2. the "Difference cell" will total C1-B1 = -4. then when i add a value of 3 into cell D1 of 3, the "difference cell" will total D1-C1 = 1. then when i add a value of 3 into cell E1, the "difference cell" will total E1-D1 = 0. Hope this makes sense lol
I were know many of trick, except Bill conditional formatting
great excel tips [is arrange all = windows key + right arrow has same function ?]
Column move krne k liye to cut kro or jaha p move krna hai waha p ctrl and + key press kro or move ho jaega skmple
my vote goes to
Ken's second trick - PQ Double refresh problem & fix and Bill's last secret trick (this is a real hack)!!!
Most unselfish professional you are ....
Chandoo you nail it❤
Hi Chandoo
Great tips - appreciated
My pleasure!
Sales data can be sort by assending a to Z column +
?
Hi Chandoo here'Renato from Venice, Italy.
Many thanks for yr videos, tips and tricks.
Is it possible fo u to share the complete recipe of Bill Jelens's two colour databars since I have been trying for all the morning with very poor results.
Many thanks in advance and my best season's greetings to u an yr family.
Avengers, assemble!
14:00 One day I will be good enough at excel so that I even have a clue what to use this for..... then I might be able to learn how to do this, or even understand what Bill is talking about :)
Don't get me wrong, I am very impressed, but my skills are so basic at the moment so I don't know how to use this, or when... :)
Even I didn't that was possible until Bill showed it. I am sure pretty soon you will catch up with most basics and learning complex stuff.
Would someone interested in Data Science but only has 12th grade education, but has experience of more than 15 years in different fields, get a job in Data Science if they comlete a course?
I want to ask Mr. /Submit Bansal if I can .is he stop loading new videos in you type . we are fan for him . thank you Mr. chandoo for everything .
Candu bhai pls make video on blockchain developer. It humble request to u
Where is leala gharani
Hmm.. I so wanted to get her for this episode, but our schedules didn't match. May be next year 😀
@@chandoo_ really blessed and thanks brother for your kind heart
Have you got a tip to paste a link into an excel table? I want to link cell B2 on sheet 1 into an excel table on sheet 2. It won't let me paste inside the table but will let me paste outside the table on sheet 2. Nothing is protected
I still prefer your tutorial Chandoo :)
Calculating grand total with subtotals made me feel myself complete idiot 🤣🤣🤣
Yep, I also found that one to be an AWESOME use of simple arithmetic. 🤯
Video not playing.....
Hi Chandoo. Some advice please. Which clip on mic do you use for recordings? Thanx.
You will be thrilled with my next video Chris. Wait and watch 😀🤫
Missed 1 person Laila Gharani
Unfortunately yes. I did try to get her on this list, but our schedules didn't work out. May be next year.
Great concept for video but the cutsie media insertions throughout are distracting and not needed.
Thanks Stu... You are in a for a lot of disappointment as most of my videos feature lame puns, dad jokes and memes.
I have some dentistry tips.