Advanced Excel Tutorial: Advanced Functions Course (VLOOKUP, IF, Forecasting, Statistical)

✅ Download the free course exercise files to follow along

During this epic 4 hour, 32 minute, advanced Excel training we look at some of the advanced Excel formulas and advanced Excel functions, including the IF statement and VLOOKUP in Excel. This Excel course assumes you already know how to use Microsoft Excel to a good standard and it teaches you how to take your spreadsheeting skills to the next level.

This course has a full introduction. If you don't wish to watch the introduction, then skip to 0:13:50 which is where the content starts.

📝 Use the timestamps below to navigate to different sections of the course. If you already know one of these topics, skip ahead!

0:00:00 - Prerequisites of the Course and Version of Excel
0:08:47 - Course Structure
0:13:50 - Functions - Part 1 (Start here to skip the introduction)
0:29:35 - Autosum
0:35:52 - Functions - Part 2
0:46:36 - How Date and Time Works
0:57:57 - Basic Date and Time Functions
1:09:53 - Complex Date and Time Functions
1:24:39 - Using Text Functions - Part 1
1:39:16 - Using Text Functions - Part 2
1:54:24 - Using Logical Functions
2:05:38 - Exercise 01
2:07:58 - HLOOKUP Function
2:23:16 - VLOOKUP Function
2:32:12 - Exercise 02
2:38:19 - Financial Functions and Terminology (PMT Function)
2:49:40 - Personal Financial Functions (FV Function, DATE Function, DATEDIF Function, RRI Function, PDURATION Function)
3:00:21 - Professional Financial Functions (PPMT Function, IPMT Function)
3:08:10 - Depreciation (SLN Function)
3:13:24 - Exercise 03
3:15:10 - Statistical Functions for Description - Part 1 (MIN Function, MAX Function, AVERAGE Function, MODE.MULT Function, AVERAGEIF Function)
3:29:24 - Statistical Functions for Description - Part 2 (QUARTILE.INC Function, QUARTILE.EXC Function, STDEV.P Function)
3:44:00 - Statistical Functions for Forecasting - Part 1 (SLOPE Function, FORECAST.LINEAR Function, TREND Function)
3:56:18 - Statistical Functions for Forecasting - Part 2 (LINEST Function)
4:08:45 - Exercise 04
4:10:30 - One Click Forecasting
4:19:46 - Statistical Functions for Inference

Note, this video was recording using Excel 2016, but much of what is shown is still relevant in Excel 2013, 2019, and Office 365 versions of the Excel software.

💻 Watch more free popular training tutorials from Simon Sez IT:

🔥Check out our FREE 300+ hour training course playlist here ➡️

💬Stay in touch!

🔔 Subscribe to our channel:

If you enjoyed the video, please give a thumbs up 👍🏽 ;-)

[bacet]

God Bless You for dedicating so much time and effort to teach us.

[anthonyyung]

Thanks a lot for your excel course. I find it very useful to my job.

[natashasamuel]

Great class.
Keep up the good work.

Thank You,
Natasha Samuel

[nurfadhilah]

THANKYOU Simon, I do appreciate you to make the 'compile' version in 5 hours package.

[nileshkale]

Hi, unable to download exercise file from given link in description please share updated link.

[decaelalba-mayer]

Thanks, it’s like watching an exciting movie, you want to pee but cannot. I did found usefulness on using much ignored portion of “ help”.

[leonard]

Superb very useful for me at this time❣️🙏

[robertspalding]

I am unable to download the Excel exercise files. When I click on the download site it asks for my email address which I give and then tells me it will send the files. Unfortunately, it never does send them.

[waiyanlin_esl]

Thanks. BTW, exercise download link is dead already.

[GabrielAlvarez]

Hi there, anyone knows if there is a way to calculate working hours to employees with multiple breaks during a shift. Some of the employees take more than 10 short breaks a day. Is there any easy way to calculate these hours?

[TheIvanacio]

Hello, how are you? my name is Ivan, I’m Brazilian and I would like to kindly ask you to enable the subtitles option in your videos so that you can better follow the class. Thanks.

[sayandey]

Y not work with power query instead of all the functions and formulas

[bhavneshparikh]

Loan pay back optimizer format can be available in excel?
Multiple loan repayment plan ? Pl share excel file
Multiple person how can maintenance?

[jgo]

the files on his website dont download

[AttramOluwole]

Nice tutorial. I got a quick question, while trying to use the datevalue formula I keep getting the Value error message as my answer tried resetting date but still got the same response....

[todye]

Can you please help me with a formula? I have report of Users and the access they have to various applications. Colomn A has the Access Level Names (names are not unique meaning multiple Users do have the same access level names, in other words it's not a one to one relationship) and columns E - EF have Yes or a dash (-) indicating access to a specific system. I need to identify which access level names in column A have the same access but with different names. Can you or anyone help with that?

[feoniathompson]

Where can I find the course materials to download?

[vincezzme]

IFS(A2=0, "Please Enter John's Birthday", A5=0, "Please Enter Jane's Birthday", A2>A5, CONCAT("John's Birthday is ", A2-A5, " days later than Jane's birthday"), A5>A2, CONCAT("Jane's Birthday is ", A5-A2, " days later than John's Birthday"), A2=A5, "Are they twins?")

[hakansokmen]

HEY I HOPE YOU ALL KEEPING WELL, I NEED SOME HELP GUYS FOR MY WEEKLY TIME TABLE...
EXAMPLE;
FOR RAW,
IF I PUT 1 ON B1 IS THERE ANYWAY EXCEL FILL THE REST FOR ME UNTIL H1...
SO IF I PUT 1 ON B1 AND PRESS ENTER C1 APPEAR 2 D1 3, E1 4, F1 5, G1 6 H1 7,
AND SAME AGAIN IF I PUT 8 ON B1 AND EXCEL AUTOMATICALLY FILL THERE REST NUMBER FOR ME UNTIL 14.
AND IF I PUT B1 15 AND SAME AGAIN FILL THE REST NUMBER FOR ME,
I DONT KNOW IF THERE IS ANY FORMULA FOR THIS...
HOPE YOU HELP ME...
THANKS
....

[TheMarvelmight]

The course files do not have the spreadsheets being worked on in the video.