Top 10 Essential Excel Formulas for Analysts in 2024

[@KenjiExplains]

🚀Get FREE Excel chart templates from Hubspot: clickhubspot.com/y21 Correction: Profit Margin % should be gross profit / revenue NOT revenue / gross profit as I did it in the video. Sorry for the mistake!

[@meeto4084]

Hey dude, I need your help at my excel sheet to get sum products

[@renvirsiasol3947]

good morning sir can I ask aside using control f is there any ways on finding data inside a formulated cell sample inside the A1 cell the formula inside was =B1+c1+d1+e1+F1+g1 then I want to find the d1 what ways in excel can I used

[@mgangat]

Thanks so much for this very useful link and information. Your videos are very informative detailed and easy to understand. Thanks again👍

[@beniria2179]

That gives 64% 59% and 48%

[@abdullahiadamumaina2708]

good job

[@Bibibelabbb]

I want this video to burn into my retina permanently, you're saving analyst lives out here man

[@MetalBreakdown]

I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.

[@Kezinch99]

How much experience did you have when you applied? I'm wondering because I'm also looking for a job as a FA and dont have much experience :/

[@vishaljhaveri6176]

LARGE and SMALL functions were new to me. Also I learnt the SUMIFS in a different manner. Thank you!

[@stresslaxing.]

Just Amazing. You are the GIFT who was given by youtube for us. Thanks for everything Kenji

[@JohnAdair-k7j]

All 10 are very good. I especially appreciated the 2nd example for the SUMIFS function.

[@bethlehemgirma-u7j]

What an amazing video ! I have come to the conclusion that those that simplify sophisticated matter like excel are those who have mastered it to the fullest. wow! just wow!

[@christopherleinweber5455]

It’s so crazy how in depth you can get using excel , I never would’ve imagined . I remember when I first started using excel back in the third grade !

[@ozurumbanewsline]

If you have not yet subscribed to this amazing channel, what are you waiting for. Great job, man!

[@_sonicfive]

the =index(filter()) pattern works beautifully too.

[@nev0116]

This was amazing! I just got hired as a programmatic specialist and this helps so much 😩

[@hectorrenemelendez]

"I just watched Kenji's video on the Top 10 Essential Excel Formulas for Analysts in 2023, and I have to say, it was incredibly helpful! As someone who's always looking to upskill in Excel, I appreciate how clear and concise his explanations were. The formulas he covered were definitely essential for any analyst, and I learned some new tips and tricks along the way. Thanks for sharing your expertise, Kenji!"

[@saukash]

Is this written by ChatGPT?

[@lukmanadamu5778]

@@saukashObviously😁😁

[@XLLearner_Courses]

Agree. There are some basics formulas that you always use

[@mindmywordsbymeghna.5325]

Thank you kenji learning excel is not confusing anymore.I am new subscriber Love from India 🇮🇳

[@ahsantheanalyst7199]

the first formula in this video is wrong :) Profit Margin %= (GP/Revenue) . Hope it helps. Overall the iferror concept is perfect .

[@KenjiExplains]

Hey you're right! Thanks for pointing it completely missed it :)

[@Mrdezmiki]

I like that he's human after all. More relatable and projects authenticity.

[@kartheek79]

Excel has fill function if we convert the data to table that can be used instead of string function

[@ilocomocomediaentertainmen7425]

Hi Kenji... Which of your course is best for data analysis? I am trying to switch career to data analytics

[@Kumar.saravana09]

1- cost/revenue will be handy

[@ace9resistance]

Honestly all these are mindblowing. As a guy thats been using excel for my new position for about 1 year now and so many steps are tedious knowing i can implement some of these lessons is a gift from the excel gods 😂

[@shahbazyandav82]

Thanks a lot dear Kenji for your great work. If possible make a video about NPV and IRR with detailed examples. God bless you.

[@christianmayebi5096]

Best excel class ever

[@thapalakotturubhaskarreddy9609]

Sir,"Textjoin" is also a better function instead of "and" function -if we had more cells to join in case of Country, City example-Trust this Learn with Kenji sir is always value +++ added

[@rockj8197]

Great videos! Anyone have a system for recording and/or remembering these so they are easier to recall and use? I've taken lots of excel classes but remembering everything is a challenge.

[@LileeCuisia]

Thank you Kenji! This is very useful information.

[@poonamparikh5107]

Great video! Very Well Explained. Can you please show how to automate some report used for Analyst and Financial role. It will be greatly appreciated.

[@Keepcool233]

Great lesson Kenji, very well presented and simply explained

[@che7001]

I really appreciate your videos! I was wondering if you could use a drop down menu instead of typing the month or data in those dynamic formulas. Fabulous videos. They are very helpful with my college assignments. I plan to watch all of videos. Thank you for sharing your dynamic talents and skills with us! Ciao!

[@imtghanabranch]

Certainly, by using data validation.

[@m.w.6099]

Hi Kenji, great videos you are making! Love to watch.. in this Excel vid with your #1 to find your result maybe an easier formula to use is by using twice the XLOOKUP function? like =XLOOKUP(B14,B3:B11,XLOOKUP(C14,C2:J2,C3:J11))

[@Cydia0]

Nested lookups are not always a good idea and I would only really want to use them in very specific sceanrios.

[@CP-zb3ky]

@@Cydia0 I use nested lookups too when looking for two criteria, would like to know why Index-Match is superior/preferred.

[@willcarroll9762]

@@CP-zb3kyI think the index match match is just easier than two lookups. It’s just personal preference

[@CP-zb3ky]

@@willcarroll9762 I prefer two xlookups, but understand people are animals of old habits, also for older window users xlookup isn't available to them.

[@HarshitSingh-zo5mi]

You don't even have to use two xlookups but just use & in both lookup values and lookup array and it should give you the same result as Index Match.

[@gergoadorjan4259]

well... you need a bit more than these functions but your title is a very good HOOK !

[@Danielle_Scott]

Great video! I utilize most of these for my work and love them all. I didn't know about Large and Small so thank you!

[@magdahassib8659]

Thanks Kenji, you are always adding value .Thanks

[@KrisD007]

I I don't know what I'm doing wrong. My sequence looks like yours =SEQUENCE(50,,DATE(2023,1,1),7) I added the 0s but every time i hit enter they disappear. I really like that you include a spreadsheet that we can download and do with you. I'm having a hard time remembering what function, etc to use when.

[@TinaNguyen-t8m]

Thank u. It's really helpful and not even an analyst ❤

[@financnifitness2583]

Hi, actually, the first example: Profit margin is calculated by taking the GROSS PROFIT and this is divided by REVENUE, not the other ay around. Other than that perfect video! :) You are wonderful!

[@faisalym]

Thanks. You made my life easier.

[@doristhecoder765]

You are a gift Kenji! Thanks

[@Quinton-Baldwin]

Thank you, Kenji!

[@manpreetsagoo367]

Indexmatch formula is amazing. Thanks 😊

[@NatashaDiedricks]

Kenji, you rock! Thank you.

[@Amal-j7c]

Hi, very great info , im realy stuck to calculate the sales for each sales person everyday of this month . it is around 1000 tall column can you help

[@michaellawrence883]

A great video as always. I have just one question! How do you know the exact excel tool to use when you are being given a problem?

[@psychofacto7212]

Experience

[@giannisgrats883]

Many new formulas that I didnt know about! Very interesting!

[@vivianac.2574]

Awesome video! I have a question. I resolved it. I can share my Excel spreadsheet with you. However, I want to find a more straightforward formula. I have a spreadsheet with several TABs. In TAB 1, the general info, sales order, original date, expiration date, how much is still open, etc. In the TAB 2 I have a Database with details of each item in each sales order (sales order / item / Quantity / price / etc.) I want to click the sales order from TAB 1 (general info) and go to TAB 2 (sales order details) and point it. Not sure if I was clear...

[@junesechowski5932]

Nicely done. Thanks!

[@susanitaq85]

Thank you for this great video tutorial.Edate was used to specify a sequence of dates by month, what can be use for the days or years pls .thanks

[@tanweerabbasabbas649]

kevin you beauty. best thing is you always include practice file..

[@safakv60]

dude you're the best, totally time saving!

[@KenjiExplains]

Glad I could help!

[@saadatkhan11]

some very nice tricks not only in this video but ur other videos too

[@duncachinoahimiha3889]

As someone on Excel 2019, I’m infinitely envious of the xlookup function (but not enough to subscribe to 365 lol)

[@franciabowen2496]

Your videos are awesome. Thank you so much

[@RexxorDDG]

This is pretty interesting to learn while in my business stats 1 class

[@ShIrEnZeR]

Your videos are so very hopefull, thanks! I just cant understand how are you choosing the correct cells for the function using the arrows? whenever i start typing a function i cant move bewtween cells..

[@piobasartejr.7686]

I'm watching this vlog always

[@muhammadrefaay1503]

super powerful kenji 💥💫 keep going

[@kellysanesthesiatips1983]

Thank you for sharing Doris The Coder!!!

[@michaellorman448]

Hi Kenjie! Can you tell me if only free template available just for one video or I could get for others as well? 🙏

[@rwfrench66GenX]

On the Filter formula should you have it as => for the criteria in case something matches the amount being searched?

[@Kbira8960]

Concise and informative

[@Guile608]

Fantastic!!!!!!!!!!!

[@peterjarabek5541]

Hi Kenji. Thanks! For me the best is the last one: Index + Match, but the other is good as well:)

[@camridgway3862]

Index and match is now the xlookup formula I believe...way more user friendly

[@PatrickWareham]

Can you do a video explaining excel advance absolute,relative and mix reference?

[@HAYDENGUANSI]

Love love love KENJIE

[@hibaadnan9983]

Thank you for the video. I'm preparing a teaching program where I need excel to calculate each letter "A" (section A, B or C) as a number and do the total sum of "A"s as numbers?

[@kennethmutai2967]

Thank you so much.

[@BenDieselBasicAutomotive]

Thanks again, Professor.... More power....

[@Mark-gz9si]

thank you kenji

[@toshiyo822]

Sumifs with the wildcard is so cool. What do I do if I want to include a word but want to exclude it if the cell includes a combination of two certain words? For example I want to sum if the description includes Amazon but want to exclude it if the description includes both Amazon and Walmart?

[@Diazkarinar]

5:24 I get an error when I use this formula, it gives me a number value. I followed you step by step

[@wilmaostman7537]

Love your videos, thank you!!!

[@bspraveen9364]

Very useful 👌 Thanks for the information

[@28dwhite]

Hi Kenji, do you think index match and xlookup are much the same?

[@swapnamali3860]

Thanks for this useful information Sir can we use data validation in here?

[@annieo.9887]

Thanks Kenji

[@jhonrayocampo7511]

Ang galing ma try sa office hehehe

[@debbietownsend1673]

YOU ROCK!

[@immaculatendia7626]

very insightful, thanks

[@MK-fq3fk]

Great job Is there a formula to inclune axes of a chart. I need to plot on axes with an angle ie not vertical and horizontal. Thanks

[@thusithadasanayaka4329]

thanks for explaining

[@vashishtsharma7217]

filter function, left-mid-right, sequence, iferror, concatinate, large-small vs min-max, index match

[@alblanquicett]

this is beautiful thank you

[@questionresearch8721]

Thank you.

[@sarathrupasinghe6240]

Nice, thanks Kenji.

[@mompreneurlife]

❤ nice video! A lot of people don’t use excel anymore however, I am a fan still excellent from your list. They’re very useful❤.

[@AlainW-xm1rh]

Hi there. What are they using instead?

[@ohdjrp4]

Hi, since when did those "lots of people" stop using Excel? and what they are using now?

[@AlainW-xm1rh]

@@ohdjrp4 Exactly... 😀

[@ggrocketleague9098]

Damn, I have the same question. Is there a better way?

[@AlainW-xm1rh]

@@ggrocketleague9098 Nah, this is somebody fishing for customers... :)

[@Jenny-pc7ge]

Great to have your video , that is exactly what I am looking for, thank you so much.

[@charlenem6040]

this was great thank you!

[@ctowtf]

If you can put the formula(s) by clicking the "fx" and using the the "function" box, would be easier for ordinary people like me (as the function box will gives you the explaination re what to write).

[@doug1307]

Indirect function is very useful too.

[@MariaMarquez-vw3yn]

Hi. Kenji, Kindly show in youtube how to be in hubspot along with downloading the free templates please. How to retrieve the sample templates please?

[@nobogurus]

Can you show us please how to analyse livelihood coping strategies in excel, how to get percent of households in stress, crisis and emergency categories and the average for the region.

[@kimsclub3860]

Great Thanks

[@flourishwithMJ]

Thank you

[@Aroooojjjjjjj]

Can u please teach how to use Google USD exchange rates for other currencies as real time currency in costing in excel to convert In one go... Please

[@NikitaDas-v7o]

Really amazing videos !!!

[@brianbarns8675]

Great Video. In your example of using the large function at 6:50 in how would I pull the top 5 results in column C along with the names that go with the result from column B so it shows both in column F and G. Also my source table is on another work sheet in the same work book. Thanks

[@KonstantinosVamvakousis]

Great Video!

[@seanolanrewaju9]

I like this guy👍🏾

[@sorosekaewmanee1845]

Thank you 😊

[@brandondinshaw3112]

If I wanted to filter between 2 numbers how would the formula look like? Example, I have a list of salaries and want to filter to between $40,000 and $80,000?

[@HydeMyJekyll]

While these are all useful formulas, they are definitely very basic ones that I'd expect most people to already be familiar with. I use all of these at some point during a typical work week, and my job doesn't even require the use of Excel aside from extremely basic data entry. Almost every business case I've found that needs to be addressed with Excel involves multiple workbooks on various network drives, in addition to .csv exports from other programs such as SAP. I need to work on how to automate some of this stuff, such as scheduling the data exports and choosing the saved file name and location, opening and manipulating the exported data, adding the new data to an existing workbook, and having a dashboard read that file to stay updated. I'm also having problems deciding how to handle some of the existing files, as they just add another column for each day's data and some of these workbooks have well over a thousand columns of data and hundreds of thousands of rows, with most cells filled with external references.

[@zolzvevo]

I really needed the magic in 9:46 😊

[@Kirkland-rv5jf]

Kenji, I have tried and tried but I cant get my head around how the solver function works. Could you do a video on that?

[@arifshaikh8937]

I am in quality team wherein in have quality of my projects in 3 categories. critical major and minor and SLA for critical is 99% , for major 98 and minor 95 percent. I have such data for about 100 plus projects and I wanted to create an attractive dashboard to present that data where I can highlight top performers, no of project meet quality, no of project miss the quality etc. Please create some video around this on SLA and kpi

[@hadiekhtiardar]

very useful - thanks