TOP 10 Excel Formulas to Make You a PRO User

Рет қаралды 728,260

Күн бұрын

👉 Learn the top 10 most useful Excel formulas using real-life examples.
Get 25% OFF Financial Edge using code KENJI25: bit.ly/3xNV4VW
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/62ac88...
In this video you'll learn all of the essential excel formulas ranked from easiest to hardest.
In level 1 we'll look at the sequence formula, follow by string functions such as left, mid, and right.
In level 2 we'll go over date functions like weekday, weeknum, or today, alongside an IF statement.
In level 3 we'll go over advanced IF statement formulas such as the SUMIF and the SUMIFS
In level 4 we'll go over vlookup (part of the lookup family) and the IFERROR
In level 5 we'll go over the most complex formulas which are going to be the xlookup, and the index match.
To follow along, you can download the free excel file above!
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
SOCIALS:
📸 Instagram - / kenji_explains
🤳 TikTok - www.tiktok.com/@kenjiexplains...
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Level 1
2:27 - Level 2
5:37 - Level 3
9:29 - Level 4
12:15 - Level 5
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

Пікірлер: 362

@KenjiExplains Жыл бұрын

Get 25% OFF Financial Edge using code KENJI25: bit.ly/3xNV4VW

@zachjo1457 Жыл бұрын

Hi Kenji, does this course cover PowerPoint for investment presentations too?

@blaiseization 11 ай бұрын

Hi Kenji, on the excle sheet when I was practicing there was an error and I don't know where it originated from. My calculation for time of arrival was showing show negative all the way down. e.g -225 What could be the cause of this? Thank you again for your cooporation

@basilabedallah5797 Жыл бұрын

This video is so underrated, it should've reached 100000 views by now

@KenjiExplains Жыл бұрын

Thank you!! Hopefully does haha 😆

@basilabedallah5797 Жыл бұрын

@@KenjiExplains good luck and keep em coming 👍👍👍👍👍👍

@Thetiredman Жыл бұрын

× 5

@tapiwanalwimba6065 11 ай бұрын

So true

@4061earthabcdesong 7 ай бұрын

@@KenjiExplains Thank you so much, Kenji 😊😊👍👍👍 You're our HERO 🙇🙇🙇🙇‍♀️🙇‍♀️🙇‍♂️🙇‍♂️🙇‍♀️🙇‍♀️🙇‍♀️

@Nerijus.. 10 күн бұрын

@KenjiExplains The content you produce is very practical and useful for working with finance. Thank you very much for sharing. I have a question, I am interested in the LBO valuation model, can you create a video on this topic? Thank you in advance.

@ComboJoe84 Жыл бұрын

Thanks for the video. A few comments: you can use CTRL + ~ to toggle show formulas on and off if you prefer keyboard vs the mouse click option. I would say that there is a level 6 or higher lookup formula that was not mentioned. Its a combination of indirect + index match. This lets you dynamically select the array in question as opposed to having to choose the array manually. The formula would look like index(indirect(concatenate("C", match(),":","C", match()), False),match(),match()). In this example, you can specify criteria by dynamically creating an array address and then looking up a value within that address based on separate criteria. It's definitely a complex scenario but I've come across this before and needed to break free from hard coding the initial array of the index.

@salala182 Жыл бұрын

I think the formula you have written is something I'll have to work on to understand!

@joukenienhuis6888 Жыл бұрын

You say match() in your formula, why isn’t there anything filled in?

@roomtemperature7096 10 ай бұрын

@@joukenienhuis6888 it's for your data

@pandaoflegend1210 Жыл бұрын

Hey a few things to try. Xlookup replaces ALL functions of Vlookup and IndexMatch and Hlookup, so if you are using any of these functions in any way Xlookup can do it. If you want to look up 2 values you need to use &. Example =XLOOKUP(lookup_value & lookup_value, lookup_array & lookup_array, return_array,””,0,1) this would be used for a vertical data set like in your example. You can also have 2 lookups used in a table format to return the cell in the table where the two cells meet at. =XLOOKUP(lookup_value1,AH3:AH13,XLOOKUP(lookup_value2,AI2:AM2,AI3:AM13,"",0,1),"",0,1) I left the cell references in, but you can get the picture. Also don’t forget about returning non adjacent rows =XLOOKUP(lookup_value2, lookup_array,INDEX(return_array,SEQUENCE(ROWS(return_array)),{1,3,4}),"",0,1) These are just a few examples of what you can do. Great video, but I have to admit that I was more then a little upset with the title of the video being “Make You a PRO User” and then not to learn anything new. I am an average excel user so ….😅

@andreugiro2807 Жыл бұрын

WOW KENJI! I'm improving a lot my EXCEL skills thanks to your courses. My workmates are hallucinating how fast I apply formulas and how quickly I finish my assignments! CHEERS!

@doctordan1668 Жыл бұрын

They don’t k know Kenji

@AlexisKasperavicius Жыл бұрын

Careful! Management will often get rid of them, give you double-work and pay you the same! That's when you need to become a consultant! :-)

@user-rj8bw7vf3q 3 ай бұрын

You by far have the best tutorial videos. Thank you for providing so many helpful videos.

@yan5699 Жыл бұрын

Thanks Kenji! Really did not expect Index -- Match could be used so efficiently. Learned another tip for except from you today!

@sarazafar849 17 күн бұрын

I love your videos! Learning excel for free at home and I feel so confident!

@susanmorris1007 Жыл бұрын

Thanks for the course. Didn’t know the xlookup. You are a clear and straightforward instructor.

@inquisitivenut2937 8 ай бұрын

I can't thank you enough. You break each point down and put it in context. Although it's not rocket science, some tutorials make it very much so!

@vikramadityarathore97 Жыл бұрын

Hey Kenji amazing content as usual. If you could make a video on how do we take assumptions in financial models , it would be helpful?

@KenjiExplains Жыл бұрын

Noted thank you!

@gaiawolfwitch Жыл бұрын

Thank you so much for sharing this, it is about 25 years since I did my Excel training, so this has been a brilliant refresher; as well as learning so much more. Many blessings.

@UKVampy Жыл бұрын

Always learning new skills and tricks in Excel, since I started with spreadsheets back with Lotus 123 rel 3.01 a long time ago, and back when you were limited to a lot less columns and rows. I used to use the string functions to extract data from a single column that I stored information in.

@nandan8811 8 ай бұрын

Thanks for the bonus tip. I am beginner it helps a lot for me 😄

@minukaasel 6 ай бұрын

Wow! Bonus tips are life savers when working with complex project files. Thanks!

@debrastewart-leigh8845 Жыл бұрын

This video was outstanding! We have been using some new programs that are adding more complex material, and this was so helpful in ways to assimilate the data. This is useful for beginners or intermediates who need some refresher training. Thank you!

@carmenchen3588 Жыл бұрын

I was taking the excel course for the exam and this definitely helps reviews the formulas again so thanks for the video!!

@elmerelmago 4 ай бұрын

I really need a 1 hr video of you explaining these excel analysis. great job bro and much love from kenya

@mauriceprice4211 Жыл бұрын

Hi, I didn't know the bonus tips. Really useful. I knew a few of the formulas. I knew of them all but hadn't had the need to use them. Now I can see that they would be useful in my work: especially "Index - Match". Thanks

@JohnJames376 Жыл бұрын

Thanks Kenji, great video. You make complex formulae easy to follow and understand.

@damontawarnermadden1811 11 ай бұрын

Thank you, Kenji! Insightful, clear and concise.

@AJA20132 Жыл бұрын

I am 46 and still learning excel, you have been of immense help. I knew some of these formulas but not all. Thank you kenji

@KenjiExplains Жыл бұрын

Awesome to hear Antony :)

@judithbredy5985 9 ай бұрын

I love how you explain everything. Easy to understand. Thank you,

@constantinosmavris 4 ай бұрын

Excellent work, one of the most helpful excel tutorials i've ever seen.

@markellis6887 Жыл бұрын

Hi Kenji, would love to see a video following up your one on PE. Could touch on carry, co-invest,waterfalls, etc.

@manjiolaa2362 Ай бұрын

Kenji, You are a very good teacher /lecturer and a slick marketer. Wishing you all the best

@lp0896 Жыл бұрын

thanks! I didnt know the xlookup and was always confuse on how to use index match. it was very clear

@touficelhajj3329 Жыл бұрын

Thank you so much for this, it was incredibly useful and well made. You're a great teacher!

@KenjiExplains Жыл бұрын

Thank you!

@andymarci6766 Жыл бұрын

Thanks Kenji! Very useful content!

@gitali Ай бұрын

Wow! Great video. I knew some of the 1 to 3 stars and vlookup formula. Very useful video as well as bonus tips. Thanks for sharing!

@rwfrench66GenX Жыл бұрын

Great video, thank you! I’m 57 and I used Lotus 1-2-3 on DOS where you had to tell it how many rows and columns you wanted. I’ve been using Microsoft Office since 1998 and the formulas and features that Excel has is always growing, but it continues to be glitchy. I have to use the Trim(Substitute formula to convert cells to where I can get rid of leading spaces and format them as currency or whatever I want. Your channel has great content but I’m pretty sure you’d get extra subs and a lot of views if you did a video on the 10 best Excel workarounds and how to auto save and find xlsb files that aren’t in Recover files. Those are the things that save time and frustration!

@james-sk1si 3 ай бұрын

Very useful video thank you! I do use all these functions for my accounting job

@shabchique7149 Жыл бұрын

Thanks so much Kenji, you make it look simple, easy to understand. 👏

@KenjiExplains Жыл бұрын

Thank you for watching!!

@Javas52 4 ай бұрын

I want to clean data based on calculated values and highlight them. For instance when a cell value is devided by 3 it should return a number without a coma. E. G 15/3 =5 "don't highlight. 14/3= 4.6 '' highlight"

@beaschofield2966 Жыл бұрын

Thank you, its the first time ive understood some of the more complex formula's

@YohanChehadeh Жыл бұрын

Hi Kenji, your videos are so great and instructional. I really appreciate your efforts and your sharing. Could I ask you please to include some videos related to Insurance companies business, cash flows, Income statements, balance sheets, actual vs. target, sales people tracking.... thanks a ton in advance.

@ceterisparibus8966 Жыл бұрын

CFBR

@henryvelasco9860 Жыл бұрын

Hi Kenji, I am learning a lot from your video tutorial. I appreciate your content or subject matter. Another thing is the courses you introduced where we can enroll to. These are great concerns to increasing knowledge.

@notesfromleisa-land7893 Жыл бұрын

Thanks for your video. I don’t use index match but the others are frequent flyers. Xlookup is a great replacement. Prior we had to do helper columns if our data was was in left to right. So many new features to include power Query which is a godsend. Nice job on your channel content.

@joukenienhuis6888 Жыл бұрын

I recently discovered power query too, it is indeed a time saver too

@first-j1020 20 күн бұрын

@kenji can you show how to create a production process yield report chart?

@bthogo Жыл бұрын

This is so awesome. I knew a lot of the formulas but not all. I will definitely watch this and make notes. @kenji you should have a PDF summary for all formulas covered.

@KenjiExplains Жыл бұрын

Thank you Brian, noted the suggestion :)

@bthogo Жыл бұрын

@@KenjiExplains Sell the summary for 2-3 dollars. You have many viewers who benefit from your videos. You should benefit as well. If you manage 200,000 sales by the end of the year at 2 dollars each…quick 400? Food for thought. I would gladly pay two dollars for this video’s summary.

@GraceQiu520 Жыл бұрын

Thank you for showing the index match. That is. Exactly what I need.

@KenjiExplains Жыл бұрын

Great to hear!

@gretchenmedado2209 Жыл бұрын

THANK YOU SO MUCH SIR KENJI. MORE STRENGHT TO CREATE INFORMATIVE AND VERY HELPFUL VIDEOS REGARDING EXCEL!

@daddoukos 11 ай бұрын

Nice video. Up to the point. I use all of aforementioned formulas and I found it helpful for a quick reference/reminder

@Serraomomma Жыл бұрын

Thank you for this video. It is a great video for "refreshing" Excel knowledge! Looks like others have made recommendations for high levels. I look forward to it!

@LovelyJordy Жыл бұрын

This was awesome thank you! I love these short vids.

@lordpetrolhead477 Жыл бұрын

Excellent video. As someone who wrote their own accounts programme in Excel and VBA, I knew all the formulas but didn’t know the level one stuff. Sequence, CTRL, shift down arrow and using the F4 key to lock in the $ sign. Well done!

@KenjiExplains Жыл бұрын

thank you!

@markatoe7074 4 ай бұрын

Hello Kenji ,you are a real tutor.love your lectures

@charlesrodriguez2176 Жыл бұрын

Great job with this collection of formulas/functions!

@pramodparnami6012 Жыл бұрын

I watch all the advertisements completely that runs at beginning, middle, at the end of video , to make sure the sponsors contribute to your efforts. Best regards from New Delhi India🇮🇳 🇮🇳

@miralesti5832 Жыл бұрын

A big thumbs up for this video, those indexmatch functions save my day, thank you!

@wuxian_2645 4 ай бұрын

Thank you soo much..I thought I knew excel but I was humbled quick..This video is so amazing.Learned a lot.Gonna watch the others too ❤🤩

@lbslassetmanagement4356 Жыл бұрын

Hi kenji I know all, except for the sequence and the strings Learnt something. Thank you very much.

@rony2469 Жыл бұрын

Hi, love your videos ! How come for level 3 I do no get numbers as a result but two dashes instead ? The formula is the same as explained. Great video btw!

@Crack11Crack11 Жыл бұрын

Amazing video Kenji! Very useful 👏

@KenjiExplains Жыл бұрын

Thank you!!

@CynSzk Жыл бұрын

Great video!!! The tips will make my life so much easier. 2 questions: 1) in the Revenue by State and Year I formatted the state and year as data validation lists so I could easily change either of the them (or both) and it seemed to still give me the correct Revenue amount. Is there a reason this would not be good practice? 2) When we used the Xlookup for the Product Arrival Date we returned one product (Dasani Water) for 5/16/22 but there were in fact 3 products with that same arrival date. How can you be sure you are returning ALL products arriving on that date? Thank you so much, I will continue to watch ever video you make.

@waqarhassan8877 Жыл бұрын

best thing of your videos is, you keep it simple and short. thanks for sharing knowledge.

@jackjohn6795 Жыл бұрын

You sir! Hats off for crisp n precise tutorial! 😊👍

@lilianezeli5521 Жыл бұрын

So going by this video I'm an excel pro. Great. Please Kenji do a video explaing the Offset function that's one formular I'm yet to understand

@eamonnsiocain6454 Жыл бұрын

Very precise and clearly explained. Thank you!

@KenjiExplains Жыл бұрын

Glad you enjoyed it!

@shellz2the1stpower Жыл бұрын

Thank you make these formulas so easy to understand.

@joseernestosabatercalvet1810 Жыл бұрын

Thank you again Kenji, appreciated!

@thedogguidebreedingadoptio5675 Жыл бұрын

Hi Kenji, I don't know how to thank you for explaining such complicated formulas in a very simple way. Can I get classes from you please?

@moumou1123 Жыл бұрын

great tips as alwas keep going kenji i've learned a lot from you thank you

@jimwalker3039 Жыл бұрын

Thanks for those great tips. Appreciate and subscribed.

@lucholeonguerrero Жыл бұрын

Fantastic video! Will binge all your vids now , so helpful

@futballmaestro Жыл бұрын

Knew all but xlookup which I hadn't used before. but great video easy to follow allong.

@phyapanchakpram Жыл бұрын

Thank you for improving my knowledge. :)

@makeme4155 Жыл бұрын

you are the first who attach the file with which I can practice! thanks! you deserve my subscribe.

@sunildalal2789 Жыл бұрын

Excellent video, so useful, and sure will be handy on a day to day use. Thank you for sharing, I am so grateful.

@noobmaster-dm7tu Жыл бұрын

an alternative to the index match is SUMIFS. it can take different conditions from different columns, and since conditions are generally unique, it will sum only 1 value i.e, it will just return that value. if multiple conditions are to be selected from the same column, one can do a SUMPRODUCT(SUMIFS

@justfelicia6671 Жыл бұрын

I knew O out of 10 formulas. THANK YOU!

@linaf6299 Жыл бұрын

Thank you for your excellent videos. Supper helpful and clear 🙏🙏

@Laughing_out_loud_ Жыл бұрын

Index match match is what I didn’t use much and really needed it

@sheenlyigongigong962 Ай бұрын

Hi, are these formulas applicable also in Mcbook? Thank you.

@jinyualcantara5036 Жыл бұрын

I've never used the indexmatch, well I learned a new one so thx for that...

@johnchapterthree5307 Жыл бұрын

This is awesome. Thank you very much. The presentation is top notch.

@2000sunsunny Жыл бұрын

today by chance saw your tutorial. thank you for great video.

@miso1076 Жыл бұрын

Great work. I think you should also cover the new array functions. I use them for formulae for which index match seem insufficient

@KenjiExplains Жыл бұрын

Great suggestion!

@helenb4550 Жыл бұрын

What are the array functions?

@miso1076 Жыл бұрын

@@helenb4550 Typically excel functions can take multiple cells as input and provide single cell as output. Eg. Sum of 10 cells will only give you single cell output. Same for if, count, vlookup or any other commonly used function. Arrays can give you multiple cell output. This makes them extremely useful in cases where you want to do intermittent calculations in a single formula itself.

@helenb4550 Жыл бұрын

@Miso10 thank you for replying! I only know offset, unique and filter. Could you teach me some more functions that would return arrays? I am working so hard to be advanced in excel because it will help my career a lot!

@joukenienhuis6888 Жыл бұрын

@@miso1076 Or at functions like the split function where it does the opposite to make an array of one cell and puts it in multiple cells

@richardguzman3874 Жыл бұрын

I proly use like 2-3 of these on a regular basis ; def not the first couple though. nice vid

@sugusgoggle Жыл бұрын

Great explanation. thank you for your videos.

@chshree007 Жыл бұрын

Just love your content! Thank youuu!

@vanessabeltransaldana9186 Жыл бұрын

Acabo de encontrar tu canal, es lo máximo. ¡Muchas gracias!

@mjmbferrer9364 Жыл бұрын

On your first example of IF function where you compare two values... you can just also use the "CELL=CELL" ex: =C2=C19 then it will put a result indicating TRUE if the values are the same or FALSE if it is NOT... Then on the finding the link of the FORMULA created.. you can also doble-click on cell with formula in it and it will highlight all the included cell in that FORMULA.

@JigaByte Жыл бұрын

It's nothing as satisfying as knowing your framework.

@kamaur01 Жыл бұрын

Knew them all but I enjoyed the video. I didn't know where those buttons were for showing formula and formula dependants. Great vid as always.

@KenjiExplains Жыл бұрын

Awesome to hear, glad you enjoyed it!

@Sahnoun23 3 ай бұрын

Great video thank you

@ismailowino2866 9 ай бұрын

Niiiice!Learnt alot Kenji!

@user-ws3kc6us6i 11 ай бұрын

the best excel teacher in youtube, good job.

@deepakjain7548 Жыл бұрын

These formulas are quite helpful. Thank you! A question - Is there a way to copy just the value from a cell with a formula in it?

@NowWhat01 Жыл бұрын

After you cooy that cell, you select your paste options and 'past values'

@philiswamnguni9590 Жыл бұрын

Thank you very much. The content was useful for me.

@blankmedia01 Жыл бұрын

Guess I'm a level 4.5 I've never used xlookup nor sumifs just sumif, but the rest I've used . I've needed xlookup I'll try it out going forward. Thanks 😊

@diegorodrigues3019 9 ай бұрын

Amazing video. I had an issue with the Vlookup. Although I had the correct items in the list it still showed N/A when I typed. When I copied the cell and pasted it showed the correct amount 🤔