Excel Advanced Features and Functions
- 5 hours on-demand video
- 41 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to Udemy's top 3,000+ courses anytime, anywhere.Try Udemy for Business
- You are going to be able to hold your own in any work related Excel project.
- Some basic knowledge of Excel and its main functions is required to complete this course.
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:
- Detailed lectures for each function with at least 2 examples. The Excel files of each lecture will be available for download as supplementary resources.
- Assignments for each function with a video solution for each one. And ...
- 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.
- Excel users that want to start using Excel in a work environment.
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.
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.
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.
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.
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
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.
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.
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.
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.
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
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.
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
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.