Excel Advanced e-learning
Would you like to become a true Excel pro? Our e-learning material includes all the functions that can make your work easier. To help you learn efficiently, our instructors demonstrate how to use formulas, and many other super useful Excel tricks, through easy-to-follow videos.
| 19 témakör | |
| 239 lap | |
| 19 önellenőrző teszt | |
| 211 kép | |
| 60 oktatóvideó | |
| Letölthető gyakorló feladatok | |
| A teljesítés feltétele az összes lap megtekintése | |
| A tananyag teljesítését tanúsító Igazolás a rendszerből tölthető le |
1. Using Nested Functions in Formulas
We often need to nest different functions within each other to solve more complex tasks.
7 2 7 1 2
Lecke multimédia tartalma (2)
Using Nested Functions in Formulas I
Using Nested Functions in Formulas II.
2. Logical Functions
Let's get familiar with logical functions. They allow us to combine multiple conditions in various tasks. Let's see how we can use them!
10 3 13 1 2
Lecke multimédia tartalma (3)
Logical Functions
Logical Functions - IFS
Logical Functions - LAMBDA
3. Using VLOOKUP for Range Lookups, Search by Multiple Criteria
We are already familiar with the VLOOKUP function, but so far, we’ve mostly used it for exact matches. Let’s see how the other version works! In addition, let’s take a look at what to do when we need to perform a lookup based on multiple columns.
5 2 9 1 2
Lecke multimédia tartalma (2)
Using VLOOKUP for Range Lookups
Search by Multiple Criteria
4. Look Up Values with INDEX and MATCH functions
Lookup tasks can be solved not only with the VLOOKUP function. Let's get to know the INDEX and MATCH functions!
9 2 9 1 2
Lecke multimédia tartalma (2)
Look Up Horizontally Using INDEX and MATCH functions
Look Up Horizontally Using INDEX and MATCH functions
5. Using Array Formulas
When performing calculations in Excel, sometimes the result of a formula isn't just a single cell but an entire range of cells. These are called array formulas.
17 4 21 1 4
Lecke multimédia tartalma (4)
Using Array Formulas
Array Functions
Filtering Tables with a Function, Extracting Unique Values
Searching with the FILTER Function Based on Word Fragment
6. Searching with the XLOOKUP function
Lookup tasks can be solved not only with the VLOOKUP function. Let's get to know the XLOOKUP function!
10 2 17 1 1
Lecke multimédia tartalma (2)
Searching with the XLOOKUP function
XLOOKUP as an Array Function
7. Date Functions
In our tables, we often enter date values, which are actually numbers. Besides simple arithmetic operations, we can also use Excel’s date functions. Let’s take a closer look at them!
15 3 22 1 1
Lecke multimédia tartalma (3)
Date Functions - TODAY, NETWORKDAYS, NETWORKDAYS.INTL, YEARFRAC
Date Functions - WORKDAY, WORKDAY.INTL, EDATE, EOMONTH, WEEKDAY, WEEKNUM, YEAR, MONTH, DAY, DATE
Date Functions in Practice
8. Combining Texts
When working with tables, sometimes we need to process textual data using functions. Let's get familiar with Excel's text functions and see how to use them!
21 6 27 1 3
Lecke multimédia tartalma (6)
Combining Texts - CONCAT, TEXTJOIN, &
Other Text Functions
Other Text Functions
Other Text Functions
The TEXTSPLIT function
A SZÖVEGELŐTTE és SZÖVEGUTÁNA függvények
9. Statistical Functions
We already know some of Excel's statistical functions; now, let's take a look at a few more specialized ones.
5 1 7 1 1
Lecke multimédia tartalma (1)
Statistical Functions - TRIMMEAN, MEDIAN, LARGE, SMALL, MODE.SNGL, MAXIFS, MINIFS
10. Financial Functions
In life, there may be situations when we're forced to take out a loan, or conversely, we've saved enough money to consider investing it. While Excel's financial functions can't replace the offers provided by individual banks, they can still give us a general idea of what to expect.
5 1 5 1 1
Lecke multimédia tartalma (1)
Financial Functions - PMT, PPMT, IPMT, FV, PV, RATE
11. Database Functions
It's common that we only want to perform calculations on table rows that meet certain criteria. We already know several functions designed for these tasks. Now let's also explore why Database functions can be particularly useful.
7 2 12 1 1
Lecke multimédia tartalma (2)
Database Functions - DSUM, DAVERAGE, DCOUNT, DCOUNTA
Database Functions - DMAX, DMIN, DGET
12. Other Special Formulas and Functions
During our work, we might encounter unusual tasks that require special formulas or functions. Let's explore a few of these!
23 6 24 1 6
Lecke multimédia tartalma (6)
Other Special Formulas and Functions - 3D Reference, SUBTOTAL, AGGREGATE
Other Special Formulas and Functions - INDIRECT, HYPERLINK, CELL
The OFFSET function
The TAKE and EXPAND functions
The CHOOSECOLS and CHOOSEROWS functions
Other Special Formulas and Functions - VSTACK, HSTACK, TOCOL, TOROW, WRAPCOLS, WRAPROWS
13. Using Conditional Formatting
We've already used Excel's conditional formatting feature; now let's expand our knowledge. When creating formatting rules, we can define more complex conditions using formulas. Let's look at some practical examples!
11 3 12 1 1
Lecke multimédia tartalma (3)
Using Conditional Formatting
Using Conditional Formatting
Using Conditional Formatting
14. Protection
Excel offers numerous options for protecting our workbooks. Let's see what we need to do if we want to set a password for the entire workbook.
8 2 9 1 2
Lecke multimédia tartalma (2)
38 File and Workbook Protection
39 Protect Worksheets Protect Cells
15. Data Validation I.
We can protect data in tables using several methods. We've already seen worksheet and workbook protection; now let's explore Data Validation, which allows us to restrict the range of data that can be entered into cells.
In the next step, let's see what other settings we have regarding data validation. Let's continue our calculations on the order form sheet we've been using!
In the next step, let's see what other settings we have regarding data validation. Let's continue our calculations on the order form sheet we've been using!
12 3 16 1 2
Lecke multimédia tartalma (3)
40 Data Validation 1
41 Data Validation 2
42 Data Validation 3
16. Sharing Workbooks
Efficient teamwork requires that we can edit our spreadsheets simultaneously without major disruptions. There are several options to achieve this. We can either share a workbook stored on a network drive, or use a cloud-based service. Let's look at these solutions in more detail!
12 2 17 1 4
Lecke multimédia tartalma (2)
43 Sharing Workbooks
44 Advanced Views
17. Charts
Charts allow us to visualize data from our tables in an easy-to-understand way. We're already familiar with the basic chart types and settings; now let's take a look at some of the more specialized ones!
29 8 36 1 8
Lecke multimédia tartalma (8)
45 Charts 1
46 Charts 2
47 Charts 3
48 Charts 4
49 Charts 5
50 Charts 6
51 Charts 7
52 Charts 8
18. PivotTable (Advanced Options), Consolidate feature
We've already explored PivotTables and understand their basic functionality. Now let's dive deeper into various settings and explore some advanced options!
21 5 18 1 2
Lecke multimédia tartalma (5)
53 PivotTable 1
54 PivotTable 2
55 PivotTable 3
56 PivotTable 4
57 The Consolidate feature
19. Other Useful Features
Now let's explore some additional Excel features that we might use less frequently but can be extremely helpful for solving certain problems. We'll start with Goal Seek.
12 3 10 1 6
Lecke multimédia tartalma (3)
58 Other Useful Features
59 Special Data Types and Excel Add ins
60 Import and Export Data
Excel advanced e-learning
The e-learning material introduces more complex and specialised features of Excel. It provides an in-depth understanding of nested functions and the use of logical, lookup, date, text, statistical, financial and database functions. It also covers array formulas and array functions, as well as the use of INDEX, MATCH and XLOOKUP. The course also introduces advanced features such as data validation, workbook protection and sharing, conditional formatting, and advanced settings for charts and PivotTables. In addition, it covers further useful Excel features and add-ins.
Price: HUF 35,000. By clicking the DEMO button, you can view one of the lessons of the course material without registration.
Access duration: 365 days
Flexible learning: The e-learning course can be completed independently, according to your own schedule and at your own pace. The course material can be accessed anytime and from anywhere during the access period, allowing learning to be flexibly incorporated into your daily schedule. Completing the entire course is expected to take approximately 20 hours.