Excel for Business Analysts Crash Course (XLOOKUP, IF, Forecast Sheets & More!)
Рет қаралды 339,380
Күн бұрын✅Follow along and download the Excel for Business Analysts files for this tutorial here ⏩
www.simonsezit.com/excel-biz-...
In this 2+ hour Excel tutorial we take you through a few of the Excel functions and tools you need to perform data or business analysis in Excel. We go through VLOOKUP, HLOOKUP, INDEX MATCH, XLOOKUP, IF, IFs, Nested IF, Forecast Sheets, LINEAR, SLOPE, INTERCEPT, and finally, Conditional Formatting.
✅This tutorial was extracted from our 7-hour Excel for Business Analysts course, available here ⏩
bit.ly/3f1dRDK
We've made the files that the instructor uses in this tutorial available to download. Just go here to claim them ⏩ www.simonsezit.com/excel-biz-...
📝Time Stamps:
0:00:00 - Looking up Information with VLOOKUP (Exact)
0:11:17 - Looking up information with VLOOKUP (Approx)
0:17:26 - How to use VLOOKUP with data that expands
0:27:49 - Using HLOOKUP
0:40:03 - Combining VLOOKUP and MATCH
0:52:29 - Using INDEX, MATCH and XLOOKUP
1:09:56 - How to use the IF function
1:15:25 - More Examples of the IF Function
1:22:34 - Working with Nested Ifs
1:29:08 - The IFS function
1:37:56 - Using SUMIFS and COUNTIFS
1:49:48 - Forecast Sheets
1:57:06 - The Forecast Function
2:04:31 - Conditional Formatting
🌍 Get unlimited training with Simon Sez IT’s 150+ courses ⏩ www.simonsezit.com/courses/
🚀 Level up your skills with a free 30 day trial to our complete training library of 8,500+ videos ⏩ www.simonsezit.com/ssit-first...
Check out our Excel 2021 tutorials for beginners playlist here
✅ • Excel 2021/365 Trainin...
Check out our FREE 300+ hour training course playlist here ➡️
🔥 • Simon Sez IT Long Trai...
💻Watch more popular tutorials from Simon Sez IT:
✅Microsoft Office 2021 and Office 365 Training Tutorials playlist
👉 • Microsoft Office 2021 ...
✅Microsoft Office 2019 Training Tutorials for Beginners playlist
👉 • Microsoft Office 2019 ...
✅ PivotTable training here 👉 • Pivot Tables - All you...
💬Stay in touch!
✅SimonSezIT.com: 👉www.SimonSezIT.com/
✅StreamSkill.com: 👉StreamSkill.com/
✅KZfaq Channel: 👉 / simonsezittraining
✅LinkedIn: 👉 / simon-sez-it
🔔Subscribe to our channel:
kzfaq.info...
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel 👍🏽 ;-)
@sumayam.7453 3 жыл бұрын
I am a senior administrator in a financial company and now selectively looking to transition to a business analyst role. This course will be certainly helpful during the process. Thank you for sharing.
@boniccie 2 жыл бұрын
I watched so many video trying to learn more about VLookup. Your instructions are absolutely top notch. I learned a lot more from this video than any other video I watched on youtube,Thank you!
@SimonSezIT 2 жыл бұрын
Great to hear!
@monicaah4830 2 жыл бұрын
Thank you so much for this add-free tutorial with excel files. It is an excellent video for a beginner!
@tonytaf107 Жыл бұрын
Cannot say how amazing those tutorials are. I'm waiting to possibly start an assistant purchaser's course next month and I think that those vids will have saved me an unbelievable amount of time, by the end of my course. Thanks SO MUCH.
@SimonSezIT Жыл бұрын
Thanks for the kind comments, Tony 🙂 If you haven't already seen them, plenty more Excel tips and tricks here ➡️ kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
@alsh5011 2 жыл бұрын
What a calm voice. Thank you.
@SimonSezIT 2 жыл бұрын
Thank you too!
@ravishankartiwari9456 2 жыл бұрын
Thank you Deborah. It's a very useful and easily understandable content and anyone can learn it with the way you teach.
@SimonSezIT 2 жыл бұрын
You are very welcome! Thanks for watching :-)
@jamesdep8128 2 жыл бұрын
I really enjoy these videos. She is a great teacher.
@SimonSezIT 2 жыл бұрын
Glad you think so James!😸
@suvadipnandi459 Жыл бұрын
thank a ton for such informative videos, looking forward to more such videos/tutorials.
@jeanettesefers8180 8 ай бұрын
Very nicely explained. Thank you for sharing. This is by far the easiest VLOOK up tutorial.
@SimonSezIT 8 ай бұрын
We're glad you found the tutorial helpful! Thank you for watching and leaving your feedback. 😊 We have intermediate and advanced Excel 2021/365 training too, if you need it 👉 kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZfaq Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzfaq.info ♥️ We appreciate your support!
@seanb5522 3 жыл бұрын
Thanks for this fantastic video preparing me well for my new role as an analyst
@SimonSezIT 3 жыл бұрын
Happy to help Sean! You got this!
@trahamatrah8250 2 жыл бұрын
Great Explanation!! Thanks for this video!
@SimonSezIT 2 жыл бұрын
Thanks you, appreciate the feedback!
@dawndaffodilkathleenegrace8148 2 жыл бұрын
Thank you for these educational videos such a great help!
@SimonSezIT 2 жыл бұрын
Glad you enjoyed the tutorial. Thanks for watching :-)
@boka88bo 3 жыл бұрын
This is absolutely fantastic. Thank u a lot!
@SimonSezIT 3 жыл бұрын
Glad you like it!
@kophioriginal1098 2 жыл бұрын
These are great lessons thank you 🙏🏿 it is very informative, clear to understand and easy to replicate in practice
@SimonSezIT 2 жыл бұрын
You're very welcome! Thank you for watching ;-)
@LosoIAm 3 жыл бұрын
Many thanks for such a great tutorial.
@SimonSezIT 3 жыл бұрын
You're very welcome!
@timothyarogundade2936 3 жыл бұрын
This training is awesome. Thank you very much.
@SimonSezIT 3 жыл бұрын
You're very welcome!
@tonytaf107 Жыл бұрын
This is absolutely fantastic.
@SimonSezIT Жыл бұрын
Thanks 😊 There is additional Excel training here if you need it -> kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
@OmarMohamed-rq8ww 2 жыл бұрын
Excellent tutorial thanks
@SimonSezIT 2 жыл бұрын
Thanks Omar!
@gauthammahadevan1304 2 жыл бұрын
Great Video..!
@alexxgalindo8557 2 жыл бұрын
Thanks, guys you are amazing
@SimonSezIT 2 жыл бұрын
Thank you!
@SimonSezIT 3 жыл бұрын
Follow along and download the Excel for Business Analysts files for this tutorial here: www.simonsezit.com/excel-biz-analysis-instructor-files/ We have additional free Excel training here 👉 kzfaq.info/sun/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzfaq.info/sun/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u
@sanjaykumar-ty8in 2 жыл бұрын
am not able to download the data sets , its keep on asking to join the paid version
@sanjaykumar-ty8in 2 жыл бұрын
Pls ignore , i got the files thanks :-)
@estherolabiyi2055 2 жыл бұрын
Pls can you send the files to my Gmail address. Thank you
@JA-kb2mh 2 жыл бұрын
I got a blank page.
@muhammadmasoodmirza8290 2 жыл бұрын
@@sanjaykumar-ty8in where have you find the files ?
@ronniewest5652 Жыл бұрын
Want to thank you so much, as a student studying data analytics, this has helped me understand the basic, Merry Christmas
@SimonSezIT Жыл бұрын
Thanks Ronnie for taking the time to say thanks. We have additional DA videos here 👉 kzfaq.info/sun/PLzj7TwUeMQ3iE3tUiAOKdVjSvcq0ynUS2 Merry Christmas to you, and all the best in 2023!
@ronniewest5652 Жыл бұрын
@@SimonSezIT Thank you, merry Christmas, best teacher I have every had teaching us analysts foundation.
@manojjrkhiara240 2 жыл бұрын
Thank u so much. Good job Love from India
@SimonSezIT 2 жыл бұрын
Thanks for the love and for watching!
@TheWhyNotSeries 2 жыл бұрын
Thank you very useful!
@SimonSezIT 2 жыл бұрын
You're welcome!
@selectfromwhere...theresti7390 2 жыл бұрын
Thank You Deborah !
@SimonSezIT 2 жыл бұрын
Welcome!
@madambruja 2 жыл бұрын
This is s brilliant course
@SimonSezIT 2 жыл бұрын
Glad you enjoyed it!
@k.dhillon3245 Жыл бұрын
very good tutorial!
@SimonSezIT Жыл бұрын
Glad you think so!
@krishandas3832 2 жыл бұрын
Thank you
@jitladaroopsoong1870 9 ай бұрын
Thank you so much❤
@SimonSezIT 9 ай бұрын
You're welcome 😊 We have intermediate and advanced Excel 2021/365 training too, if you need it 👉 kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZfaq Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzfaq.info ♥️ We appreciate your support!
@GROWINGBUSINESSESBETTER 2 жыл бұрын
I appreciate the videos you produce. I slightly changed your formula to =VLOOKUP($A$18:$A$20,Review,MATCH(B17:D17,$A$4:$H$4,0),FALSE) to create a dynamic spill array. This spills down. I then cop over to population and spill the other two columns. I tried to do a dynamic spill array for the range & match permitted me to do that, but it did not work right in the vlookup formula. Just trying to better understand all that can be done.
@koala322 6 ай бұрын
The teacher is so good! I wanna learn more regarding this 😭. Does she teach other things related to business analytics too?
@SimonSezIT 6 ай бұрын
Glad you enjoyed it. Thanks for watching! 🙂 You may find my other free tutorials for various data analysis tools and services here 👉 kzfaq.info/sun/PLzj7TwUeMQ3iE3tUiAOKdVjSvcq0ynUS2 For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZfaq Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzfaq.info ♥️ We appreciate your support!
@marjannikolic2224 2 жыл бұрын
How did you format cells in column RANGE DESCRIPTION where you put intervals?I dont know how to get that form.
@ramyabhat8328 3 жыл бұрын
Very nice
@SimonSezIT 3 жыл бұрын
Thank you! Delighted you think so!!!
@devarajkammar2456 3 жыл бұрын
@@SimonSezIT you are just replying to positive comments, many of us here, haven't received the files after confirming the e-mail. please address this..
@GROWINGBUSINESSESBETTER 2 жыл бұрын
This is what I did =INDEX(GameApps,MATCH(H4,App,0),MATCH(G5:G8,GameApps[#Headers],0)) using the INDEX function. This works great, dynamic spilled array that changes when the selected item changes. I could not get Xloopup to do this dynamic spilled array.
@calemgriffin61 3 жыл бұрын
I am trying to download the Excel files, but I am not receiving them in my email. Very frustrating
@dilawarfareed9796 2 жыл бұрын
how can i get practice files used in this tutorial and thanks for sharing such detail lecture
@onam300 2 жыл бұрын
Thanks for the explanation in the best ways but I couldn't able to download sample sheet. It is not coming in the mail.
@stanleyombok7022 Жыл бұрын
Good
@sowmithrivinayparavastu9351 2 жыл бұрын
Xlookup🔥🔥
@alexikamran7039 3 жыл бұрын
Not received the excel file yet ?
@tonyndefrufrachaha1595 2 жыл бұрын
The course file is unavailable. Please provide! Thanks.
@ramk4029 Жыл бұрын
How to sum the five set of records for example if u sum 1,2,3,4,5 row cell next drag 6,7,8,9,10 pleas give me formula to find out above the seinario
@tonytaf107 Жыл бұрын
I'm about to enter an assistant buyer course. I guess this is the most suitable Excel aspect I should start to learn, isn't it?
@SimonSezIT Жыл бұрын
Hi Tony! If you are need of advanced Excel tips, that video offers quite a few. We have an Excel playlist with additional intermediate and advanced Excel tutorials -> kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
@tonytaf107 Жыл бұрын
@@SimonSezIT awesome I'll go through all of them
@kgosiletlojane1477 2 жыл бұрын
Guys I know I am late to the party. but I really would like to learn this. I can't download the material. can you please help with that. either than that, great work. I am learning so much. thank you
@SimonSezIT 2 жыл бұрын
Can you let us know what happens when you try to download the files?
@pavithrarajakumar5215 Жыл бұрын
@@SimonSezIT file is not showing up as it SAYS FILE NOT FOUND 404 ERROR..PLS HELP SIR
@sabrinabeezer4883 Жыл бұрын
Great info but I can't get the files. Help please.
@gauravkalra6184 2 жыл бұрын
I am unable to download excel sheet . Please help
@AkashNandwani 4 ай бұрын
TOP
@mallikamishra9490 3 жыл бұрын
Hey I haven't received the file yet
@jnrops2832 2 жыл бұрын
For those that can't any files its because its not free very misleading what gets sent to u is a few introductory courses nothing advanced so you can get these files for $25 a month honestly can't believe that this person didn't state that anywhere confusing the hell out of everyone
@SimonSezIT 2 жыл бұрын
The files referenced for this video are free. The courses offered on our KZfaq channel consist of a portion of our complete courses. If we offered a 10-hour course free, we wouldn't have a viable business. Anyone interested in the complete course without ads is welcome to subscribe to our membership site.
@Eternal_Truth8 2 жыл бұрын
40:11 match function for vlookup 1:06:00 xlookup 1:10:00 if functions 1:22:00 nested ifs 1:30:00 IFS function (better than nested ifs) 1:38:00 count if and sum if
@CWW888 2 жыл бұрын
why i am not able to download the file...
@tonytaf107 Жыл бұрын
For the part 09:30, is there a way not to have to press F3? I cannot find it on the current computer I'm using.
@philiplbrown Жыл бұрын
Tony - I bought my HP Lap top overseas during the COVID pandemic, and with mine F3 alone doesn't work, however if you have a "fn" key (to the right of "Ctrl"), hold down "fn" key then F3 and it should work.
@bluegoat6323 Жыл бұрын
what is the use of setting up an email subscription if we cant have the files?
@philiplbrown Жыл бұрын
Exactly! I love these tutorials, but I wanted the Excel file for another Simon Sez IT tutorial so I could follow. 30 hours later and I'm still waiting. I have been lucky a few times where the files appear from the web and I just save into my docs, but this one, and the one I viewed yesterday I had to enter my email address. Yes, we might be able to get these files "For Free", but WHEN?
@SimonSezIT Жыл бұрын
Have you checked your spam/trash folder? We just tested the download link and it works.
@ritu7644 Ай бұрын
Hey, I havnt received the excel file on my email
@suleymanmohamed512 Жыл бұрын
We appreciate the good work but kindly go abit slowly when deciding which cells to make absolute or relative
@SimonSezIT Жыл бұрын
Thanks for the suggestion. You can playback the video at a slower speed if needed. If you need additional Excel training, there is plenty of it here -> kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
@sibabobani2075 3 жыл бұрын
Hi I tried to download the files used in this tutorial using my computer but I failed. How can I possibly get an access to them?
@SleepEatWorkRepeat 3 жыл бұрын
I also could not download them. The email was sent for the subscription, but when signing up I received no files.
@asthasrivastava9564 3 жыл бұрын
I can mail it to you. Please share your email ID.
@brianbwoma1054 2 жыл бұрын
WHY IM NOT RECEIVING THE EXCEL FILES???
@ankita6414 2 жыл бұрын
Yes I didn’t get excel file
@angelclarke1942 2 жыл бұрын
i am using office 365 and I am not getting the forecast option under data tab
@SimonSezIT 2 жыл бұрын
If it is not in your Data tab by default, try right clicking on the ribbon, then select Customize the Ribbon and see if you can find what you need in the list of commands.
@angelclarke1942 2 жыл бұрын
@@SimonSezIT ok thanks
@uncutsceneonline4596 26 күн бұрын
0:17:26 - How to use VLOOKUP with data that expands ( EXPANDING PARTS)
@uncutsceneonline4596 25 күн бұрын
1:15:25 - Uselful IF Function
@uncutsceneonline4596 25 күн бұрын
1:49:48 - Forecast Sheets ( predict what's happening in the future )
@uncutsceneonline4596 25 күн бұрын
1:37:56 - Using SUMIFS and COUNTIFS (Same concept)
@JA-kb2mh 2 жыл бұрын
Why does the page go blank? Is there any way to get the information
@SimonSezIT 2 жыл бұрын
Hi! Which page are you referring to so we can check and help?
@JA-kb2mh 2 жыл бұрын
@@SimonSezIT the link to download the files i cant access. But I signed up and I am glad to have an extended version of all of this. Thank you so much for sharing
@muhammadmasoodmirza8290 2 жыл бұрын
sorry mam i cant find excel file to practice
@SimonSezIT 2 жыл бұрын
If you followed the link in the description, the files will be sent to the email you provided.
@muhammadmasoodmirza8290 2 жыл бұрын
@@SimonSezIT i did not get any data file yet even I confirm my subscription also
@ironpurush0978 Жыл бұрын
You file providing system is very poor plese fix it.
@theophilusdada1222 3 жыл бұрын
Hello,. I am yet to get the Microsoft excel file in my email.. Thanks
@bbow4972 3 жыл бұрын
I am having the same issue. Video seems informative but the files do not appear to be provided.
@mhcool67 2 жыл бұрын
the way of getting the course material is very annoying ,
@SimonSezIT 2 жыл бұрын
Thanks for the feedback, hope you enjoyed the course content!
Simon Sez IT
Рет қаралды 122 М.
Lillian Chiu
Рет қаралды 763 М.
Online Training for Everyone
Рет қаралды 59 М.
Online Training for Everyone
Рет қаралды 3 МЛН
Simon Sez IT
Рет қаралды 233 М.
LED Screen Factory-EagerLED
Рет қаралды 2,8 МЛН