Tips & tricks: How to combine advanced functions in Excel
4.8 (3 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.
849 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Tips & tricks: How to combine advanced functions in Excel to your Wishlist.

Add to Wishlist

Tips & tricks: How to combine advanced functions in Excel

Training for nesting advanced functions into other formulas for automation purposes
4.8 (3 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.
849 students enrolled
Created by Adam Holczer
Last updated 9/2016
English
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 2 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • learn advanced formulas for making your work quicker
  • get to know how to use combination of functions in Excel
  • be more efficient by using quick solutions
View Curriculum
Requirements
  • You need a computer with possibly Microsoft Excel 2013 and you are free to go.
Description

We are living in a running and crazy world, where everybody wants to grasp the most information with the least time spending on it. This is true for all of us, especially the ones would like to study and know more. 

The aim of this course is to show you the most commonly used combination of basic and advanced functions, their use, and some real-life example how to use it in your daily work/life. 

When I started learning Excel, I realized that it is very important to know Excel functions, but knowing them is not enough. You have to build it into your daily work and start thinking the way Excel "thinks". With this course I will focus on some real-life examples I had to face during my work and try to shed light on what Excel is capable of. The structure of the course includes some basics as well, so that if you do not have a strong basis, it still gives you guidance and a firm fund to continues and handle more complex problems.

If you would like to save some time and spend less on sitting in front of a computer and handling data in Excel, then I recommend this course for you.

Have fun with the course and let me know if you have any question.

Adam

Who is the target audience?
  • This excel course is for newbies and for guys with advanced level in the same time.
  • For those who would like to save time by applying automatization
Students Who Viewed This Course Also Viewed
Curriculum For This Course
23 Lectures
02:08:43
+
Intro
3 Lectures 08:29

Short introduction about myself.

Preview 03:32

Introduction on the aim of the course and the targets we would like to achieve together in this tutorial.

Preview 01:49

Presentation on the structure and content of the course.

Preview 03:08
+
Combination examples
15 Lectures 01:28:30

This lecture shows the combination of 2 settings with its upsides on interactivity:
- data validation in a cell

- combining it with LOOKUP-type functions (in this example: VLOOKUP)

Preview 09:38

This lecture shows how to handle error messages in a dataset.

How to hide error messages
09:41

This lecture is about combining 2 important formulas showing you how to automatize the lookup formulas by nesting them:

- MATCH function and its use

- HLOOKUP and its use 


Combination of HLOOKUP and MATCH formulas
06:51

This lecture is connected to the error message hiding lecture (lecture #5). Using IF and ISBLANK function can help you to filter out blank cells in a dataset.

Combination of IF and ISBLANK functions
03:59

Nesting IS and ISBLANK formulas, moreover use OR function could help you filter out even more errors in your dataset.

Combination of IF, ISBLANK and OR functions
04:55

This lecture is about how to extend the single IF function in case you have not only 2 outcomes (true or false) but more option to choose from.

Nested IF functions
04:26

This video lecture is about the extension of a data structure/formula combination by simply using VLOOKUP function.

Using VLOOKUP formula for displaying nested IF function results
04:25

This lecture is about nesting INDEX and MATCH formula, which is similar to LOOKUP-types, however, more precise and stable. Check out the video for more.

Combination of INDEX and MATCH function
07:35

For extracting figures from a database, the must is a unique identifier. This lecture shows you how to create such lookup value with combining LEFT and CAPITAL function.

Combination of LEFT and CAPITAL function
05:15

This lecture is similar to the lecture #12, with extension of how to create more complex unique identifier with using text manipulation functions and CONCATENATE formula.

Combination of LEFT, CAPITAL and '&' function
03:16

This lecture shows the nesting of text manipulation formulas (LEFT, CAPITAL) and IF function.

Combination of LEFT, CAPITAL and IF function
03:10

This lecture shows the nesting of text manipulation formulas (LEFT, CAPITAL) and VLOOKUP function.

Combination of LEFT, CAPITAL and VLOOKUP function
05:27

This video lecture is about the combination of text manupulation functions, and how to handle text strings in Excel.

Combination of RIGHT and LEN function
05:40

This lecture is showing a case study on how to combine time formulas (NOW, HOUR and MINUTE function). By using these formulas you can extract interactive and interesting data structures and graphical design.

Combination of NOW, HOUR and MINUTE function
06:59

Controlling AVERAGEIF and YEAR combo with cell data validation.
07:13
+
Bonus
5 Lectures 31:44
Control panel: combo box
05:33

Controlling VLOOKUP with combo box
04:46

The list box is a control panel from the developer settings of Excel, which enables you to control any formulas in an interactive way. Find out more in the tutorial.

Control panel: list box
08:52

This lecture is the extension of the previous, with an example of how to use list box control panel in combination of VLOOKUP, and how to make your structure more understandable and interactive.

Controlling VLOOKUP with list box
07:11

Expand VLOOKUP and list box combination
05:22
About the Instructor
Adam Holczer
4.5 Average rating
29 Reviews
2,276 Students
6 Courses
Senior Optimization Analyst

I'm Adam, working for an oil company in sales optimization, aiming the potential sales volumes and allocation throughout the company's market. I have a strong interest in the Oil Industry, therefore I always wanted to work in the industry. after finishing Business Administration (BSc) I studied International Economics and Business in Budapest and in the Netherlands. My interest drove me back to University to study Optimization in Oil Industry in the framework of a post-graduate course. Now I am working for the biggest Oil & Gas Corporation in Central Eastern Europe as an optimization expert, supporting decision-making and analyzing the regional markets.