5 Advanced Excel Formulas You Probably Didn't Know!

Подписаться 522 тыс.

Просмотров 215 тыс.

50% 1

Learn 5 advanced Excel formulas to become an advanced Excel user.
🔥 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/64ba5d...
In this tutorial we go over 5 advanced excel formulas. The first function is the TEXTSPLIT, where we use several delimiters to split columns of data. Second, we go over the SUM and OFFSET combination using a nested formula to make a dynamic sum range. Third, we learn how to use the XLOOKUP with the wildcard feature to detect inconsistent data. Fourth, we use the INDEX MATCH with the ampersand (&) to use multiple criteria to lookup values. Finally, we learn the SUM FILTER combination which is most useful to sum ranges with duplicate filters.
LEARN:
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🔥 Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Level 1 ADVANCED TEXTSPLIT
2:28 - Level 2 SUM OFFSET
4:19 - Level 3 XLOOKUP WILDCARD
6:53 - Level 4 INDEX MATCH AMPERSAND
9:05 - Level 5: SUM OFFSET
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

Опубликовано:

15 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 153

@KenjiExplains 10 месяцев назад

🔥 Check out our Complete Finance & Valuation Course! www.careerprinciples.com/courses/finance-valuation-course

@joshuasarpong1194 10 месяцев назад

Kenji what's your name on LinkedIn?

@khal29 10 месяцев назад

Christ 🤣 Please tell me no one’s been dumb enough to buy that?

@martinmarco4814 6 месяцев назад

@@khal29 Hi I have filterxml and not the normal filter you are using cuz if I apply the logics its coming to #VALUE please advise thanks

@benjamingreer1660 4 месяца назад

Data validation would probably be quicker on the first one.

@excelgazialimuhiddinhacibekir Месяц назад

@@martinmarco4814 Your EXCEL 365 New Dynamic Array Functions have not been activated apparently. Check your Office 365 account info and subscription.

9 месяцев назад

You are a born video instructor. Your voice is engaging, your speed with the mouse is not too fast and not too slow, and your progression made sure I did not get lost. I am absolutely coming back to watch this again when I need another refresher. Subscribed!

@KenjiExplains 9 месяцев назад

Thank you for the nice comment! Made my day :)

@bobby4242 9 месяцев назад

Great job building the complex formulas in steps with great detail.

@alexrosen8762 10 месяцев назад

Extremely useful tutorial because of the different hacks in for example the filter function when in combination with other formulas can do magic. A lot of tutorials shows the filter function in it's basics which is good but can be quite repetitive and tedious, especially when you are a learner who wants to take the next steps in the learning curve.

@nurulsarifah3702 6 месяцев назад

For number 4, we can use double xlookup ;) =XLOOKUP(C3,B10:B29,XLOOKUP(C4&C5,C8:K8&C9:K9,C10:K29)). I am a beginner here. I have watched +10 of your videos and found that double xlookup = index match. Thank you so much. Your videos are very helpful and you are truly amazing! Please keep it up!

@expertgaming5146 5 месяцев назад

Exactly xlookup is easy than index match isn’t it ? (Im a beginner aswell)

@Servants_Heart 9 месяцев назад

Very nice. I had to learn excel before there were videos, classes, and manuals. I officially have subscribed.

@manosvenianakis9323 10 месяцев назад

Excellent video and excellent presentation! Thanks!

@hayderrazakabd 3 месяца назад

For video number 2, please calculate the profit if the droplet by month. All the best

@vikramadityarathore97 10 месяцев назад

Outstanding video mate.very helpful

@SaiSanjeevie 7 месяцев назад

Really Super man. Clear and Beautiful explanation of new subject in Excel...

@TheFlying-Cyclist 10 месяцев назад

Hey Kenji, great content you are posting. In fact, I ended up subscribing to one of your courses after a video I watched a while back. I'm curious, though, how would one start freelancing with the skills learned from your course(s)? I am taking Excel for Business and Finance and Power BI for Business and Finance. Looking forward to hearing from you and thank you in advance.

@owrsrlt9062 3 месяца назад

Thank you so much Kenji; your explainations are extremely clear, you give us the examples files. I don't how to say that once and of all: THANK YOU !

@KenjiExplains 3 месяца назад

thank you for watching :)

@1015Gao 10 месяцев назад

Thanks for the vid, very helpful

@ruminsb330 10 месяцев назад

Nice Sir. Your Explain Very Good. Thank Sir...

@mohamedsoffar4434 9 месяцев назад

Really cool what your sharing ,this is amazing ,Thanks Dear

@sevetp 9 месяцев назад

Great video :-) For #4, I would suggest this formula : =SUMPRODUCT((B10:B29=C3)*(C8:K8=C4)*(C9:K9=C5)*(C10:K29)) whose syntax is sumproduct((criteria_range1=value1)*(criteria_range2=value2)*[...]*(value_range=range)), where "*" stands for AND and "+" stands for OR. Note that you need to end your formula bay value_range.

@excelgazialimuhiddinhacibekir Месяц назад

Excelllent! I guess this is Problem #5, btw. As a longer solution alternative to Kenji's "Two embedded FILTER() functions" solution and to your ultra efficient "SUMPRODUCT()" solution, I would suggest the following "CHOOSECOLS() and a single FILTER()" solution: *=SUM( CHOOSECOLS( FILTER( RevenueData, Managers=H4 ), MATCH( I4, Years ) ) )* I resorted to named ranges of cells for better readability of the formula.

@Spreadsheets_Plus 10 месяцев назад

Nice one kanji and thank you so much for this. And the XLOOKUP function as well can perform the same advance index match which you did. Great work man!!!!!

@KenjiExplains 10 месяцев назад

Thank you!!

@Spreadsheets_Plus 9 месяцев назад

Good morning Kanji. Trust you are doing well. Please I am putting together a contribution sheet for a colleague and it’s expected that every month the value of to be contributed is 2000, please I need a formula that can automatically add that 2000 to the previous value and subsequently as the year goes on it should have automatically reached 24000. Thank you

@camlex6310 10 месяцев назад

Great video thanks!!

@joshuasarpong1194 10 месяцев назад

Thank you Kenji really helpful

@KenjiExplains 10 месяцев назад

Cheers Joshua :)

@razamoulvi8801 10 месяцев назад

Thanks for the video❤

@nihonpark7058 10 месяцев назад

THANKS KENJI FOR THE LAST FUNCTIONS 😍

@chrisgray8774 9 месяцев назад

I have a table similar to your example #4, only the first two columns are row labels and the top single row is column headers (quarters 1 to 4). I couldn’t get the formula to work, so I replaced the dataset array with INDEX(dataset,0,MATCH(column_header,column_header_row,0)). This made the array a single column. I used the & with the first two columns, and it worked. Thanks for the neat tricks. It was a good presentation.

@user-wo3mu9bc4j 3 месяца назад

Alonso is definitely Dr! :)

@hiprateek025 5 месяцев назад

Very Useful! 👍

@Saadi_Lyfe 5 месяцев назад

My God the last one made my life so easy.. Thanks.

@ashishshiriskar123 8 месяцев назад

sir as usual you are superb

@standoffsniper5693 10 месяцев назад

You don't need to put 2 filters,you can write it like this =Filter(array,(name=billy)*(year=2021)) of course the name and the year can be referenced

@Samiksha_W 10 месяцев назад

Did you try on Excel?? Because I think it takes either only column criterias OR only row criterias. Not both at the same time.

@TheAlwaysGoel 10 месяцев назад

@@Samiksha_Wyes you are wright

@timisolakhadijah6939 9 месяцев назад

@@Samiksha_WIt works

@yibrahim7 9 месяцев назад

@@timisolakhadijah6939 How, can you elaborate please?

@casmartin790 9 месяцев назад

I was thinking this could all be done with a SUMIFS function instead. I was working on a sales report as practice and had to do this exact same thing for sales, profit, cost, and units sold and I just did SUMIFS for them all and then data validated the countries. I had to do the same for all the sales people also and I used the same formulas with a bit of extra coding or whatever, but it was exactly the same

@motopelamohapi4152 10 месяцев назад

Thank you, Kenji

@KenjiExplains 10 месяцев назад

Thank you for watching 🙏

@How_2_Excel 10 месяцев назад

Great video

@juanparada3387 7 месяцев назад

Useful stuff!

@kirant96 9 месяцев назад

4th one is quite useful

@user-cf1rx9zy2z 3 месяца назад

Thanks sharing

@shobhit_nair 10 месяцев назад

Forumla 1 Drivers😂That was a nice touch!

@sahilpatil9003 10 месяцев назад

Thanks

@yemancotv6372 8 месяцев назад

great one

@franzzzis 10 месяцев назад

Your explanations and examples are excellent, which are also suitable for beginners with Excel. There are countless tutorials on Excel, from simple to highly complex, but only a few are really good, your videos are definitely among the top 20 for me. compliment

@KenjiExplains 10 месяцев назад

Thank you for your comment, very nice of you!

@debbielaubscher4983 10 месяцев назад

I agree. Your explanations are clear and easy to follow. Thank you for sharing your talent with us.

@mostafaiffakh9358 10 месяцев назад

Thank you Kenji , very useful Please , how about more then three criteria.

@mostafaiffakh9358 10 месяцев назад

It's ok now thank you

@mos3863 10 месяцев назад

Kenji can you show the substitute function for textsplit as i use office 2021 its doesn't have that function it would really helpful for me

@user-jg4sf1uu1u 4 месяца назад

Please make one video for data anyltics full course

@jitnagar7735 10 месяцев назад

On the 4 and 5 formula I’d prefer to have used Sumproduct

@ahmedkamon9123 10 месяцев назад

Very impressive. I have a column contain text or numbers , How can I search a certain value in that column in a another table contains columns with text and numbers

@willzinner8813 10 месяцев назад

youre back!

@KenjiExplains 10 месяцев назад

yes!

@lifeandmoney8073 Месяц назад

Awesome

@dannya1209 10 месяцев назад

Wow, this is strangely what I needed today. I'm a basic Pivot Table + XLOOKUP/SUMIF guy. I knew there were these other formulas, especially #4 AND #5, but couldn't quite remember them, been doing the same ol same ol. The INDEX and FILTER is an EXACTMATCH of what I needed today.

@Besavant623 8 месяцев назад

Sir, Can you please make a video regarding a excel workbook in which there will be two sheets in one of the sheet there will be database about our invoices like invoice number, invoice date and invoice amount. the another sheet will ask us to enter a value which we will enter manually but after we input the amount the second sheet will show us all the possible combinations of invoices which total upto that amount which we entered. i have been facing this problem during my work and for this i have to manually search and check which invoices total upto certain amount and then allocate payment to them. but i hope you can help me with my problem and make video for teaching how to solve for this issue.

@Alexthesparker 9 месяцев назад

SUM FILTER 🔥🔥🔥

@mohdsalman01 Месяц назад

Formulas with formula 1 drivers makes absolute sense

@marninei 9 месяцев назад

The use of the Offset function was effective but did not utilize the full functionality of the formula itself. The syntax of the formula is OFFSET(reference, rows, cols, [height], [width]) so you should instead type: SUM(OFFSET(C10,,, 1,C2)) This way you don't need to subtract 1 from the cols, your referencing C10 just once and you're using the formula to its full capacity.

@erictchetche5890 9 месяцев назад

Brillant

@AGaylon0618 10 месяцев назад

Hi sir, how much is the cost to learn for data analyst and how long? Thanks

@smilejs 9 месяцев назад

I feel the sum filter could be replaced by sumifs which is more easy to use

@chetanmaurya8557 18 дней назад

can we use sumif and offset together? as I am not able to use sumif and offset together

@none4509 10 месяцев назад

Hi Kenji, for power excel users do you recommend Mac or Window’s PC?

@KenjiExplains 10 месяцев назад

Definitely windows as the shortcuts are better!

@MrDhunpagla 10 месяцев назад

Kenji Rocks 😊😊😊I 😊

@nidohadi 9 месяцев назад

Hi , 1st i am very much thank full to you for sharing you precious knowledge Thank you so much 2nd. I am Naveed Ahmad from pakista. I am learner from Chanels RU-vid, face book I found your Chanel rich with knowledge

@subhashini4242 7 месяцев назад

If we have same names in first column with different supporting data in other columns how to take full details with vlookup formula

@surajD21 8 месяцев назад

I have very large data with conditional formatting. I want to paste it in another sheet without values but I need only colours. Can you tell me how??

@cshahed 10 месяцев назад

Awsome

@KenjiExplains 10 месяцев назад

Thank you!

@jonathanogwumike6088 10 месяцев назад

Nice video Kenji. Database functions, DSUM should solve the last one too!

@gargbhavin6956 7 месяцев назад

HI,I WANT TO SHOW MULTIPLE MATCH RESULT BY USING INDEX MATCH METHOD IS IT POSSIBLE ?

@colinekszczecin 10 месяцев назад

If I am not mistaken, you can use Filter for multiple criteria (instead of nesting one filter in another). So in the last example you could do: FILTER(C4:F25, (B4:B25=H4)+(C3:F3=I4)) Basically "* = or", and "+= and" (or the other way around, I can never remember and can't check in excel just now).

@Samiksha_W 10 месяцев назад

I think it takes either only column criterias OR only row criterias. Not both at the same time.

@Samiksha_W 10 месяцев назад

Also + is when you want any one condition to be satisfied and * when both conditions to be satisfied for filtering.

@colinekszczecin 10 месяцев назад

@@Samiksha_W I will try tomorrow when I am at work and have access to Excel

@Samiksha_W 10 месяцев назад

@@colinekszczecin cool:)

@davycruz5796 19 дней назад

Can someone please help me? I have an warining message witht the sumoffset formula and can't get any further. After doing this formula "=SUM(C10:OFFSET(C10,," each time i want to select "C2" i get the message that there is a problem with the formula you type =1+1, cell shows 2 etc.. I can't proceed 😢

@beshboushaghatta9993 8 месяцев назад

I want to ount anumber times a numberthat is cubed..then divede it on a number..how do I do that

@Akkk-mu8nr 4 месяца назад

Microsoft Office activation failed sir please help Osppream file delete by mistake.

@mohamednasser2865 Месяц назад

The TEXTSPLIT function is only available in Excel for Microsoft 365

@sagar72721 10 месяцев назад

these functions r not in my excel sheet ..how to use them,

@akens1964 8 месяцев назад

I have excel file created in 2016 version, forgot password for this file, I tried lot of options through internet, but nothing helpful to open file, can you help me

@ItsjustmeAR Месяц назад

#4 why not use sumifs? #5 you can use multiple criteria by ()*() the parameters within the filter formula…..or use sumifs again.

@ragexiii 10 месяцев назад

is it possible to use sumifs for the 5th solution as well?

@ragexiii 10 месяцев назад

tried it.. it didn't work.. thanks for sharing this kenji!

@How_2_Excel 10 месяцев назад

You can use SUMPRODUCT as an alternative =SUMPRODUCT((B4:B25=H4)*(C3:F3=I3),C4:F25)

@ragexiii 9 месяцев назад

@@How_2_Excel ah alright.. awesome.. thank you..

@karthikkayan8049 9 месяцев назад

Sir 2016 not working match cretriya &

@4061earthabcdesong 7 месяцев назад

(For level 5... Filter function) Sorry sorry for being a sleuth, but i tried to add up the sum using a calculator, there're discrepancies... 1,087+1,239+1,413+1,611 should be 5,350 (not 5,351） Also, 1,250+1,425+1,625+1,852 should be 6,152 (not 6,153) Why is that ? Please, Kenji please look into it 😊

@samborn1 10 месяцев назад

Kenji I have a problum : I have a sheet where I have lots of PAN card Txn details in whole Financial year. Now I have to give every pan card user a certificate for his whole year txn details for income tax purpose. Now I use vlookup but this formula give me first find value. I use sumif but this formula give me total value of txn. I want Pan card user number of txn in other sheet. Pls solve my problem

@AmpersAndAI 10 месяцев назад

I know you asked Kenji, but I might be able to help however to effectively solve your problem set, I would need to see what the data sheet looks like as the information you provided isn't substantial to provide a successful solution to your issue. Do you have discord?

@samborn1 10 месяцев назад

@@AmpersAndAIThanks for your concern and support. I m glad to see your helping nature. I have data sheet but according to company rules I m not share data with out company.

@peterakeredolu3346 10 месяцев назад

Thanks, Kenji for this video. The presentation was both enlightening and clear, and I truly appreciate the effort you put into it. Having seen this video, I'm interested in understanding if it's possible to use any of the functions to construct a template as demonstrated in another video I came across. Here's the link for your reference: [ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-DPGjhadxiTs.html]. I tried reaching out to the original creator of the video for clarity on the template construction but was unable to procure a detailed response. I would be immensely grateful if you could provide guidance or elucidate the process of building a similar template, specifically for tender evaluation. Your expertise in this matter would be invaluable. Thank you for your time and consideration. I eagerly await your feedback.

@levangugulashvili1296 7 месяцев назад

Formula 1 fan)

@luckmanmoulana 8 месяцев назад

Hi, Chapter 5 is SUM Filter not SUM offset 9:10

@nidohadi 9 месяцев назад

It is request humbly please my respected

@roywilson9580 9 месяцев назад

Interesting video, though not particularly advanced. In the last example using 2 filters to find Billy's revenue is a little over the top for the required result. I would not have used filter as a simple SUMIFS would have produced the same result; also if you want to apply 2 range filters to the dataset you can add the criteria in sets of parentheses and use the '*' or'+' operators to filter with 'and' or 'or' functionality, this would make the formula far easier to read as the logic is easier to follow.

@KenjiExplains 9 месяцев назад

Hey Roy, the SUMIFS unfortunately doesn't work. That's because the criteria ranges and the sum range don't have the same number of rows and columns (feel free to try it and let me know if otherwise though)

@roywilson9580 9 месяцев назад

Yes, sorry! My bad, I didn't notice the shape of the data watching on my mobile phone on crowded bus! You are right and I apologise for my error

@AmpersAndAI 10 месяцев назад

Are you also going to be competing in the Microsoft Excel World Championships this October?

@akens1964 8 месяцев назад

Hello

@eniseymenkuzu9019 10 месяцев назад

Hey, when will you talk about the real company Cannafarm Ltd that brings profit?

@surya_vj20 Месяц назад

Textsplit formula not showing in my excel😢

@SeriaYT-FORAX 19 дней назад

There's no textsplit in my excel why

@LNT379 10 месяцев назад

It must be hard to be in IB and make these videos

@newmhelemish 9 месяцев назад

The 5th one, why did not you use SUMIFS ?

@philipverhoeven5545 6 месяцев назад

Level 5: isn't a pivottable easier to use in this scenario?

@karthikkayan8049 9 месяцев назад

I am written correct formula I am not get result why??

@pairenu 7 месяцев назад

Why not just sumifs?

@Y100001 9 месяцев назад

"A new function in Excel...". In WHICH version of Excel does it become available?

@martinmathew3554 7 месяцев назад

Dont mind....but its ikd methods

@ericssontest2332 9 месяцев назад

@joesmith4254 7 месяцев назад

Level 5: Easier to use SUMPRODUCT formula.

@jsrjhamitrawat 8 месяцев назад

Jai shree ram jai hanuman baba 🚩🚩🌺🌺🌼🌼🍌🍌🌾🌾🙏🙏🙏

@nishanth4323 10 месяцев назад

Pin my comment if I'm first 😁✌️

@KenjiExplains 10 месяцев назад

Hahaa very fast!

@nishanth4323 10 месяцев назад

@@KenjiExplains 😜

@nidohadi 9 месяцев назад

Please better your video to view Not very much clear for watching Little bit problam to watch Even i zoom it Resulting pixels breakup

@tymezundepmc 9 месяцев назад

This is too much

@babimeimirtafil8410 10 месяцев назад

Are cryptocurrency and business incompatible? I think youre not keeping up with the news. While you thought it was impossible, some enthusiasts from Cannafarm Ltd integrated cryptocurrency into the production of medical cannabis. What do you say now?

@slenderm-uc8141 10 месяцев назад

Wait, do you think cryptocurrency will crash? I dont think so. More and more companies are integrating cryptocurrency into their operations: Amazon, Cannafarm Ltd, Burger King, even Starbucks, dude!

@AmpersAndAI 10 месяцев назад

Ever since Ethereum went to proof of stake, it has been dead and will stay dead until an identical crypto emerges...