Microsoft Excel Course - Intermediate Training

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.

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.

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.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

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.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

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.

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.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

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.

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.  

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.

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.

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.

CLICK AND DOWNLOAD THE HOMEWORK EXCEL FILE BELOW.