I've been self studying Excel for about 3 weeks now and set myself challenges after creating my own datasets. Well I wanted to create a bank statement with running totals and spent 2 days trying to figure out a formula to do it; I accidentally came across the quick analysis but quickly closed it and finally figured it out yesterday and now I've seen this, it's bittersweet to say the least! Great videos and really entertaining and useful, definitely subbed!!😂😂 👍🏾
Really great video Chandoo! Particularly like the part about aligning and distributing chart elements, super important functions for improving optics and impressing your client/boss!
Thank you for the fantastic tricks. You always rock. I have a question regarding the auto-resizing of the column. We can either double-click on the columns or use ALT+H+O+I. Now my question is how can I auto-resize the entire workbook columns in one go?
if your boss doesn't give you enough salary, don't bother yourself with helping your boss. He won't raise your salary anyway. Do your minimum at work. Why exhaust yourself to make your boss rich while you're earning little money? Not worth it. Life is short. Be smart.
hello sirs , please help A folder contains more than fifty Excel sheets with the same name An additional serial number is affixed to each after the original How can one of these origins be isolated?
Hi Chandoo..Thanks for these videos and time, quite useful to develop my knowledge in Excel. I need some expert advice...I am dealing with a Excel file of 13,000 rows+... earlier I was using aumproduct function later changed to sumifs due to performance issues. I am not sure which formula is better but sumifs is little fast for me. But I have 1 problem, I want to sum multiple columns with same condition, in aumproduct I can do it with but in sumifs the formula is very long and I want to avoid it. so please suggest any option where I can sum 5 to 6 column, but still the conditions are same.
Can you give me an example of the condition you are trying to do? With 13k rows, the performance of SUMIFS vs SUMPRODUCT shouldn't be too different. May be you have got something else on the page that is slowing things down. Read up chandoo.org/wp/optimize-speedup-excel-formulas/ to implement some best practices.
@@chandoo_ Hello Chandoo Thanks for your quick reply. Yes In my file I use the Indirect function to calculate the currency conversion along with offset and subtotal...so it takes some time whenever I do some calculations. Anyhow with Xlookup I completely avoid indirect function use. coming to the current problem let's say I have a table with column A name, Column B department, Column C to G marks by each subject. What I want to do is to find the sum of all 5 columns by the department in a single sumifs formula. With aumproduct I can consider Column C to G as an array, but I want to do it in Sumifs without adding any new columns. Of course I can add the total column and do the sumifs, but I want to understand with a single sumifs formula this is possible or not. Note: I just explain as an example to make it easier, my working file has columns at different intervals and I want to do sumifs. I can copy the sumifs formula by each column, but the formula becomes longer. Since the condition is the same for all sumifs I want to explore any shortcut available. in case my explanation is not clear let me know I will try to upload the sample workbook in Google drive. Thanks for your support in advance.
You can't solve this with a single SUMIFS as it needs condition and sum arrays to be of same size. You can continue to use SUMPRODUCT or even SUM (it works the same way) or FILTER and SUM (as you have Excel 365). For example, =SUM(FILTER(C:G, B="Electronics")) will give you a sum of the marks in C:G where B is Electronics.
Thanks Chandoo...I will try the filter function, but is there any possibility to do the sum 2 columns and subtract 3 columns in the same filter function. (i.e.) =sum(filter((c:e, -f,g),B="Electronics") I understand it will be easy to add sum column and do it, but just want to explore with a single formula we can do it or not.
I don’t like the smugness of these videos. Meanwhile when your workplace isn’t going to that version of excel or having these addin packs , some won’t work. Then the others are things that have been around since 1995
This series is a fun way to explain Excel topics. There is nothing smug about them as I don't really have a boss. While we can all look back on the great products from past with fond nostalgia, unfortunately the world around is moving too fast and occasionally you may want to catch up.
![Excel Tutorial for Beginners 2024 [Made Easy]](http://i.ytimg.com/vi/fzdn1vcHPsA/mqdefault.jpg)
![[UA] Team Vitality проти NAVI | IEM Cologne 2024](http://i.ytimg.com/vi/wttO1zVRTC0/mqdefault.jpg)
![АЛАУДИНОВ у Скабеевой: эти их ВСУ нас НЕ ДОГОНЯТ 😁 [Пародия]](http://i.ytimg.com/vi/an0anqU4WGQ/mqdefault.jpg)
![19 Excel Shortcuts you NEED to use more [with FREE PDF 📃]](/img/n.gif)
Love these Boss videos! Keep up the good work!
I've been self studying Excel for about 3 weeks now and set myself challenges after creating my own datasets. Well I wanted to create a bank statement with running totals and spent 2 days trying to figure out a formula to do it; I accidentally came across the quick analysis but quickly closed it and finally figured it out yesterday and now I've seen this, it's bittersweet to say the least!
Great videos and really entertaining and useful, definitely subbed!!😂😂 👍🏾
Chandoo, you are great. Thanks, I always place a like on all of your videos that I watch
I appreciate that!
Really great video Chandoo! Particularly like the part about aligning and distributing chart elements, super important functions for improving optics and impressing your client/boss!
The more I watch your videos, the more I love them ^^
Awesome tricks
Chandoo.. Your Boss is one Lucky person.
Thank you 🙏😊
Love watching how you present this video... I have given my 👍. thank you for the knowledge sharing.
My pleasure
My all-time favorite excel man!
Awesome! Thanks to you and your boss, I always Learn.
Thank you Mr. Chandoo. It's useful in MIS preparations
Thank you for amazing tricks Love from India 🇮🇳
Amazing tricks....Excel is so vast, what ever we learn later we realise that we dont know anything much😎
Great video, thanks Chandoo!
Glad you liked it!
Thank you
You are amazing gifted given person = thank you for everything.
Thanks 👍
Sometimes I think I am your boss.
Funny
FYI.. I've emailed you those TPS reports.
😂😂
Thanks 🙏❤
Very Good Tips ! Many thanks Chadoo !!
I liked format painter the most.
It is one of my favourite feature of Excel
Wow, I almost miss work - love these! Thanks!
Amaizing learning Chandoo Ji. Thank you
Thank you for the fantastic tricks. You always rock. I have a question regarding the auto-resizing of the column. We can either double-click on the columns or use ALT+H+O+I. Now my question is how can I auto-resize the entire workbook columns in one go?
Excellent...
Great 👍 shortcuts... Thanks 😊 Chandoo...
Thanks a lot, I learn something new with every video you create
if your boss doesn't give you enough salary, don't bother yourself with helping your boss. He won't raise your salary anyway. Do your minimum at work. Why exhaust yourself to make your boss rich while you're earning little money? Not worth it. Life is short. Be smart.
Thank you, Chandoo🙏🙏🙏
Very good, nice tips. Thank you Chandoo!
Wow! Amazing! 😎😍 thank you so much!😊
You're welcome 😊
I always enjoy your video, because of the you do your explanations.. they are hilarious
Sir its thankful to reach you
mba marketing done
sql and power bi need to learn fom basics
Need job
I helped my boss with this now he is hiss boss boss 💪🏻😄
U R Awesome....
Ok sir
Crazy demanding boss😂
thanks unc
Sir very useful video
Awesome
Wonderful!
Sir please make one video
On how to documenting your power bi project
I request to you
ur good!
ahh fabulous!
Without daily practice this is nothing
I want know how to make report of first in and last out transaction.please make video of it
Chandoo this is a great job, but to help some of us, kindly attach the data set for practice. Great job once again..
These are collection of different tips. So you can use any of your own datasets (or even the files I have on other videos) to practice.
@@chandoo_ great, I will search these videos. Thanks man..
Booom!
Good morning teacher: this is crazy! thanks again, again ... and again.
You're so welcome!
Boos is awesome
Nice tamil accent with english😊
hello sirs , please help
A folder contains more than fifty Excel sheets with the same name
An additional serial number is affixed to each after the original
How can one of these origins be isolated?
😅😅😅
Interesting
Hi , for 1st shortcut, after applying formula , data is converted intoa single column row wise , what if we want data in column wise only ?
Use torow() function
@@chandoo_ Thank you
Hi Chandoo..Thanks for these videos and time, quite useful to develop my knowledge in Excel. I need some expert advice...I am dealing with a Excel file of 13,000 rows+... earlier I was using aumproduct function later changed to sumifs due to performance issues. I am not sure which formula is better but sumifs is little fast for me.
But I have 1 problem, I want to sum multiple columns with same condition, in aumproduct I can do it with but in sumifs the formula is very long and I want to avoid it. so please suggest any option where I can sum 5 to 6 column, but still the conditions are same.
Can you give me an example of the condition you are trying to do?
With 13k rows, the performance of SUMIFS vs SUMPRODUCT shouldn't be too different. May be you have got something else on the page that is slowing things down. Read up chandoo.org/wp/optimize-speedup-excel-formulas/ to implement some best practices.
@@chandoo_ Hello Chandoo Thanks for your quick reply. Yes In my file I use the Indirect function to calculate the currency conversion along with offset and subtotal...so it takes some time whenever I do some calculations. Anyhow with Xlookup I completely avoid indirect function use.
coming to the current problem let's say I have a table with column A name, Column B department, Column C to G marks by each subject. What I want to do is to find the sum of all 5 columns by the department in a single sumifs formula. With aumproduct I can consider Column C to G as an array, but I want to do it in Sumifs without adding any new columns. Of course I can add the total column and do the sumifs, but I want to understand with a single sumifs formula this is possible or not.
Note: I just explain as an example to make it easier, my working file has columns at different intervals and I want to do sumifs. I can copy the sumifs formula by each column, but the formula becomes longer. Since the condition is the same for all sumifs I want to explore any shortcut available.
in case my explanation is not clear let me know I will try to upload the sample workbook in Google drive.
Thanks for your support in advance.
in practical cases I may required to add first 3 columns and deduct 2 columns and find the answer by same conditiona
You can't solve this with a single SUMIFS as it needs condition and sum arrays to be of same size. You can continue to use SUMPRODUCT or even SUM (it works the same way) or FILTER and SUM (as you have Excel 365).
For example, =SUM(FILTER(C:G, B="Electronics")) will give you a sum of the marks in C:G where B is Electronics.
Thanks Chandoo...I will try the filter function, but is there any possibility to do the sum 2 columns and subtract 3 columns in the same filter function. (i.e.) =sum(filter((c:e, -f,g),B="Electronics")
I understand it will be easy to add sum column and do it, but just want to explore with a single formula we can do it or not.
Cheeky 😂😅😊
Pls suggest me tq😊
I am not getting TOCOL :-(
chandooooooooooooooo
I don’t like the smugness of these videos. Meanwhile when your workplace isn’t going to that version of excel or having these addin packs , some won’t work. Then the others are things that have been around since 1995
This series is a fun way to explain Excel topics. There is nothing smug about them as I don't really have a boss. While we can all look back on the great products from past with fond nostalgia, unfortunately the world around is moving too fast and occasionally you may want to catch up.
Hello Sir, I am from India and I am coming there within 2 years. I don’t know what to do, would you advise me! It’s really help to me if you can
Amazing video. I would like to get training from you. Pls let me know how to contact you. thanks
Thank you. You can message me going to chandoo.org/wp/contact/
beauty!!🫠
Thank you