I use subtotal a lot also. I put it on the top row and give it plenty of rows in the formula in the case more data is added at the bottom. It works great for giving totals when the list is filtered.
ok, I have watched two of your videos and you are really good at teaching this stuff! I just wrote a tedious formula the other day and now I can go back and IFERROR it way easier!
Agreed, 100%! I use HYPERLINK, FILTER with SORT, TODAY, CONCATENATE, COUNTA, COUNTIF, some of the ones dealing with time and dates, LEFT, NETWORKDAYS - and of course IF, AND, OR, SUM, AVERAGE. Turns out I don't do much math!
Thank you for this and many other videos. Your narration, explanation with examples are extremely helpful. Can’t be any more effective. Im in love with excel all over again now
All of all this video is mindblowing .. (posting comment even before I finished watching the video) Xlookup with filter ... I really wished I knewed that before... !! Thanks
Chandoo! You really got me with the like() function! Hahaha! You sir, deserve my like! (im already a loyal subscriber) On a sidenote: If we already have xlookup.. do we still have a use for the index function?
Thanks for the love Kairos. INDEX is helpful in many other situations too, not just in INDEX MATCH. For example, if you want 2nd column values of a FILTER result, you can use =INDEX(FILTER(...), , 2)
Thanks for giving us the most common formulas to focus on. Excel is the most difficult discipline to learn among the fields of science. But you make it easy for us. .. Thanks and more power.
I thought I am the only one suffering :) I also want to say thank you Chandoo for always including a file to follow along. Please let me know which video I can learn more on countifs date using &. Thank you so much!
See this video for more on COUNTIFS / SUMIFS ruclips.net/video/B5hayFelHDU/видео.html Also refer to the other videos in the description. Some good pointers there.
People are probably better off using a proper programming language for most of those other functions. It's certainly safer than embedding a bunch of macros in a spreadsheet.
I knew everything except edate. Maybe I knew but forgot edate. But the way you present them is still fun too watch. Also I like where you put 3 function in 1 line. I was confused whether to put unique whole outside or inside the filter function
Thanks Nikunj... Combining functions opens a whole new level of awesomeness. If you are having some trouble, watch this video as well - ruclips.net/video/Y3DEt7P62G8/видео.html
But if you inherit a spreadsheet that’s been in use for a long time, you may not see functions like FILTER() and XLOOKUP(), so it’s best to understand other functions like VLOOKUP(), HLOOKUP(), SUMPRODUCT() and others.
While each function has its own purpose, most of the time I use these 10. Once you are familiar with these, expand and learn other domain specific functions
I need help. We already have a formula to check if the content in cell 1 matches with cell 2. Do we have a way to Spot/highlight the difference between two cell's content
Hi, By any chance do you know any website/community where I can get real life problems that are related to excel/data anlaytics? I want to practice what I learn.
See the homework page on my site. chandoo.org/wp/category/excel-challenges/ Also you can participate in forums like stackoverflow where many people post their queries and answer them. It will build your skills up quickly.
I've searched all over your website for the practice workbook for download. Can't find it to save my life. Can you just post a direct link to it from this tutorial?
How to remove unwanted space in one shot.. is there any tricks, can use trim, but we need to use one Cell first then drag...is there any other method to clear it one click
Thanks for the video Chandoo! I wonder what your opinion is on the OFFSET formula? I use it almost exclusively over INDEX as it can produce ranges as well, instead of individual cells. Also, it's formula is quite elegant and simple, with tons of useful applications. Do you have a take on this?
You are welcome Soma. I try to avoid OFFSET if possible. As I use Excel 365, I have been relying on the spill ranges and new functions like choosecols etc. to do similar things. If I can't use the new functions, my goto choice is INDEX. It is semi-volatile unlike OFFSET which is volatile. So your worksheets will be quick.
@@chandoo_ I see. To be honest I'm not sure about the whole volatile thing. I have heard of it related to INDIRECT, but I'm still not sure what it means. Would really love a video on this topic! Or maybe you already have one? Thank you Chandoo!
Can you please make a video on, when assigning values to different (eg people) in corresponding columns, the highest value including the row should move upward automatically.
So using SORT to sort alphabetically of course only sorted by first names. Not very useful using English language names where you want to sort by surname. It's going to be difficult to combine FILTER and SORT to sort by last names but it would be elegant as opposed to splitting the names using Text to Colums (which doesn't work for all names). I'm guessing you'd have to filter by the first letter after the last space in the name and then sort the result. Possible? And would it work with a name like Quinton de Kock (cricketer) by sorting as a last name starting with a K not a d, as the prefix is not the actual surname or even more complicated Ursula K. le Guin (SF author)?
sadly, we cannot use most of fhese functions, as we need to build our excel in mind of older version that our client will most likely still use even up to 2010
Thanks for the video on the functions. I use most of them but your information regarding INDEX function has changed my perception. This function really can do a lot of things. I have a query if you can look into this. I am trying to arrange multiple column's (A:E) values into one (Col F) in an order. I came to know about TOCOL function but didn't find it in my Excel application.
TOCOL would be my goto option for this. Or VSTACK. I suggest using Power Query or similar option to consolidate if you don't have these functions. Save a lot of time thinking thru edge cases.
@@utdkidswifeITO To select multiple non-adjacent cells, use ‘ctrl’ shortcut key for this. Hope that helps. If you still have queries, I would love to help you.
Sir we have new feature in Power BI in quick measure with suggestion, wherein we write simple English and power BI generates Dax formula. Request you to prepare video on the same and help us. Thank you 🙏🙏
Why not make the function & other results on a level that 'float' above the table (the spreadsheet). Would be much easier to see than to move up and down. Elementary, MS.
Most used formulas include - Sumifs Countifs Iferror Match Index Edate Eomonth Vlookup Offset Max I use Excel 2016. Haven’t got an option to explore the simplified formula included in office 365
runtime error 1004 method onkey of object _application failed SIR HOW TO GET RID OF ERROR WHEN OPENING .xls (EXCEL 2007) File in office2016 . any possible solution
LAMBDAs are great and I have been using them often in my recent project. But I don't consider them essential at this point. I can still do the same with other functions or longer methods.
Concatenate and Xlookup I have been using for past few months. Concatenate to combine GSTN + INVOICE NO + INVOICE DATE and then XLOOKUP to retrieve the value. And of course, SUMIFS too will be in my daily menu
Great summary of indeed the most used Excel functions! I have another question though if you don't mind, I'm populating item descriptions from another tab, by using a lookup function. The descriptions are quite long, and as I'm creating my own invoices like that, I'd like the text to wrap nicely, based on the lookup result. However, the alignment only looks at the length of my formula, instead of the retrieved results. How would you tackle this?
@@chandoo_ The text indeed does wrap, but the adjustment of the cell width and height are manual. I was hoping to automate this, as for every invoice, the field will have a different length, and I want to avoid white lines.
Actually found the issue, I used merge cells, which disables the AutoFit function. I adjusted the layout and not at least it works manually by selecting all cells and click AutoFit. Next would be to automate this
Hi Chandoo, I emailed you questions about your course multiple times. I am waiting to hear your response. Please confirm if you received my email with the subject "Questions about Excel school courses".
What other functions you use all the time? Tell me in comments 💭🗯💬
Why are filters not working in macro steps?
Can you make video on indirect + index + match, to pull data from multiple sheets appreciate in advance. Thanks For the above top 10 formula.
'subtotals' is one of my favorites. Even if there's no filter, I always use 'subtotal' instead of 'sum'.
I use subtotal a lot also. I put it on the top row and give it plenty of rows in the formula in the case more data is added at the bottom. It works great for giving totals when the list is filtered.
I prefer using either Power Query or the new VSTACK for such things. I haven't used INDIRECT in many years now.
AWESOME! Thanks!
Thanks GS For the love and Super 😍😍😍
Thanks!
ok, I have watched two of your videos and you are really good at teaching this stuff! I just wrote a tedious formula the other day and now I can go back and IFERROR it way easier!
Agreed, 100%! I use HYPERLINK, FILTER with SORT, TODAY, CONCATENATE, COUNTA, COUNTIF, some of the ones dealing with time and dates, LEFT, NETWORKDAYS - and of course IF, AND, OR, SUM, AVERAGE. Turns out I don't do much math!
Awesome Chandoo! Thanks for the tips. Thumbs up!!
Greetings from Christchurch! This is very apt for the work that I do, and relevant. Thank you for the succinct advice on these 10 functions.
Very nice andhelpful content. Thank you so much
Thank you for this and many other videos. Your narration, explanation with examples are extremely helpful. Can’t be any more effective. Im in love with excel all over again now
Thank you very much for your easy to understand excel tutorials. You have enhanced my MS Excel learning a lot.
حفظك الله لنا يا شيخ على خير
All of all this video is mindblowing .. (posting comment even before I finished watching the video)
Xlookup with filter ... I really wished I knewed that before... !!
Thanks
Chandoo! You really got me with the like() function! Hahaha! You sir, deserve my like! (im already a loyal subscriber)
On a sidenote: If we already have xlookup.. do we still have a use for the index function?
Thanks for the love Kairos.
INDEX is helpful in many other situations too, not just in INDEX MATCH. For example, if you want 2nd column values of a FILTER result, you can use =INDEX(FILTER(...), , 2)
That smooth segue to the "Subscribe" function deserves a like and a subscribe.
The new Unique function - don’t know how we lived without it! Makes life so much easier.
Thanks for giving us the most common formulas to focus on. Excel is the most difficult discipline to learn among the fields of science. But you make it easy for us. .. Thanks and more power.
I thought I am the only one suffering :)
I also want to say thank you Chandoo for always including a file to follow along. Please let me know which video I can learn more on countifs date using &. Thank you so much!
See this video for more on COUNTIFS / SUMIFS ruclips.net/video/B5hayFelHDU/видео.html
Also refer to the other videos in the description. Some good pointers there.
People are probably better off using a proper programming language for most of those other functions. It's certainly safer than embedding a bunch of macros in a spreadsheet.
The best channel to learn excel. Best content 🥳
Thanks! 😃
Haha, love the LIKE() function, didn’t expect that in the list but definitely use it!
This is why I like so much pivot tables because they can solve many of these tasks in one central place
Thanks for the video Appreciate it 👍
Very useful 👌 👍 bro.
Thank you for awesome Video Sir
Best function is like()! Great videos Chandoo
Excellent💯👍👏
Nice video. Can you please come up with a video which covers the equivalent of these functions in a power query.
Thanks
Noted
Thank you so much.
Thank You Sir.😊
Great and true ❤❤❤
I knew the all functions.which you explain.and I like to watch your videos
I knew everything except edate. Maybe I knew but forgot edate. But the way you present them is still fun too watch. Also I like where you put 3 function in 1 line. I was confused whether to put unique whole outside or inside the filter function
Thanks Nikunj... Combining functions opens a whole new level of awesomeness. If you are having some trouble, watch this video as well - ruclips.net/video/Y3DEt7P62G8/видео.html
Great sir, Your teaching method is so unique. Keep it up your good work sir❤
Thanks a ton
more power sir 😊😊😊
But if you inherit a spreadsheet that’s been in use for a long time, you may not see functions like FILTER() and XLOOKUP(), so it’s best to understand other functions like VLOOKUP(), HLOOKUP(), SUMPRODUCT() and others.
Pls give me the vlookup formula to fetch last data not first data?
Liked your video just because of Like function😅
Learning new things from u
Nice video
I always wondered if all those functions were really used by excel users. Thanks for the video.
While each function has its own purpose, most of the time I use these 10. Once you are familiar with these, expand and learn other domain specific functions
Hello Chandu, Text join was really helpful to create filters in sql… was searching this function quite longtime. Thank you sir
Wow! Thanks.
I love your like function segway. Haha
Thank you very much for sharing the common formula using in excel. I am mostly using sumproduct formula for creating MIS report.
Glad it was helpful!
Okay, the accent is a bit thick, but the content is nice. Changed the vote to thumbs up.
Good video
I would also suggest if functions (if, and, or) as well as match combined with all the lookups functions! Nice video
Good options. As I have a few videos on IF, I focused on the other functions in this.
Like 👍 function was one of the most used 😁
Crisp and Quick.
Awesome Chandoo 👌
Great sir 🎉
I use ampersand, & , to concatenate fields but I'm going to start using textjoin!
6:40 hhahahaha great plug haha superb
Hi Chandoo, Can we use these important functions in Power Query?
The like function!❤️🙏
Hey Chando
Thanks for making a gorgeous video can you make series about MIS report ?
Sir,
I use 2016 version of excel. Most used functions are..
1. If
2. Vlookup
3. Index and match
4. Countif
5. Text
For 2016,
I suggest using the plural versions, COUNTIFS, SUMIFS etc.
Also, I would replace TEXT with LARGE / SMALL
Thank you for your suggestion. Will implement using these functions too.
Using the =COMMENT() Function 😉
Lol, the like() function 👌
lmao you got me there in the most important function haha!
Nice
I liked that' Like' function in which is not in Excel but on RUclips.
I was already thinking towards "doesn't he mean =IFS?()" :D
Good morning. I have a question. How do I count different words from a single column in excel 365.
You are awesome @chandoo
These different functions are used by others in different fields so still all relevant.
I need help. We already have a formula to check if the content in cell 1 matches with cell 2. Do we have a way to Spot/highlight the difference between two cell's content
Hi, By any chance do you know any website/community where I can get real life problems that are related to excel/data anlaytics? I want to practice what I learn.
No you cannot get any problem related questions on excel like sql and other programming languages.
See the homework page on my site. chandoo.org/wp/category/excel-challenges/
Also you can participate in forums like stackoverflow where many people post their queries and answer them. It will build your skills up quickly.
INDIRECT is so useful
sir can you please start series of power bi classes one by one for beginner please sir 🥺
I use Office 2021 LTSC to work with xlookup, eomonth, sumifs, index-match combined and other functions.
I'd use a Pivot table for many of these.
I've searched all over your website for the practice workbook for download. Can't find it to save my life. Can you just post a direct link to it from this tutorial?
How to remove unwanted space in one shot.. is there any tricks, can use trim, but we need to use one Cell first then drag...is there any other method to clear it one click
Thanks for the video Chandoo!
I wonder what your opinion is on the OFFSET formula?
I use it almost exclusively over INDEX as it can produce ranges as well, instead of individual cells. Also, it's formula is quite elegant and simple, with tons of useful applications.
Do you have a take on this?
You are welcome Soma.
I try to avoid OFFSET if possible. As I use Excel 365, I have been relying on the spill ranges and new functions like choosecols etc. to do similar things. If I can't use the new functions, my goto choice is INDEX. It is semi-volatile unlike OFFSET which is volatile. So your worksheets will be quick.
@@chandoo_ I see. To be honest I'm not sure about the whole volatile thing. I have heard of it related to INDIRECT, but I'm still not sure what it means.
Would really love a video on this topic! Or maybe you already have one?
Thank you Chandoo!
Read this article. chandoo.org/wp/handle-volatile-functions-like-they-are-dynamite/
I will add a video on "Volatile" functions in the future.
@@chandoo_ Will definitely do, thank you! Hope to see your explanation video as well!
Can you please make a video on, when assigning values to different (eg people) in corresponding columns, the highest value including the row should move upward automatically.
You would need VBA or somehting else to move rows.
I use EOMONTH() quite often for calculating the last day of the month, I have also been using IFS() and SWITCH() a lot to tidy up nested IF()s..
Those are some good ones Dave.
So using SORT to sort alphabetically of course only sorted by first names. Not very useful using English language names where you want to sort by surname. It's going to be difficult to combine FILTER and SORT to sort by last names but it would be elegant as opposed to splitting the names using Text to Colums (which doesn't work for all names). I'm guessing you'd have to filter by the first letter after the last space in the name and then sort the result. Possible? And would it work with a name like Quinton de Kock (cricketer) by sorting as a last name starting with a K not a d, as the prefix is not the actual surname or even more complicated Ursula K. le Guin (SF author)?
sadly, we cannot use most of fhese functions, as we need to build our excel in mind of older version that our client will most likely still use even up to 2010
Thanks for the video on the functions. I use most of them but your information regarding INDEX function has changed my perception. This function really can do a lot of things.
I have a query if you can look into this.
I am trying to arrange multiple column's (A:E) values into one (Col F) in an order. I came to know about TOCOL function but didn't find it in my Excel application.
TOCOL would be my goto option for this. Or VSTACK. I suggest using Power Query or similar option to consolidate if you don't have these functions. Save a lot of time thinking thru edge cases.
Thanks a lot. 🙂
Sir my all time favorite function is SEQUENCE
Do we even need those 10? If we take a DAX mindset of Filter, then Aggregate. I think we could probably remove XLOOKUP (use FILTER & INDEX).
Sure. Whatever works for you :)
I use these 10 most often. But again, I also use a lot of DAX.
Hi Chandoo....Is there a way to add iferror function to a bunch of cells with formulae....or one has to do manually only?
Use copy and paste option to employ this function in non-contiguous data
@@its.gurasees could you please explain this more? Thanks
@@utdkidswifeITO To select multiple non-adjacent cells, use ‘ctrl’ shortcut key for this. Hope that helps. If you still have queries, I would love to help you.
Sir, how can i get the answer if the the forth character is D then the value calculate the 2%.i.e asfd2345
Sir we have new feature in Power BI in quick measure with suggestion, wherein we write simple English and power BI generates Dax formula. Request you to prepare video on the same and help us. Thank you 🙏🙏
Why not make the function & other results on a level that 'float' above the table (the spreadsheet). Would be much easier to see than to move up and down. Elementary, MS.
*The most useful function is the Excel table. Everything you mentioned can be done easily within the Excel table.*
Tables are a must for good data analysis.
Hloo sir I do advance by your RUclips vedio so now I want work on the basic of excel so sir if u have some work excel related then sir please give me
It's the LIKE function for me 😂👍
Most used formulas include -
Sumifs
Countifs
Iferror
Match
Index
Edate
Eomonth
Vlookup
Offset
Max
I use Excel 2016. Haven’t got an option to explore the simplified formula included in office 365
Good list for Excel 2016 :)
We hardly use sumif. But the job seems decent after watching this video. I need only subtotal at end of the column.🤔🤔🤔
runtime error 1004 method onkey of object _application failed SIR HOW TO GET RID OF ERROR WHEN OPENING .xls (EXCEL 2007) File in office2016 . any possible solution
What aboud LAMBDA and her babies?
LAMBDAs are great and I have been using them often in my recent project. But I don't consider them essential at this point. I can still do the same with other functions or longer methods.
Concatenate and Xlookup I have been using for past few months. Concatenate to combine GSTN + INVOICE NO + INVOICE DATE and then XLOOKUP to retrieve the value. And of course, SUMIFS too will be in my daily menu
That is awesome Surya.
Like Function 🤣
6:44 😂😂😂
🎉
Great summary of indeed the most used Excel functions!
I have another question though if you don't mind, I'm populating item descriptions from another tab, by using a lookup function. The descriptions are quite long, and as I'm creating my own invoices like that, I'd like the text to wrap nicely, based on the lookup result. However, the alignment only looks at the length of my formula, instead of the retrieved results. How would you tackle this?
If you select the cells with formulas and apply word wrap, the result should wrap nicely. Adjust cell width and height to get the desired look.
@@chandoo_ The text indeed does wrap, but the adjustment of the cell width and height are manual. I was hoping to automate this, as for every invoice, the field will have a different length, and I want to avoid white lines.
Actually found the issue, I used merge cells, which disables the AutoFit function. I adjusted the layout and not at least it works manually by selecting all cells and click AutoFit. Next would be to automate this
What the heck?!!!
I feel if I knew anything about data or maths or excel, this wouldn’t look complex to me. 5:26
AVERAGEIF, RANKX, INDEX, MATCH
The first 3 items are easier to get out of a pivot table.
Concatenate
Hi Chandoo, I emailed you questions about your course multiple times. I am waiting to hear your response. Please confirm if you received my email with the subject "Questions about Excel school courses".