Excel Advanced Features and Functions
4.5 (14 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
620 students enrolled

Excel Advanced Features and Functions

The Game Changer Course - Learn how to use some of the most important advanced Excel functions.
4.5 (14 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
620 students enrolled
Last updated 3/2019
English
English [Auto-generated]
Current price: $9.99 Original price: $49.99 Discount: 80% off
30-Day Money-Back Guarantee
This course includes
  • 5 hours on-demand video
  • 41 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to Udemy's top 3,000+ courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • You are going to be able to hold your own in any work related Excel project.
Requirements
  • Some basic knowledge of Excel and its main functions is required to complete this course.
Description

Hi there. My name is Kornilios, and I am part of the TEST4U team.

In this course we will teach how to use some of Excel’s advanced features and functions.

In addition to other courses we offer a way to learn skills by doing, by using the TEST4U software.

So, the tools we will use to achieve our learning goals are:

  1. Detailed lectures for each function with at least 2 examples. The Excel files of each lecture will be available for download as supplementary resources.
  2. Assignments for each function with a video solution for each one. And ...
  3. This is the game changer…We will offer free access to the most innovative training concept. The TEST4U training system. It is an irreplaceable tool that provides you with a hands-on interactive way of learning. It consists of dozens of free exercises that are automatically graded helping you learn by getting your hands dirty and not by just listening to ... plain old me.

In a bit more detail TEST4U shows an Excel workbook with certain instructions to follow, to solve the exercise. When you are done you submit your answer and TEST4U automatically grades it as correct or wrong. If you were wrong, you will see a video solution of the exercise. Neat, right?

We should note here that some basic knowledge of Excel and its basic functions is required to complete this course.

In the first part we will deal with some advanced general knowledge along with some good to know excel features.

Next, we will go on a quest to find the best and most versatile lookup function.

Then we have two chapters, one financial and one statistical. We explain how Excel helps us make our lives easier in both those fields.

Finally, we will proceed into data analysis where we will introduce you to power pivot and pivot tables.

Feel free to check our bio to learn more about us.

We hope you will enjoy this. Let’s begin.

Who this course is for:
  • Excel users that want to start using Excel in a work environment.
Course content
Expand all 49 lectures 05:08:33
+ GENERAL FUNCTIONS AND TOOLS
8 lectures 36:14

Welcome to the first chapter of our course.

We will begin with three general functions that can prove very useful. Sumifs, Averageifs, countifs. Then we will learn about the very cool goal seek tool. We will finalize this chapter explaining the Solver plugin.
I hope you enjoy and learn. Feel free to leave any comment, rating, or complain. We’ll be happy to respond and make this lesson better based on your demands.

Preview 00:32
How to submit an assignment
03:17

In Excel the manipulation and management of data is not limited to complex calculations.

It is certain that at some point everybody needs to alter the form and layout of their data, in order for them to be more readable, or answer to some specific visual requirements.

The ability to transpose our data is such a need.

Transpose and TRANSPOSE
04:59

Excel has provided us with several functions to help us make sense of huge pools of data.

SUMIFS is one of them.

The SUMIFS function
05:29

Excel has provided us with several functions to help us make sense of huge pools of data.

COUNTIFS is one of them.

The COUNTIFS function
05:16

Excel has provided us with several functions, to help us make sense of huge pools of data.

AVERAGEIFS is one of them.

The AVERAGEIFS function
05:20

Some of the most useful features in excel, are hidden under the data ribbon button, What-If analysis. In this lesson, we will try to explain and use the goal seek feature.

This tool allows us to see how one data item in a formula, impacts another.

The Goal Seek tool
03:52

In another lesson we saw how we can, using goal seek, to find ourselves a better loan for a new car. Although Excel’s Goal seek is a powerful tool, it has one basic limitation. It allows only one variable to be changed to help us achieve our goal.

Excel provides an even more powerful tool that bypasses this limitation. The solver. The solver is in the form of an add-in which must be enabled before you can use it.

Solve everything with the Solver
07:29
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the TRANSPOSE function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the transpose paste feature
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the SUMIFS function
1 question
his assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the COUNTIFS function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the AVERAGEIFS function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the Goal Seek tool
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the Solver addin
1 question
+ The Quest for the Best LOOKUP Function
11 lectures 01:01:21

One of the most used feature of Excel is the ability to lookup for certain values in a cell area dynamically.
To accomplish this, Excel provides us with some interesting functions. 
During the overview of these functions we will find out their abilities and limitations.
In doing so we will determine which is the best and most versatile lookup function.
This quest is going to lead us to the conclusion that when dealing with numbers, the power comes in ... numbers, and that the best lookup function is actually a combination of functions.
This is kind of a lengthy chapter but bear with us since the result will be rewarding.

Introduction
00:42

Probably the easiest to understand lookup function is the choose function.

It returns a value from an array of values, that corresponds to a specific position in that array. Choose can have up to 254 value arguments. 

The CHOOSE Function
05:08

The index function is one of the most interesting functions in excel. It can be combined with many other functions to help you out of some difficult situations.

It comes in two formats. An array format and a range format. The array format looks up a reference to a cell within a single, cell range

The INDEX Function
05:55

There is no way to create a powerful and versatile lookup formula without using at least once the match function.

The match function looks up information from an array of data, just like the choose function we saw in a previous lesson.

The difference is that the match function returns the position of the lookup value in the array of data.

The match function also allows us to choose if it will return an exact match of the requested value, or the closest match (above or below) the requested value.

The MATCH Function
05:35

One more very useful Excel function is Offset.

It returns a range of cells which corresponds to a specified number of rows and columns starting at a given distance from a reference cell.

The OFFSET function
04:38
The VLOOKUP Function
05:46
The HLOOKUP Function
05:24

The lookup function has two forms. The Vector form and the array form.

Microsoft proposes not to use the array form and use their two other lookup functions. Vlookup and Hlookup.

But in truth, although it is a powerful function, lookup is hardly used at all.

The LOOKUP Function
08:49

We will begin a quest to find the best lookup function. All the internal functions of Excel have limitations which we will try to overcome.

In this and the following lessons, the knowledge of the simple syntax of the functions mentioned, is considered a requirement.

The basic limitation of VLOOKUP and HLOOKUP is that they are single dimensional. They only search vertically or horizontally. The most obvious way to overcome this is by combining the two of them in a single formula.

In short we will try to make VLOOKUP search in a matrix (both vertically and horizontally). To do this we will use a VLOOKUP function with a nested HLOOKUP to provide us with the column number attribute of the VLOOKUP.

Advanced Lookup Formulas I (VLOOKUP-MATCH, VLOOKUP-HLOOKUP)
08:47

We will continue our quest for the best lookup formula with another combination. We will use an offset and two match functions to do a matrix lookup. Unfortunately, this formula also only searches from the right column to the left and from the top to the bottom, but is very easy to understand and use.

In this and the following lesson, the knowledge of the simple syntax of the functions mentioned, is considered a perquisite.

Advanced Lookup Formulas II (OFFSET-MATCH-MATCH)
04:37

We have reached the end of our quest for the best lookup function, where we will talk about the combination of index and match. In the current lesson, we will use index with dual match functions, to achieve a matrix lookup functionality. But you can easily skip one of the two match functions, and replace it with a static value if you are not interested in a matrix lookup.

The knowledge of the simple syntax of the functions mentioned, is considered a prerequisite.

Preview 06:00
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the CHOOSE function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the INDEX function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the MATCH funtion
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the OFFSET function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the VLOOKUP function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the HLOOKUP function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the LOOKUP function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use Advanced Lookup Combinations
1 question
+ Financial Functions
14 lectures 01:28:31

In Finance Excel really excels. It has a multitude of functions to cover almost any needs. 
You can find approximately 40 of them analyzed in this chapter. 
We will try to explain them to you in both simple and more financial language. They will be accompanied by simple every day examples.
The examples are simple, for purely educational reasons. But rest assured they can help you understand how to use these functions from setting up a simple household budget, to holding your own on any job position in finance. 
So let’s begin

Introduction
00:38

The functions we are going to see now belong to the group of functions excel provides us with, to calculate loans and investments.

The Functions PMT, IPMT, PPMT
11:16

The functions we are going to see now belong to the group of functions excel provides us with, to calculate loans and investments.

The Functions PV, FV, NPER, RATE
10:20
The Functions NPV, XNPV
06:18
The Functions IRR, MIRR, XIRR
07:12
The Functions DOLLARFR, DOLLARDE
03:47
The Functions YIELD, YIELDDISC, YIELDMAT
04:35
The Functions DISC, RECEIVED, INTRATE
03:59
The Functions PRICE, PRICEMAT, PRICEDISC
05:53
The Functions ACCRINT, ACCRINTM
07:45
The Functions COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD
08:53
The Functions DURATION, MDURATION
03:10
The Functions SLN, SYD, DB, DDB, VDB
09:20
The Functions TBILLEQ, TBILLPRICE, TBILLYIELD
05:25
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the IPMT function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the NPER function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the NPV function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the XIRR function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the DOLLARFR function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the YIELDDISC function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the RECEIVED function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the PRICE function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the ACCRINT function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the COUPDAYBS function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the DURATION function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the SLN function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the TBILLEQ function
1 question
+ Statistics Using Excel
7 lectures 50:10

Statistics has come to be one of the most useful sciences in modern times.
It is being used in everything. From simple election polls to advanced physics experiments or ground breaking medical discoveries.
It is also one of the hardest sciences to master due to the complicated math behind it.
For those of us getting bored of too much math, excel has provided.
There is a vast range of functions in excel aimed to help us make our sheet be statistically accurate without having to deal with too much of the math stuff. 
In this chapter we will try to understand and learn how to use the most popular amongst them.
You won’t become expert in statistics, because this requires, unfortunately, more math. But you will learn to cope with most statistics problems in a personal or working environment.
So let’s begin already.

Introduction
00:56
The Functions MEDIAN, AVERAGE, MODE
06:32
The Functions QUARTILE.INC, QUARTILE.EXC, PERCENTILE.EXC, PERCENTILE.INC
13:59
The Functions LARGE, SMALL
04:29
The Functions STDEV, STDEVP, STDEV.P, STDEV.S
09:15
The Functions VAR, VAR.S, VAR.P, VARP
06:51
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the functions MEDIAN,AVERAGE,MODE
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the Quartile functions
3 questions
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the LARGE function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the SMALL function
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the functions: STDEV,STDEVP,STDEV.P,STDEV.S
2 questions
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the functions: VAR.S,VAR.P
2 questions
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use the CORREL function
1 question
+ Data Analysis or How to Pivot with a Keyboard
7 lectures 36:35

It is time to move into Data analysis. We will learn about pivot tables and pivot charts. We will find out how we can use calculated fields and items. We will use slicers and learn about the data model.
Finally, we will use power pivot plugin and try to understand relationships and hierarchies.
Data analysis is a vast area of interest and we try to cover the basics for as much of this area as it is possible. Hopefully this will give you the starting point from which you can launch your exploration, and boldly go where no analyst has gone before

Introduction
00:39
Pivot Tables
05:48
Calculated Fields and Items
06:51
Pivot Charts
04:22
Slicers
06:53
Power Pivot
06:21
Power Pivot Hierarchies
05:41
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use Pivot tables
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use Calculated fields
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use Pivot Charts
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use Slicers
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use PowerPivot
1 question
This assignment is included in your TEST4U subscription (check the Bonus content). Using TEST4U your answer will be instantly evaluated and the results will be shown to you. Alternatively, you are provided with the necessary excel files to solve the assignment without installing TEST4U.
Use PowerPivot Hierarchies
1 question
+ Epilogue
1 lecture 00:31

Reaching the end of this series of lessons, I want to ask you to rate and comment on what you have seen so far.

We want to be better at this, and you are the only ones that can help us pin point the problems in our lectures (if there are any). 

I would also like to urge you to use everything you learned in this or any other series of lessons. Knowledge not being used can never become a skill.

I hope you are satisfied with what you watched and learned, and that it was helpful in some aspects of your life.

Epilogue
00:31
+ Bonus Content
1 lecture 03:36
BONUS Lecture (TEST4U Access)
03:36