Excel for Business Analysts Crash Course (XLOOKUP, IF, Forecast Sheets & More!)

✅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/
✅KZbin Channel: 👉 / simonsezittraining
✅LinkedIn: 👉 / simon-sez-it
🔔Subscribe to our channel:
kzbin.info...
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel 👍🏽 ;-)

[@monicaah4830]

Thank you so much for this add-free tutorial with excel files. It is an excellent video for a beginner!

[@boniccie]

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]

Great to hear!

[@sumayam.7453]

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.

[@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 ➡️ kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

[@alsh5011]

What a calm voice. Thank you.

[@SimonSezIT]

Thank you too!

[@ravishankartiwari9456]

Thank you Deborah. It's a very useful and easily understandable content and anyone can learn it with the way you teach.

[@SimonSezIT]

You are very welcome! Thanks for watching :-)

[@suvadipnandi459]

thank a ton for such informative videos, looking forward to more such videos/tutorials.

[@jamesdep8128]

I really enjoy these videos. She is a great teacher.

[@SimonSezIT]

Glad you think so James!😸

[@seanb5522]

Thanks for this fantastic video preparing me well for my new role as an analyst

[@SimonSezIT]

Happy to help Sean! You got this!

[@kophioriginal1098]

These are great lessons thank you 🙏🏿 it is very informative, clear to understand and easy to replicate in practice

[@SimonSezIT]

You're very welcome! Thank you for watching ;-)

[@jeanettesefers8180]

Very nicely explained. Thank you for sharing. This is by far the easiest VLOOK up tutorial.

[@SimonSezIT]

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 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@dawndaffodilkathleenegrace8148]

Thank you for these educational videos such a great help!

[@SimonSezIT]

Glad you enjoyed the tutorial. Thanks for watching :-)

[@boka88bo]

This is absolutely fantastic. Thank u a lot!

[@SimonSezIT]

Glad you like it!

[@trahamatrah8250]

Great Explanation!! Thanks for this video!

[@SimonSezIT]

Thanks you, appreciate the feedback!

[@LosoIAm]

Many thanks for such a great tutorial.

[@SimonSezIT]

You're very welcome!

[@tonytaf107]

This is absolutely fantastic.

[@SimonSezIT]

Thanks 😊 There is additional Excel training here if you need it -> kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

[@OmarMohamed-rq8ww]

Excellent tutorial thanks

[@SimonSezIT]

Thanks Omar!

[@timothyarogundade2936]

This training is awesome. Thank you very much.

[@SimonSezIT]

You're very welcome!

[@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 👉 kzbin.info/aero/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.

[@GROWINGBUSINESSESBETTER]

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.

[@alexxgalindo8557]

Thanks, guys you are amazing

[@SimonSezIT]

Thank you!

[@gauthammahadevan1304]

Great Video..!

[@manojjrkhiara240]

Thank u so much. Good job Love from India

[@SimonSezIT]

Thanks for the love and for watching!

[@dilawarfareed9796]

how can i get practice files used in this tutorial and thanks for sharing such detail lecture

[@TheWhyNotSeries]

Thank you very useful!

[@SimonSezIT]

You're welcome!

[@selectfromwhere...theresti7390]

Thank You Deborah !

[@SimonSezIT]

Welcome!

[@marjannikolic2224]

How did you format cells in column RANGE DESCRIPTION where you put intervals?I dont know how to get that form.

[@k.dhillon3245]

very good tutorial!

[@SimonSezIT]

Glad you think so!

[@madambruja]

This is s brilliant course

[@SimonSezIT]

Glad you enjoyed it!

[@onam300]

Thanks for the explanation in the best ways but I couldn't able to download sample sheet. It is not coming in the mail.

[@calemgriffin61]

I am trying to download the Excel files, but I am not receiving them in my email. Very frustrating

[@krishandas3832]

Thank you

[@koala322]

The teacher is so good! I wanna learn more regarding this 😭. Does she teach other things related to business analytics too?

[@SimonSezIT]

Glad you enjoyed it. Thanks for watching! 🙂 You may find my other free tutorials for various data analysis tools and services here 👉 kzbin.info/aero/PLzj7TwUeMQ3iE3tUiAOKdVjSvcq0ynUS2 For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@SimonSezIT]

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 👉 kzbin.info/aero/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzbin.info/aero/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u

[@sanjaykumar-ty8in]

am not able to download the data sets , its keep on asking to join the paid version

[@sanjaykumar-ty8in]

Pls ignore , i got the files thanks :-)

[@estherolabiyi2055]

Pls can you send the files to my Gmail address. Thank you

[@JA-kb2mh]

I got a blank page.

[@muhammadmasoodmirza8290]

@@sanjaykumar-ty8in where have you find the files ?

[@jitladaroopsoong1870]

Thank you so much❤

[@SimonSezIT]

You're welcome 😊 We have intermediate and advanced Excel 2021/365 training too, if you need it 👉 kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI For more information about Simon Sez IT courses, visit: SimonSezIT.com: www.SimonSezIT.com/ Stay in touch! KZbin Channel: www.youtube.com/@SimonSezIT LinkedIn: www.linkedin.com/company/simon-sez-it/ Please SUBSCRIBE ► kzbin.info ♥️ We appreciate your support!

[@GROWINGBUSINESSESBETTER]

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.

[@sabrinabeezer4883]

Great info but I can't get the files. Help please.

[@tonyndefrufrachaha1595]

The course file is unavailable. Please provide! Thanks.

[@alexikamran7039]

Not received the excel file yet ?

[@ramyabhat8328]

Very nice

[@SimonSezIT]

Thank you! Delighted you think so!!!

[@devarajkammar2456]

@@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..

[@mallikamishra9490]

Hey I haven't received the file yet

[@CWW888]

why i am not able to download the file...

[@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

[@gauravkalra6184]

I am unable to download excel sheet . Please help

[@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 -> kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

[@tonytaf107]

@@SimonSezIT awesome I'll go through all of them

[@stanleyombok7022]

Good

[@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.

[@brianbwoma1054]

WHY IM NOT RECEIVING THE EXCEL FILES???

[@kgosiletlojane1477]

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]

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

[@sowmithrivinayparavastu9351]

Xlookup🔥🔥

[@sibabobani2075]

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]

I also could not download them. The email was sent for the subscription, but when signing up I received no files.

[@asthasrivastava9564]

I can mail it to you. Please share your email ID.

[@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.

[@ankita6414]

Yes I didn’t get excel file

[@Eternal_Truth8]

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

[@AkashNandwani]

TOP

[@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 -> kzbin.info/aero/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

[@angelclarke1942]

i am using office 365 and I am not getting the forecast option under data tab

[@SimonSezIT]

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]

@@SimonSezIT ok thanks

[@JA-kb2mh]

Why does the page go blank? Is there any way to get the information

[@SimonSezIT]

Hi! Which page are you referring to so we can check and help?

[@JA-kb2mh]

@@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

[@ironpurush0978]

You file providing system is very poor plese fix it.

[@jnrops2832]

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]

The files referenced for this video are free. The courses offered on our KZbin 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.

[@muhammadmasoodmirza8290]

sorry mam i cant find excel file to practice

[@SimonSezIT]

If you followed the link in the description, the files will be sent to the email you provided.

[@muhammadmasoodmirza8290]

@@SimonSezIT i did not get any data file yet even I confirm my subscription also

[@theophilusdada1222]

Hello,. I am yet to get the Microsoft excel file in my email.. Thanks

[@bbow4972]

I am having the same issue. Video seems informative but the files do not appear to be provided.

[@mhcool67]

the way of getting the course material is very annoying ,

[@SimonSezIT]

Thanks for the feedback, hope you enjoyed the course content!