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?
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?
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.
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.
Love these Boss videos! Keep up the good work!
Chandoo, you are great. Thanks, I always place a like on all of your videos that I watch
I appreciate that!
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!
Awesome! Thanks to you and your boss, I always Learn.
The more I watch your videos, the more I love them ^^
Thank you Mr. Chandoo. It's useful in MIS preparations
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?
Amaizing learning Chandoo Ji. Thank you
Chandoo.. Your Boss is one Lucky person.
Very Good Tips ! Many thanks Chadoo !!
Great 👍 shortcuts... Thanks 😊 Chandoo...
Thank you for amazing tricks Love from India 🇮🇳
Wonderful!
Sir please make one video
On how to documenting your power bi project
I request to you
You are amazing gifted given person = thank you for everything.
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!
Wow, I almost miss work - love these! Thanks!
Thanks a lot, I learn something new with every video you create
I always enjoy your video, because of the you do your explanations.. they are hilarious
Awesome tricks
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!
Very good, nice tips. Thank you Chandoo!
Thanks 🙏❤
Sir its thankful to reach you
mba marketing done
sql and power bi need to learn fom basics
Need job
Thank you 🙏😊
I want know how to make report of first in and last out transaction.please make video of it
Sometimes I think I am your boss.
Funny
FYI.. I've emailed you those TPS reports.
😂😂
Thank you, Chandoo🙏🙏🙏
Wow! Amazing! 😎😍 thank you so much!😊
You're welcome 😊
Excellent...
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..
Crazy demanding boss😂
Thank you
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
I helped my boss with this now he is hiss boss boss 💪🏻😄
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?
I liked format painter the most.
It is one of my favourite feature of Excel
Sir very useful video
Thank you
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.
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.
ur good!
U R Awesome....
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
Awesome
ahh fabulous!
Good morning teacher: this is crazy! thanks again, again ... and again.
You're so welcome!
Ok sir
thanks unc
Boos is awesome
Nice tamil accent with english😊
😅😅😅
Interesting
Without daily practice this is nothing
Booom!
Pls suggest me tq😊
I am not getting TOCOL :-(
Cheeky 😂😅😊
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.
beauty!!🫠
Thanks 👍