Excel Formulas YOU NEED for Business & Finance Roles
HTML-код
- Опубликовано: 7 июн 2024
- Most useful excel formulas for business & finance professionals.
👉 Use my link bit.ly/KenjiExplainsMTMar22 and code KENJI15 to get 15% off MeisterTask PRO or Business and align your team like never before!
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/621fb5...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
10 of the most useful excel formulas for business and finance professionals. These excel formulas include xlookup, text to columns, concatenate, sumifs, data validation, find & replace, unique, transpose, conditional formatting, and date functions.
To practice these, we will be using Apple's financials and their product line. Furthermore, to replicate a real-life example, we'll be interacting with our manager. These awesome formulas will help you increase your productivity and impress your colleagues and superiors. This video is suitable for all levels of excel..
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 - Intro
0:33 - Find & Replace
1:20 - Unique Function
1:50 - Transpose
2:25 - SumIFs
4:30 - Conditional Formatting
6:35 - Data Validation
7:45 - Xlookup
10:00 - Text to Columns
10:45 - Concatenate
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
This video was sponsored by MeisterTask
👉 Use my link bit.ly/KenjiExplainsMTMar22 and code KENJI15 to get 15% off MeisterTask PRO or Business and align your team like never before!
Hey Kenji, love your contents. A few questions though.
1. How do people reach out to you?
2. Your courses are very practical. Can you please get audited and get some sort of credit on your courses? So that when the user gets your certification it gets recognized? Or you think it is not necessary?
Cheers.
Bro how to know when to use xlookup, vlookup or sumifs?
Currently watching you Excel for Business and Finance playlist to prepare for my internship at BofA in a few weeks! Thank you Kenji for providing knowledge like this for free while I'm in college!
you don't need to write concatenate, you could have used just the expression inside brackets =D3&" "&E3. Both work but when dealing with complex models, shorter formulas always help for auditing the model. Keep up the great work! I didn't know about the unique formula nor the camera function in your quick access video, great stuff! ;)
Thank you Kenji for the shortcuts and Xlookup formula. Please elaborate the use of Xlookup too.
Hey Kenji! Thanks for the great video! You covered a lot of great formulas and I wanted to know where you learned them if possible. Thanks again for the great videos!
Thanks! I was a teaching assistant at university mainly teaching Excel so I learned most of them from that actually
From my perspective the most useful formuals are IF, VLOOKUP, INDEX&MATCH, SUMIF/SUMIFS and IFERROR :). Afterwards cube formulas referring to data model, if some more advanced stuff needs to be done, maybe also unpivot function in power query. There never was time during work with excel when these things weren't enough to solve the issue (obviously things like find&replace, clear formatting, remove duplicates, conditional formatting and text to columns are sometimes required as well).
Xlookup - I've never found practical use for, it was always easier to go with index&match or sumif...
These excel videos are really interesting. Thank you very much
Great illustration, thank you Kenji
Your uploading these with perfect timing, I’ve been trying to learn all these excel function in the past week
Great to hear!
Very useful video, thank you so much for teaching us.
My new favorite youtuber. I am using all of this stuff from now on!
Yesss love to hear it! 🙌
Your videos are super powerful. Don't just stop. Keep it up.
thank you so much! That's the plan :)
Cool explanation Kenji, thanks for sharing!
thanks for this video. i think there’s a better way to make header and to fill the data in SUMIFS session. You can use transform combined with unique function to make the header, and using hashtag to fill the data. that way you only insert functions in 3 cells, and the table will expand automatically as the number of regions grow.
Awesome video, thanks for the explanations
To the point! Most useful finance videos on yt after Shkreli's finance streams, great job!
Thank you!
Great video - good coverage on some fundamental excel exercises!
Many thanks!
Thank you so much for this! I am about to take a little Excel test for a job and this was very helpful for it.
how do you find ur work?
Thank you Kenji
Great video as always! Thanks Kenji
Glad you enjoyed it!
Very nice tips!! Thanks Kenji
Could you do a video on the new function of excel Lambda?
Great Job! This was really good
thanks so much!
No dejo de ver tus videos. He aprendido mucho en estos últimos días. GRAN CONTENIDO. Saludos desde México
Muchas gracias José, un saludo!
Wow, concatenate is actually very useful
Hi kenji, do you have tutorial on MS Access, to build simple databases for financial information?
This is a fantastic video🎉
Thanks Kenji! Those shortkeys for different functions using CTRL + "" (such as formatting a line under or above the cell, entering a cell etc.) - could you possibly do a video on the most important shortcuts? :)
he already uploaded a video regarding excel shortcuts
noted!
Excellent work great 👍 help👍
Hi Kenji, thank you once again, however, I do have one question, during the selection of range and for giving absolute selection we are inserting a $ sign, not able to populate same by advised short cut I am doing it manually. any advice?
thank youu😊
Excellent video
Informative 👌🙏
Thank you very much!! 😁
You're welcome!
thx Kenji again
Cheers!
amazing video
V nicely explained
Thank you so much 🙂
The greatest
What will us Accounting & Finance experts do without Excel lol. Nice Video Kenji
The concatenate example is probably over kill ... you can drop the entire function and just do the exact same thing with =D3&" "&E3 ... the '&' alone signify concatenate i.e. A&B = concatenate(a,b)
Amazing
Love your Content
Thank you!
Nice..!
We can use index match instead of xlookup.....
Anyway great content....
Kenji, your content is so great, keep it up!!!
Do you have a course or video explaining how to build a financial/fiscal sales comparison that is based on a Monday through Sunday week? My Fiscal year began on Monday, 12/26/2022. That was day 1, period 1, quarter 1 of year 2023. Building reporting based on a retail calendar like this is extremely difficult and I can find no help anywhere on the web. Excel doesn't support fiscal calendars in pivot tables. Thank you.
Hey Kenji, curious, what do you use to edit and put together you videos? They look great!
premiere pro!
Great Chanel ✌️
Glad you enjoy it!
Please can you create aging report for warehouse
Hey Kenji, I would like to thank you so much for making such incredible videos for us; it has helped me a ton! However, I have a question- at 2:25, when you used 'SUMIFS,' you got the correct answer; I tried using the XLOOKUP and INDEX MATCH, but excel is throwing me an error. I have double-checked my referencing but still can't get to the answer. Can you please help me understand why it's not working with XLOOKUP and INDEX MATCH?
That's because you actually need the sum of the quantity, xlookup does not get you the sum only the corresponding value and since there are multiple similar values in the data for eg Iphone 11 is repeated it'll show an error. I think that is the reason.
Hi Kenji, I am not able to access those excell worksheets by using the link for download. How should I do that ?
Thank you so much for this video Sir Kenji!
I'm a graduating student next year, right now I'm building up my Excel skills. This eill really boost drastically my Excel skillset. 🤗✨💖
Thanks for watching and good luck with your final year!
Hi kanji, your videos are amazing, I enjoy them very much indeed.
I am just comment here to see if you could explain a bit thorough regarding to the formula SUMIF, because I tried to do the same as you shown in the video and what comes up was a warning saying (”you’ve entered too many arguments for this function”) don’t understand why is that. If you could explain that to me that would be fab.
you should type SUMIFS to take multiple arguments not sumif
Bro how u guys so good on this. Im fuckup
Man, I’m going to have to watch this video a million times because I have no idea what to lock and what not to lock
Hello, If I don't have Xlookup formula what is the substitute of it?
Ah, control shift down...takes me back
Please can you send me the link to download the data you used for the tutorial.
When I put in =TODAY() in the "choose date" for part 2 it's saying that it doesnt match the data validation restrictions for the cell (I guess because today's date is well beyond the reach of the data range in the column?)
plz tell where is this excel sheet available for practice
First half can be done with a pivot table
what to do if anyone don't have Xlookup fn in the excel? Please reply Thanks
Excel creates pivote tables for you
Can we get a Mac version of these any time soon???
My excel does not have the Unique Function tho. What other formula can I use which can do the similar function?
Can someone explain why it's necessary to press the F4 key while completing the SUMIFS formula? What is it doing?
Can't seem to download the practice excel file.....
Thanks Kenji, one question: why the "UNIQUE" function is not available in my excel?
Could be because you have an older version?
When you work in marketing, you basically live inside Excel… my bread & butter! 😁
What version of excel are you working with. The unique and =TODAY function doesn't work on mine...smh. It's time for an upgrade.
Standard Deviation.
Hey, Where and how did you gain your excel
Skills. Did you take classes, learn from RUclips etc??
Hey, mix of university, at work, and the internet!
The entire section on SumIf could have been accomplished by leveraging a pivot table in much less time.
Let's see Paul Allen's pivot table.
Hi kenji unrelated to this vid but u mentioned in your previous vids that u interned at Marriot International. Did u intern at the Marriot International inc. office or at a hotel owned by Marriot? If the latter is the case, on ur internship certificate/recommendation, did it say Marriot International or just the hotel's name? Cuz I currently have an internship offer with the Westin (under Marriott International) and I'm not sure if i should pursue the offer or not. If i do intern there, can i say i interned @ Marriott International?
E.g. is the below ok for my resume?
Xyz Intern
Marriott International (the Westin, xyz resort)
Hey it was a hotel managed by Marriott, not the Marriott offices. My contract was with Marriott International, that's why I Marriott International as the header. For the title I just put my position and the hotel name.
Unique formula is not appearing on my excel. How do I apply it?
Please share me this sample excell file for practice
How can I download the data
Is the red book behind the intelligent investor ?
Hi Kenji, I am currently a workforce specialist with at least 4 months of experienced and I know I still lack experienced especially in excel that we are using on our daily driver. Can you recommend me the course for "Excel for Business and Finance"?
Hey Christopher, if you're looking to improve your excel skills, especially for a real-world environment I think it makes sense! (I'm obviously biased though, as I (and some other colleagues) made the course :)
Thank you Kenji, I'll consider this and saved to buy this your course. I live in philippines by the well, do you have other students live in here too?
The first part could’ve easily be done by using/inserting a pivot table
I can see how a windows OS can really be efficient for using MS office with all those alt key shortcuts that my Mac doesn't utilise
Yeah sorry i cant make a video that works for both!
Hi,
I’m new to Excel and I was wondering if someone can help. I want to know how to create a formula that will subtract whatever number I enter into a cell, from two different cells. For example; In cell A1, let’s say I have the number 10, and in cell B1, I have the number 10. When I enter a number in cell C1, I want it to subtract from cell A1 and B1. The number I enter in cell C1 could be any number.
Thank you!
=A1-b1
u just do "=A1-B1"
Nice, but what is the point of using so many different formulas to create this table, when you can do it in couple of seconds with PivotTable?
#MY STRENGTH'S
-Mininum Client Face
-Long Planning
-Analysis
-Logic
-Strategy
-Organized
Which Job Is Best For Me ?
😊
Unable to download excel free one
i can't use unique syntax
❤
I'm new to this and so I'm struggling to understand your accent. But what's even more troubling is the spreadsheet Part 2 came with a couple errors when I get to the date 3/2/2022. I don't know what the numbers are supposed to be. When I tried working to your suggestion, my answers don't match yours.
I am sad😥😥. Because I have Microsoft Office version of 2016.Where are not allowed unique function. How to use unique function.And When I Go to Update my Ms Office.than Dialogue Box Show You are Up to date . Now What should I do now?? If Anybody to know then please give this solution.This Question Also Kenji Brooo 😋🥰🥰🥰🥰
COULDN'T DO THE SAME SHOWN IN 4.01 TO 4.05.
Bro transpose function doesn't works like u mentioned in the video
I used office 2016. It seems not all Microsoft office have this formulas .
The entire section about sorting product by region could've been done in 2 seconds with a pivot table
hi sir every thing oki except music we cant hear good from we are Arabic nationality .thank you go head
Queen Elizabeth II technically owns all the dolphins in UK waters.
I love your videos but I wish they were slower. you go so fast. I get lost. If you took it a bit slower you would be so much better to follow as we work.
you can slow it down yourself in the settings just adjust the playback speed
Bruh just insert a pivot table
My excel does not have the UNIQUE formula
U moved too fast …difficult to understand…please go step by step for beginners like us …I know you are pro pro
Dude can you please speak a little bit slowly. Slowing down the video makes it too slow and as a biggner, the normanl video is a bit fast for me. Thaxx