Microsoft Excel Course - Intermediate Training
4.3 (65 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,849 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel Course - Intermediate Training to your Wishlist.

Add to Wishlist

Microsoft Excel Course - Intermediate Training

Real life exercises from banking and finance
4.3 (65 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,849 students enrolled
Last updated 2/2017
English
Price: Free
Includes:
  • 2.5 hours on-demand video
  • 8 Articles
  • 8 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Beat almost everyone in Excel formulas
  • Create complex Vlookup, Index-Match, Nested IF functions, etc
  • Start learning macros and Excel programming to become even more efficient in their job
View Curriculum
Requirements
  • Excel basic knowledge
  • Basic Excel formulas
Description

Microsoft Excel Course Intermediate Training + 15 mins live online consultation with the tutor

Course Overview

Microsoft Excel Course - Intermediate Training contains all formulas that are required to be known in all finance jobs. It can be very useful for those who just finished the university studies and looking for a job in the finance sector. Only relevant and essential exercises that you will need to know in work. It will require a good understanding of basic Excel knowledge.

Material are recorded with Excel 2010 but works in 2013 and 2016 furthermore most functions also work in the 2007 version.

Enroll now to go through a deep dive of the most popular spreadsheet tool on the market, Microsoft Excel.

This course will help you to learn all formulas that could be required in any jobs and with the help of this course your Excel knowledge will be outstanding.

As your instructor I will use my 5+ years of Excel training and Investment Banking experience to guide you step by step through the Intermediate Microsoft Excel formulas to advanced level and beyond. You will start with the basics,  building a solid foundation that will give you further knowledge as you progress into the more advanced level topics,  array functions.

At completion of this course you will have mastered the most important Excel formulas and come out with an outstanding knowledge. You will be able to complete any Excel tasks with efficiency and proficiency.

Below are just a few of the topics that you will master.

  • Creating effective spreadsheets
  • Managing large sets of data
  • Mastering the use of some of Excel's most popular and highly sought after functions (VLOOKUP, IF, NESTED IF, AVERAGEIFS, SUMIFS, INDEX/MATCH, EOMONTH and many more...)
  • Create dynamic report with Excel formulas.
  • Learn how to become super fast with Excel Shortcuts
  • Get ready for the Advanced Excel Course and Excel VBA programming

So, what are you waiting for, enroll now and take the next step in mastering Excel and become outstanding in jobs and interviews!

Who is the target audience?
  • Finance people
  • Everyone who use Excel
  • Students who would like to become outstanding in jobs and interviews
Students Who Viewed This Course Also Viewed
Curriculum For This Course
31 Lectures
02:45:38
+
EXCEL SHORTCUTS AND TIME SAVERS
3 Lectures 13:59

Intermediate Excel Course - Shortcuts part 1

In this lecture you will learn the basics how to use shortcuts to navigate or select data quickly on your spreadsheet. You will also learn how to use dynamic range selection and how you can copy and paste your data in the required formats. With Microsoft Excel paste special option, you can choose from different pasting cells as values, as formulas as format, etc.

These are the basics that you need to know if you are willing to become a quick and efficient Microsoft Excel user.


Excel shortcuts and time savers – Part 1
05:54

Intermediate Excel Course - Shortcuts part 2

In this video you will learn the basics of how to use shortcuts to navigate to a different Excel sheet quickly. The aim of the shortcuts is to forget about using your PC mouse. You need to be able to navigate, select, filter, copy paste cells and ranges without even touching your mouse. This is the only way to be very fast and efficient in Excel.

In this lecture the following topics will also be covered: creating tables, search and replace text in your required cells and how to add or remove filters on your spreadsheet by using ONLY keyboard shortcuts. 



Excel shortcuts and time savers - Part 2
04:39

Intermediate Excel Course - Formula Back Tracing

In this lecture you will learn a very important feature of Microsoft Excel, which is how to trace back a formula or how to find the dependents of a cell. With the help of these feature, you will be able to see what are the values that are feeding into an other cell/formula.

DO NOT MISS THIS LECTURE because it is one of the most important part that you need to know in your Intermediate Microsoft Excel course.


Formula back tracing and shortcuts
03:26
+
VLOOKUP AND HLOOKUP FUNCTIONS
5 Lectures 20:41

Intermediate Excel Course - Vlookup-Hlookup functions

In this lecture you will learn how to use VLOOKUP to look up a value you want to find in an Excel list or table. Using VLOOKUP is similar to looking up a person’s name in a contacts list to get a phone number. VLOOKUP looks at a value in one column, and finds its corresponding value on the same row in another column.

With the help of the HLOOKUP, you will be able to find something in the column header and return an item from the same column, but in a different row.

These are fully dynamic formulas, therefore if the order of the rows or columns change in your spreadsheet, it will be still able to return you the required item.

Vlookup and Hlookup formulas
04:45

Intermediate Excel Course - Double Vlookup functions

In this lecture you will learn how to use a nested, DOUBLE VLOOKUP to look up a value you want to find in an Excel list or table. In the previous lesson we already learnt how to use a simple VLOOKUP, in this lecture we will find a solution for those cases where the data that we need is in two different tables and we need to create a connection between the two tables to lookup the required result. We will use two VLOOKUP functions in one formula.

These are fully dynamic formulas, therefore if the order of the rows or columns change in your spreadsheet, it will be still able to return you the required item.

Nested, double Vlookup
08:23

Intermediate Excel Course - Double VLOOKUP, HLOOKUP

In this lecture you will learn how to use a nested, VLOOKUP, HLOOKUP function to look up a value, that you want to find in an Excel table. In the previous lessons we already learnt how to use a simple VLOOKUP. In this lecture we will use one horizontal lookup formula and we will joint it with a vertical lookup function

These are fully dynamic formulas, therefore if the order of the rows or columns change in your spreadsheet, it will be still able to return you the required item.


Nested Vlookup and Hlookup
03:12

Intermediate Excel Course - Vlookup-Hlookup with multiple criteria

This lecture brings your Microsoft Excel knowledge to a new level. In case of a simple VLOOKUP you can have only one criteria in your search. In this exercise you will learn how you can have multiple criteria in a VLOOKUP function. This formula will return only that item that is matching with all of your criteria.

Watch this lecture now to see what kind of trick we need to use.

Vlookup with multiple criteria
04:10

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: Vlookup, Hlookup
00:11
+
INDEX AND MATCH FUNCTIONS
4 Lectures 21:35

Intermediate Excel Course - Index Match, Vlookup-Match functions

As you have already experienced the VLOOKUP and HLOOKUP functions are not fully dynamic, because you need to manually type the column-index or row-index number into your formula.

With the help of the MATCH function we will learn how can we make our formula fully dynamic and how the MATCH function will find the required column-index or row-index instead of you.

VLOOKUP and HLOOKUP functions have a limitation. You can return an item only below or on the right side of the searched item. The INDEX and MATCH formula will solve this issue and it will make your formula flexible.

If you would like to make a fully dynamic formula that is very flexible in it's usage, you should learn how to use the INDEX-MATCH formula.This lecture will help you in it to understand the basics of this formula.


Index-Match and Vlookup-Match
09:37

Intermediate Excel Course - Index Match functions 2

In the previous lecture we learnt the basics of INDEX-MATCH formulas, but in this video we will see a complex version of it. This exercise was created based on real life tasks that an investment banker need to do on a daily basis.

Look at this video and learn how you can create one formula that will be smart enough to transform a big table into a different format.

Do not forget about the homework that is added to the last lecture of each section.

Complex Index-Match exercise
03:47

Intermediate Excel Course - BONUS Index Match functions

More exercises and different examples how the INDEX and the MATCH function can be used in Microsoft Excel.

We will learn how to sum up the first X amount of numbers with an INDEX formula. The amount of numbers will be selected by a drop down list.

This lecture contains a little introduction to cell drop downs too. With the help of this function, we can select any item from a given list in a drop down form.

This is a BONUS lecture, but I can guarantee this is as important as the other lectures.

BONUS: Extra Functions of Index-Match
07:59

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: Index Match
00:12
+
IF AND NESTED IF FUNCTIONS
5 Lectures 29:41

Intermediate Excel Course - IF functions

IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. You will be able to return a number or a text based on your logical test.

No more extra unnecessary filtering in the values or results. With the help of the IF function you will be able to return a 0 or nothing if the given row does not meet your requirements and return a value or text if your criteria is fulfilled.

Nested IF" or "Nested IF statement". In this case we will use more than one IF function so that you can test for more conditions and return more possible results. Each IF statement needs to be carefully "nested" inside another so that the logic is correct.

Watch this lecture to know how to use one of the most powerful function in Microsoft Excel.

IF and nested IF functions
08:45

Intermediate Excel Course - IF (OR, AND)

The IF function can be combined with logical functions like AND and OR.

Why do you want to use it? You can add multiple criteria into your logical test. A normal IF statement can test only 1 criteria at the same time, but with the AND, OR function inside an IF statement, your possibilities are unlimited.

Watch this lecture and start using these functions to become an Advanced Microsoft Excel user.

IF statement with OR and AND
05:33

Intermediate Excel Course - IF with LIST of criteria

In this lecture we will learn how to add a List of criteria in the IF formula. 

Very few people know how to use a list in an IF function, but it is a big mistake as it can make your formula much easier to read and understand, furthermore it expands the options that you can do with the IF formula.

IF with LIST of criteria
05:56

Intermediate Excel Course - Complex IF functions

This lecture contains many easy and complex exercises about logical tests in Microsoft Excel. Those tasks that we will do in this lecture are not only designed for the Intermediate Excel level, but if you complete this lecture you will be able to use any kind of IF formula in your work.

DO NOT FORGET about the practice exercises that you can find in the next lecture. If you do not know the answer, you will find the solution in the same excel file.

IF in complex real daily tasks
09:16

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: IF, nested IF, OR and AND
00:11
+
COUNT AND COUNTIF FUNCTIONS
3 Lectures 13:51

Intermediate Excel Course - Count, CountIF functions

In this lecture we will learn how to count cells that meet a give criteria or even more criteria.

Firstly we will look at how to count those cells that contain a text. Afterwards, we will look at what formula can count the cells that are blank.

We will also cover the COUNTIF function that is purposed for counting cells with the condition you specify. First, we will briefly cover the syntax and general usage, and then I provide a number of examples and warn about possible issues that can happen when using this function with multiple criteria and specific types of cells.


Count and CountIF functions - part 1
07:50

Intermediate Excel Course - Count, CountIF functions

We have already learnt how to use the COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS formulas, but in this lecture we will practice more and look at several real life examples.

Once you have finished this lecture, download the homework Excel file and complete the exercises to gain more confidence in these intermediate level Microsoft Excel formulas.

Count and CountIF functions - part 2
05:49

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: CountIF and SumIF
00:12
+
REPLACE AND SUBSTITUTE FUNCTIONS
2 Lectures 07:27

Intermediate Excel Course - Value, Replace, Substitute

We will use the Find and Replace features in Microsoft Excel to search for something in a workbook, such as a particular number or text string.

The Replace function allows you to replace certain values within a cell with values you specify.

The Substitute function, replaces one or more instances of a given text string, within an original text string.

Value, Replace and Substitute functions
07:16

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: Replace and Substitute
00:11
+
ERROR HANDLING IN FORMULAS
2 Lectures 13:18

Intermediate Excel Course - IS, ISERROR, IFERROR

Most of you have encountered with situations when the Excel formula was returning some kind of error. We do not want to see these error in our Excel Spreadsheet so we need to find a way to handle these instances when an error happens.

We will learn several formulas in this lecture, but the most important one is the IFERROR function. It helps us to trap and handle errors produced by other formulas or functions. IFERROR can handle the following issues: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Watch this lecture to convert your spreadsheet to bullet proof and to make sure you do not send out Excel files that contains any error.

Error handling (ISerror, IFerror)
13:07

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: Error handling
00:11
+
ALL ABOUT TEXT FORMULAS
4 Lectures 27:26

Intermediate Excel Course - Text functions

Copying data from internet/Word or any other sources to Microsoft Excel is very often, but sometimes the format is not always as what we need. We need to learn how we can change and manipulate our text quickly and easily even on thousands of row. Therefore we will use the Trim, Clean functions, that removes all unnecessary characters.

Sometimes we need to extract only the Right, Left, Middle part of the text, so we will see lot of exercises that shows you how can you do it in Excel. Excel Online Academy provides you wide range of exercises to practice the Text modification formulas. This is a very important part of the Intermediate Microsoft Excel Course.

 

Text formulas - (Len, Left, Right, Mid, Trim, Clean, T, Find, Search)
08:07

Intermediate Excel Course - Text functions

We are continuing to expand our knowledge on text modification formulas. These formulas are very simple and easy to use, but it can save lot of time. We will learn how to join two or multiple different cells into one cell, by using the and sign and the Concatenate formula. We will learn a quick way to change the letters of a text to capital letter or if needed to normal letters. The Upper, Lower and the Proper functions can help you to solve these issues quickly and easily.

Text formulas - (Concatenations, Upper, Lower, Proper)
07:14

Intermediate Excel Course - Complex Text functions

In this lecture we are using complex and advanced text modification formulas. The formulas are mostly the same what we already learnt in the previous lessons, but we need to combine them into one long and complex formula.

Why do we need these complex formulas? Let's assume you would like to extract only the numbers from a cell and ignore the letters, spaces, etc. This is a complex and difficult task, but by the end of this lecture, you will know where to start and by completing the homework, you will gain enough experience to do it yourself independently.  

ADVANCED Text formulas - complex exercises
11:54

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: Text formulas
00:11
+
DATE FUNCTIONS
3 Lectures 17:56

Intermediate Excel Course - Date functions

Microsoft Excel provides a lot of functions to work with dates and times. Each function performs a simple operation and by combining several functions within one formula you can solve more complex and challenging tasks. This is the last part of the Intermediate Microsoft Excel course.

In this lecture we will cover the Now function. Text function to convert the date to the required format. We will also review how we can retrieve the Year, Month, Day from a given date or from a dynamic formula like the Now function.

In the last part of this lecture we will see examples of the Workday function that helps you to find a date that is not a weekend or a holiday. From this lecture we will be also able to return the last working day of a month with the help of the EoMonth function. Furthermore, I will show you how to use the Networkdays and the Weekday function and why it can be very useful in daily tasks.

Most important Date functions
11:19

Intermediate Excel Course - Date functions

This is the second lecture of the Date functions in this Intermediate Excel Course. In this lecture we will see how we can change and increase date by one month, how we can use the Choose formula to return the name of a day like Monday, Tuesday, etc. 

I think you have a very good knowledge of Microsoft Excel now, therefore it is time to start automating your Excel tasks, reports and enroll to the Microsoft Excel VBA programming course. With that course you can automate any task that you need and save huge amount of time. If you are interested in this course, contact the Excel Online Academy and see what are the next available dates for the Excel VBA programming course.

I hope this course was useful and you learnt a lot, if you have any questions, contact me in email and search for my website where you can get access to other FREE materials. Excel Online Academy is here to help you.

Complex Date functions
06:26

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

HOMEWORK: Date functions
00:11
About the Instructor
Excel Online Academy
4.3 Average rating
152 Reviews
4,272 Students
3 Courses
Online Education

Online Excel Academy provides educational services to everyone including students , professionals and companies. The main profile of Excel Online Academy is mostly Microsoft Excel and MS Excel Visual Basic (VBA) education by online courses and private tuition.

Additional services are available such as Private Courses, CV Advisory and Writing, Full Interview Preparation and Career Advisory in finance.

The tutor is an investment banking professional who specialized in Excel and Excel programming. Has experience in firm valuation, risk management, trading and statistics and working experience in the largest investment banks. Has been teaching private students more than 4 years in finance and in Microsoft Excel.  Furthermore, held several professional training in Excel for companies.

These days it is not enough to be good, you need to be exceptional. Our services are aimed to help all people who are looking for a job or willing to improve their career.